pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
why index scan not working when using 'like'?
5+ messages / 5 participants
[nested] [flat]

* why index scan not working when using 'like'?
@ 2003-11-25 19:48 LIANHE SHAO <lshao2@jhmi.edu>
  2003-11-25 19:51 ` Re: why index scan not working when using 'like'? Josh Berkus <josh@agliodbs.com>
  2003-11-25 19:56 ` Re: why index scan not working when using 'like'? Dror Matalon <dror@zapatec.com>
  0 siblings, 2 replies; 5+ messages in thread

From: LIANHE SHAO @ 2003-11-25 19:48 UTC (permalink / raw)
  To: pgsql-performance

Hi all,

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.

could anybody give me some hints how to deal with 
this. If I do not used index, it take too long for
the query.

   
PGA> explain select distinct probeset_id, chip,
gene_symbol, title, sequence_description, pfam from
affy_array_annotation where lower(gene_symbol) like
upper('%mif%');
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Unique  (cost=29576.44..29591.44 rows=86 width=265)
   ->  Sort  (cost=29576.44..29578.59 rows=857
width=265)
         Sort Key: probeset_id, chip, gene_symbol,
title, sequence_description, pfam
         ->  Seq Scan on affy_array_annotation 
(cost=0.00..29534.70 rows=857 width=265)
               Filter: (lower((gene_symbol)::text)
~~ 'MIF%'::text)
(5 rows)


PGA=> explain select distinct probeset_id, chip,
gene_symbol, title, sequence_description, pfam from
affy_array_annotation where lower(gene_symbol) =
upper('%mif%');
                                                   
 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Unique  (cost=3433.44..3448.44 rows=86 width=265)
   ->  Sort  (cost=3433.44..3435.58 rows=857 width=265)
         Sort Key: probeset_id, chip, gene_symbol,
title, sequence_description, pfam
         ->  Index Scan using gene_symbol_idx_fun1
on affy_array_annotation  (cost=0.00..3391.70
rows=857 width=265)
               Index Cond:
(lower((gene_symbol)::text) = '%MIF%'::text)
(5 rows)





Regards,
William




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: why index scan not working when using 'like'?
  2003-11-25 19:48 why index scan not working when using 'like'? LIANHE SHAO <lshao2@jhmi.edu>
@ 2003-11-25 19:51 ` Josh Berkus <josh@agliodbs.com>
  2003-11-25 21:29   ` Re: why index scan not working when using 'like'? Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 5+ messages in thread

From: Josh Berkus @ 2003-11-25 19:51 UTC (permalink / raw)
  To: LIANHE SHAO <lshao2@jhmi.edu>; pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: why index scan not working when using 'like'?
  2003-11-25 19:48 why index scan not working when using 'like'? LIANHE SHAO <lshao2@jhmi.edu>
  2003-11-25 19:51 ` Re: why index scan not working when using 'like'? Josh Berkus <josh@agliodbs.com>
@ 2003-11-25 21:29   ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-11-26 17:33     ` Re: why index scan not working when using 'like'? Hannu Krosing <hannu@tm.ee>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2003-11-25 21:29 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: LIANHE SHAO <lshao2@jhmi.edu>; pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
> 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.

