From: Ross J. Reedstrom <reedstrm@rice.edu>
To: pgsql-sql@postgresql.org
Subject: Re: indexing and LIKE
Date: Thu, 11 Oct 2001 15:05:26 -0500
Message-ID: <20011011150526.A22528@rice.edu> (raw)
In-Reply-To: <20011011141651.B50778-100000@tb303.partitur.se>
References: <20011011141651.B50778-100000@tb303.partitur.se>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: reedstrm@rice.edu
Subject: Re: indexing and LIKE
In-Reply-To: <20011011150526.A22528@rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox