Skip site navigation (1) Skip section navigation (2)

Re: why index scan not working when using 'like'?

From: Dror Matalon <dror(at)zapatec(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: why index scan not working when using 'like'?
Date: 2003-11-25 19:56:13
Message-ID: 20031125195613.GB30893@rlx11.zapatec.com (view raw or flat)
Thread:
Lists: 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

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2003-11-25 21:29:04
Subject: Re: why index scan not working when using 'like'?
Previous:From: Josh BerkusDate: 2003-11-25 19:51:40
Subject: Re: why index scan not working when using 'like'?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group