Thread: indexing and LIKE
Hi! If I want to be able to search for stringmatches using LIKE, doing something like the following: select id, name from table1 where lower(name) like 'somestring%'; Actually I will be joining with some other table on id too, but the join will produce a substancial amount of data to be filtered with the LIKE clause so I figure if it'd be possible to index on lower(name) somehow, it would result in an appreciated speed gain. Is it at all possible to create an index on lower(name), and in that case, what type of index and using what syntax? Is it possible to create a multicolumn index on both id and name? Both id and name are of type "text". TIA, Patrik Kudo
On Thu, Oct 11, 2001 at 02:28:34PM +0200, Patrik Kudo wrote: > Hi! > > If I want to be able to search for stringmatches using LIKE, doing > something like the following: > > select id, name from table1 where lower(name) like 'somestring%'; > > Actually I will be joining with some other table on id too, but the join > will produce a substancial amount of data to be filtered with the LIKE > clause so I figure if it'd be possible to index on lower(name) somehow, > it would result in an appreciated speed gain. > > Is it at all possible to create an index on lower(name), and in that case, > what type of index and using what syntax? Is it possible to create a > multicolumn index on both id and name? Both id and name are of type > "text". Checking the short help from CREATE INDEX: template1=# \h create index Command: CREATE INDEX Description: Constructs a secondary index Syntax: CREATE [ UNIQUE ] INDEX index_name ON table [ USING acc_name ] ( column [ ops_name ] [, ...] ) CREATE [ UNIQUE ] INDEX index_name ON table [ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] ) template1=# So, you want something like: CREATE INDEX table1_l_name_idx ON table1 (lower(name)); Multicolumn indices are seldom as useful as you may think at first. And I don't think you can combine them with functional indices. Ross -- Ross Reedstrom, Ph.D. reedstrm@rice.edu Executive Director phone: 713-348-6166 Gulf Coast Consortium for Bioinformatics fax: 713-348-6182 Rice University MS-39 Houston, TX 77005
On Thu, 11 Oct 2001, Patrik Kudo wrote: > Hi! > > If I want to be able to search for stringmatches using LIKE, doing > something like the following: > > select id, name from table1 where lower(name) like 'somestring%'; > > Actually I will be joining with some other table on id too, but the join > will produce a substancial amount of data to be filtered with the LIKE > clause so I figure if it'd be possible to index on lower(name) somehow, > it would result in an appreciated speed gain. You can make functional indexes: create index <name> on table(lower(<col>)); If you're running in a locale other than C however I don't think postgres will use it in any case in 7.1 and earlier (I'm not sure about 7.2) > what type of index and using what syntax? Is it possible to create a > multicolumn index on both id and name? Both id and name are of type > "text". Yes, you can do a multicolumn index, but if you want an id and lower(name) index, it's a little more complicated and probably wouldn't do what you want (I think the functional indexes are limited to a single function with only column references as parameter).
Patrik Kudo wrote: > [...] > > Is it at all possible to create an index on lower(name), and in that case, > what type of index and using what syntax? You'll want to look at section 7.5 "Functional Indices" in the 7.1.3 'User's Guide'. Allan.
Hi Thanks for your respons, but I'm afraid it didn't help. I've succeeded with indexing my table using functional indices, but the problem is that the index I create won't work with my query. Let me illustrate: kudo=# select version(); version --------------------------------------------------------------PostgreSQL 7.1.3 on i386--freebsd4.3, compiled by GCC 2.95.3 (1 row) kudo=# create index person_lower_lname_idx on person (lower(last_name)); CREATE kudo=# vacuum analyze person; VACUUM kudo=# explain select userid, first_name, last_name from person where lower(last_name) like 'kud%'; NOTICE: QUERY PLAN: Seq Scan on person (cost=0.00..217.44 rows=70 width=36) EXPLAIN kudo=# explain select userid, first_name, last_name from person where lower(last_name) = 'kudo'; NOTICE: QUERY PLAN: Index Scan using person_lower_lname_idx on person (cost=0.00..57.82 rows=70 width=36) As you can see, the functional index is working fine when using the "normal" = operator. However, it is not used when using the "like" operator, which I need. I understand that a pattern-matched query probably can't be made as effective as a query with =, but I think it, at least theoretically, should be possible to use a btree-index to find matches in the first query above. Am I totaly wrong here? What is possible/impossible with Postgres? Regards, Patrik Kudo On Thu, 11 Oct 2001, Ross J. Reedstrom wrote: > On Thu, Oct 11, 2001 at 02:28:34PM +0200, Patrik Kudo wrote: > > Hi! > > > > If I want to be able to search for stringmatches using LIKE, doing > > something like the following: > > > > select id, name from table1 where lower(name) like 'somestring%'; > > > > Actually I will be joining with some other table on id too, but the join > > will produce a substancial amount of data to be filtered with the LIKE > > clause so I figure if it'd be possible to index on lower(name) somehow, > > it would result in an appreciated speed gain. > > > > Is it at all possible to create an index on lower(name), and in that case, > > what type of index and using what syntax? Is it possible to create a > > multicolumn index on both id and name? Both id and name are of type > > "text". > > Checking the short help from CREATE INDEX: > > template1=# \h create index > Command: CREATE INDEX > Description: Constructs a secondary index > Syntax: > CREATE [ UNIQUE ] INDEX index_name ON table > [ USING acc_name ] ( column [ ops_name ] [, ...] ) > CREATE [ UNIQUE ] INDEX index_name ON table > [ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] ) > > template1=# > > So, you want something like: > > CREATE INDEX table1_l_name_idx ON table1 (lower(name)); > > Multicolumn indices are seldom as useful as you may think at first. > And I don't think you can combine them with functional indices. > > Ross > -- > Ross Reedstrom, Ph.D. reedstrm@rice.edu > Executive Director phone: 713-348-6166 > Gulf Coast Consortium for Bioinformatics fax: 713-348-6182 > Rice University MS-39 > Houston, TX 77005 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
Patrik Kudo <kudo@partitur.se> writes:
> Thanks for your respons, but I'm afraid it didn't help. I've succeeded
> with indexing my table using functional indices, but the problem is that
> the index I create won't work with my query.
Works for me:
test71=# create table person (last_name text);
CREATE
test71=# create index person_lower_lname_idx on person (lower(last_name));
CREATE
test71=# explain select * from person where lower(last_name) like 'kud%';
NOTICE: QUERY PLAN:
Index Scan using person_lower_lname_idx on person (cost=0.00..8.16 rows=10 width=12)
EXPLAIN
The difference is probably a locale problem: if you aren't in C locale
then the index LIKE optimization is disabled because it doesn't work
reliably. See the list archives for more info.
regards, tom lane
On Fri, 12 Oct 2001, Patrik Kudo wrote: > kudo=# select version(); > version > -------------------------------------------------------------- > PostgreSQL 7.1.3 on i386--freebsd4.3, compiled by GCC 2.95.3 > (1 row) > > kudo=# create index person_lower_lname_idx on person (lower(last_name)); > CREATE > kudo=# vacuum analyze person; > VACUUM > kudo=# explain select userid, first_name, last_name from person where lower(last_name) like 'kud%'; > NOTICE: QUERY PLAN: > > Seq Scan on person (cost=0.00..217.44 rows=70 width=36) > > EXPLAIN > kudo=# explain select userid, first_name, last_name from person where lower(last_name) = 'kudo'; > NOTICE: QUERY PLAN: > > Index Scan using person_lower_lname_idx on person (cost=0.00..57.82 rows=70 width=36) > > As you can see, the functional index is working fine when using the > "normal" = operator. However, it is not used when using the "like" > operator, which I need. I understand that a pattern-matched query probably > can't be made as effective as a query with =, but I think it, at least > theoretically, should be possible to use a btree-index to find matches in > the first query above. > > Am I totaly wrong here? What is possible/impossible with Postgres? Are you running with locale support turned on, and if so what locale? IIRC, if it's not C locale Postgres won't use the index.