pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (7+ messages)  latest in thread

Message-ID: <20011011150526.A22528@rice.edu>
Permalink:  ../20011011150526.A22528@rice.edu/
Also on:    postgresql.org/message-id/20011011150526.A22528@rice.edu

 · 

reply

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