From: Josh Berkus <josh@agliodbs.com>
To: LIANHE SHAO <lshao2@jhmi.edu>
To: pgsql-performance@postgresql.org
Subject: Re: why index scan not working when using 'like'?
Date: Tue, 25 Nov 2003 11:51:40 -0800
Message-ID: <200311251151.40781.josh@agliodbs.com> (raw)
In-Reply-To: <3d83693d9fe0.3d9fe03d8369@jhmimail.jhmi.edu>
References: <3d83693d9fe0.3d9fe03d8369@jhmimail.jhmi.edu>
Lianhe,
> I want to use index on the gene_symbol column in my
> query and gene_symbol is indexed. but when I use
> lower (gene_symbol) like lower('%mif%'), the index
> is not used. While when I change to
> lower(gene_symbol) = lower('mif'), the index is used
> and index scan works, but this is not what I like. I
> want all the gene_symbols containing substring
> 'mif' are pulled out, and not necessarily exactly match.
LIKE '%mif%' is what's called an "unanchored text search" and it cannot use an
index. The database *has* to scan the full text looking for the substring.
This is true of all database platforms I know of.
In regular text fields containing words, your problem is solvable with full
text indexing (FTI). Unfortunately, FTI is not designed for arbitrary
non-language strings. It could be adapted, but would require a lot of
hacking.
So you will need to find a way to restructure you data to avoid needing
unanchored text searches. One way would be to break down the gene_symbol
field into its smallest atomic components and store those in an indexed child
table. Or if you're searching on the same values all the time, you can
create a partial index.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
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-performance@postgresql.org
Cc: josh@agliodbs.com, lshao2@jhmi.edu
Subject: Re: why index scan not working when using 'like'?
In-Reply-To: <200311251151.40781.josh@agliodbs.com>
* 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