Received: from bastuba.partitur.se (bastuba.partitur.se [212.209.169.194]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9BCSdn35525 for ; Thu, 11 Oct 2001 08:28:39 -0400 (EDT) (envelope-from kudo@partitur.se) Received: (from root@localhost) by bastuba.partitur.se (8.11.6/8.11.6) id f9BCSZP98570 for pgsql-sql@postgresql.org.AVP; Thu, 11 Oct 2001 14:28:35 +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 f9BCSYo98562 for ; Thu, 11 Oct 2001 14:28: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 f9BCSY476101 for ; Thu, 11 Oct 2001 14:28:34 +0200 (CEST) (envelope-from kudo@partitur.se) Date: Thu, 11 Oct 2001 14:28:34 +0200 (CEST) From: Patrik Kudo To: pgsql-sql@postgresql.org Subject: indexing and LIKE Message-ID: <20011011141651.B50778-100000@tb303.partitur.se> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200110/146 X-Sequence-Number: 5101 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