pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwhy 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>
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:51 Josh Berkus <josh@agliodbs.com>
parent: LIANHE SHAO <lshao2@jhmi.edu>
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:56 Dror Matalon <dror@zapatec.com>
parent: LIANHE SHAO <lshao2@jhmi.edu>
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
* Re: why index scan not working when using 'like'?
@ 2003-11-25 21:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
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-26 17:33 Hannu Krosing <hannu@tm.ee>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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
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