Received: from ece.rice.edu (ece.rice.edu [128.42.4.34]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9BK5Un85332 for ; Thu, 11 Oct 2001 16:05:30 -0400 (EDT) (envelope-from reedstrm@rice.edu) Received: from localhost (localhost [127.0.0.1]) by ece.rice.edu (Postfix) with ESMTP id 8730468A59 for ; Thu, 11 Oct 2001 15:05:27 -0500 (CDT) Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by ece.rice.edu (Postfix) with ESMTP id CA63368A17 for ; Thu, 11 Oct 2001 15:05:26 -0500 (CDT) Received: from reedstrm by wallace.ece.rice.edu with local (Exim 3.22 #1 (Debian)) id 15rm58-0005sQ-00 for ; Thu, 11 Oct 2001 15:05:26 -0500 Date: Thu, 11 Oct 2001 15:05:26 -0500 From: "Ross J. Reedstrom" To: pgsql-sql@postgresql.org Subject: Re: indexing and LIKE Message-ID: <20011011150526.A22528@rice.edu> Mail-Followup-To: "Ross J. Reedstrom" , pgsql-sql@postgresql.org References: <20011011141651.B50778-100000@tb303.partitur.se> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20011011141651.B50778-100000@tb303.partitur.se> User-Agent: Mutt/1.3.18i X-Virus-Scanned: by AMaViS snapshot-20010714 X-Archive-Number: 200110/147 X-Sequence-Number: 5102 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