Re: [GENERAL] Index on substring
2011/6/5 Håvard Wahl Kongsgård > Hi, my database performance badly on substring comparison between two very > large tables. > In postgresql 8.4 is it possible to create a index on a substring, or must > I create an new field for the substring match ( and then create a new index > for that field)? > > -- > Håvard Wahl Kongsgård > > http://havard.security-review.net/ > > Hi, I've checked that for 9.0, however for 8.4 should work as well: CREATE INDEX i_test_sbstr ON test( substring(t, 5, 3) ); It is important that the substring() parameters in the index definition are the same as you use in your query. regards Szymon
Re: [GENERAL] Index on substring?
Jeff Eckermann schrieb: > > Tom, > Thanks very much for your full and clear answer. > It's hard to imagine a general use for this facility, anyway. > For me this is a one-off exercise, albeit a big one. > Regards > There're commercial OO persistance frameworks out there, which create there own OID's (actually they consists out of three numbers) all these numbers are converted to base 36 and concatenated to a string with size 15. One part of this string is a class number of the instance you have just loaded. Therefore if you look for instances of a special class you may query a substring of this OID Just as an practical usage Marten
RE: [GENERAL] Index on substring?
Tom, Thanks very much for your full and clear answer. It's hard to imagine a general use for this facility, anyway. For me this is a one-off exercise, albeit a big one. Regards > -Original Message- > From: Tom Lane [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, October 12, 2000 12:49 AM > To: Jeff Eckermann > Cc: '[EMAIL PROTECTED]' > Subject: Re: [GENERAL] Index on substring? > > Jeff Eckermann <[EMAIL PROTECTED]> writes: > > extracts=# create index c_namesum_i on customers > (substr(bill_company,1,5)); > > ERROR: parser: parse error at or near "1" > > The functional-index syntax only allows a function name applied to > simple column names. > > You can work around this by defining a function that handles any > additional computation needed, eg, > > create index c_namesum_i on customers (mysubstr15(bill_company)); > > where mysubstr15(foo) returns substr(foo,1,5). In current releases > the intermediate function has to be in C or a PL language. 7.1 will > allow a SQL-language function too (although frankly I'd recommend > against using a SQL function for indexing, on performance grounds). > > There's been some talk of generalizing the functional-index support > into arbitrary-expression-index support, but it doesn't seem to be > real high on anyone's priority list. > > regards, tom lane
Re: [GENERAL] Index on substring?
Jeff Eckermann <[EMAIL PROTECTED]> writes: > extracts=# create index c_namesum_i on customers (substr(bill_company,1,5)); > ERROR: parser: parse error at or near "1" The functional-index syntax only allows a function name applied to simple column names. You can work around this by defining a function that handles any additional computation needed, eg, create index c_namesum_i on customers (mysubstr15(bill_company)); where mysubstr15(foo) returns substr(foo,1,5). In current releases the intermediate function has to be in C or a PL language. 7.1 will allow a SQL-language function too (although frankly I'd recommend against using a SQL function for indexing, on performance grounds). There's been some talk of generalizing the functional-index support into arbitrary-expression-index support, but it doesn't seem to be real high on anyone's priority list. regards, tom lane
