Received: from bastuba.partitur.se (bastuba.partitur.se [212.209.169.194]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9C8Gan83152 for ; Fri, 12 Oct 2001 04:16:37 -0400 (EDT) (envelope-from kudo@partitur.se) Received: (from root@localhost) by bastuba.partitur.se (8.11.6/8.11.6) id f9C8GYw55671 for pgsql-sql@postgresql.org.AVP; Fri, 12 Oct 2001 10:16:34 +0200 (CEST) (envelope-from kudo@partitur.se) Received: from tb303.partitur.se (tb303.partitur.se [212.209.169.230]) by bastuba.partitur.se (8.11.6/8.11.6) with ESMTP id f9C8GYo55663; Fri, 12 Oct 2001 10:16:34 +0200 (CEST) (envelope-from kudo@partitur.se) Received: from localhost (localhost [127.0.0.1]) by tb303.partitur.se (8.11.6/8.11.6) with ESMTP id f9C8GX401856; Fri, 12 Oct 2001 10:16:33 +0200 (CEST) (envelope-from kudo@partitur.se) Date: Fri, 12 Oct 2001 10:16:33 +0200 (CEST) From: Patrik Kudo To: "Ross J. Reedstrom" cc: pgsql-sql@postgresql.org Subject: Re: indexing and LIKE In-Reply-To: <20011011150526.A22528@rice.edu> Message-ID: <20011012100747.I99068-100000@tb303.partitur.se> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200110/152 X-Sequence-Number: 5107 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 >