I'm not sure why you say that FTI isn't a usable solution.  As long as
the gene symbols are separated by whitespace or some other non-letters
(eg, "foo mif bar" not "foomifbar"), I'd think FTI would work.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: why index scan not working when using 'like'?
  2003-11-25 19:48 why index scan not working when using 'like'? LIANHE SHAO <lshao2@jhmi.edu>
  2003-11-25 19:51 ` Re: why index scan not working when using 'like'? Josh Berkus <josh@agliodbs.com>
  2003-11-25 21:29   ` Re: why index scan not working when using 'like'? Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-11-26 17:33     ` Hannu Krosing <hannu@tm.ee>
  0 siblings, 0 replies; 5+ messages in thread

From: Hannu Krosing @ 2003-11-26 17:33 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: josh@agliodbs.com, LIANHE SHAO <lshao2@jhmi.edu>; pgsql-performance

Tom Lane kirjutas T, 25.11.2003 kell 23:29:
> Josh Berkus <josh@agliodbs.com> writes:
> > 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.
> 
> I'm not sure why you say that FTI isn't a usable solution.  As long as
> the gene symbols are separated by whitespace or some other non-letters
> (eg, "foo mif bar" not "foomifbar"), I'd think FTI would work.

If he wants to search on arbitrary substring, he could change tokeniser
in FTI to produce trigrams, so that "foomifbar" would be indexed as if
it were text "foo oom omi mif ifb fba bar" and search for things like
%mifb% should first do a FTI search for "mif" AND "ifb" and then simple
LIKE %mifb% to weed out something like "mififb".

There are ways to use trigrams for 1 and 2 letter matches as well.

-------------
Hannu




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: why index scan not working when using 'like'?
  2003-11-25 19:48 why index scan not working when using 'like'? LIANHE SHAO <lshao2@jhmi.edu>
@ 2003-11-25 19:56 ` Dror Matalon <dror@zapatec.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Dror Matalon @ 2003-11-25 19:56 UTC (permalink / raw)
  To: pgsql-performance


Hi,

Searches with like or regexes often can't use the index. Think of the index as
a sorted list of your items. It's easy to find an item when you know it
starts with mif so ('mif%' should use the index). But when you use a
'like' that starts with '%' the index is useless and the search needs to
do a sequential scan.

Regards,

Dror

On Tue, Nov 25, 2003 at 07:48:49PM +0000, LIANHE SHAO wrote:
> Hi all,
> 
> 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.
> 
> could anybody give me some hints how to deal with 
> this. If I do not used index, it take too long for
> the query.
> 
>    
> PGA> explain select distinct probeset_id, chip,
> gene_symbol, title, sequence_description, pfam from
> affy_array_annotation where lower(gene_symbol) like
> upper('%mif%');
>                                        QUERY PLAN
> -----------------------------------------------------------------------------------------
>  Unique  (cost=29576.44..29591.44 rows=86 width=265)
>    ->  Sort  (cost=29576.44..29578.59 rows=857
> width=265)
>          Sort Key: probeset_id, chip, gene_symbol,
> title, sequence_description, pfam
>          ->  Seq Scan on affy_array_annotation 
> (cost=0.00..29534.70 rows=857 width=265)
>                Filter: (lower((gene_symbol)::text)
> ~~ 'MIF%'::text)
> (5 rows)
> 
> 
> PGA=> explain select distinct probeset_id, chip,
> gene_symbol, title, sequence_description, pfam from
> affy_array_annotation where lower(gene_symbol) =
> upper('%mif%');
>                                                    
>  QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------
>  Unique  (cost=3433.44..3448.44 rows=86 width=265)
>    ->  Sort  (cost=3433.44..3435.58 rows=857 width=265)
>          Sort Key: probeset_id, chip, gene_symbol,
> title, sequence_description, pfam
>          ->  Index Scan using gene_symbol_idx_fun1
> on affy_array_annotation  (cost=0.00..3391.70
> rows=857 width=265)
>                Index Cond:
> (lower((gene_symbol)::text) = '%MIF%'::text)
> (5 rows)
> 
> 
> 
> 
> 
> Regards,
> William
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

-- 
Dror Matalon
Zapatec Inc 
1700 MLK Way
Berkeley, CA 94709
http://www.fastbuzz.com
http://www.zapatec.com



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2003-11-26 17:33 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-25 19:48 why index scan not working when using 'like'? LIANHE SHAO <lshao2@jhmi.edu>
2003-11-25 19:51 ` Josh Berkus <josh@agliodbs.com>
2003-11-25 21:29   ` Tom Lane <tgl@sss.pgh.pa.us>
2003-11-26 17:33     ` Hannu Krosing <hannu@tm.ee>
2003-11-25 19:56 ` Dror Matalon <dror@zapatec.com>

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