Thread: Generic search
Hi, As it seems Postgres doesn't use index when the where clause contains "like" operator. It only uses it in case of "=" operator. for example : test=> create table essai (nom varchar(50)); CREATE test=> create index essai_nom on essai (nom); CREATE test=> \d essai Table = essai +----------------------------------+----------------------------------+----- --+ | Field | Type | Length| +----------------------------------+----------------------------------+----- --+ | nom | varchar() | 50 | +----------------------------------+----------------------------------+----- --+ test=> \d essai_nom Table = essai_nom +----------------------------------+----------------------------------+----- --+ | Field | Type | Length| +----------------------------------+----------------------------------+----- --+ | nom | varchar() | -4 | +----------------------------------+----------------------------------+----- --+ test=> explain select * from essai where nom='abc'; NOTICE: QUERY PLAN: Index Scan using essai_nom on essai (cost=0.00 size=0 width=12) EXPLAIN test=> explain select * from essai where nom like 'a%'; NOTICE: QUERY PLAN: Seq Scan on essai (cost=0.00 size=0 width=12) EXPLAIN Postgres 6.4 Linux 2.0.34. So I'm looking for a way to get quite good performances with generic search. Thank you for advices. Jerome.
Hello Jerome, venerdì, 4 dicembre 98, you wrote: jd> Hi, jd> As it seems jd> Postgres doesn't use index when the where clause contains "like" operator. jd> It only uses it in case of "=" operator. jd> for example : test=>> create table essai (nom varchar(50)); jd> CREATE test=>> create index essai_nom on essai (nom); jd> CREATE test=>> \d essai jd> Table = essai jd> +----------------------------------+----------------------------------+----- jd> --+ jd> | Field | Type | jd> Length| jd> +----------------------------------+----------------------------------+----- jd> --+ jd> | nom | varchar() | jd> 50 | jd> +----------------------------------+----------------------------------+----- jd> --+ test=>> \d essai_nom jd> Table = essai_nom jd> +----------------------------------+----------------------------------+----- jd> --+ jd> | Field | Type | jd> Length| jd> +----------------------------------+----------------------------------+----- jd> --+ jd> | nom | varchar() | jd> -4 | jd> +----------------------------------+----------------------------------+----- jd> --+ test=>> explain select * from essai where nom='abc'; jd> NOTICE: QUERY PLAN: jd> Index Scan using essai_nom on essai (cost=0.00 size=0 width=12) jd> EXPLAIN test=>> explain select * from essai where nom like 'a%'; jd> NOTICE: QUERY PLAN: jd> Seq Scan on essai (cost=0.00 size=0 width=12) jd> EXPLAIN jd> Postgres 6.4 Linux 2.0.34. jd> So I'm looking for a way to get quite good performances with generic jd> search. jd> Thank you for advices. jd> Jerome. How many records do you have in this table ? If you have only few records PostgreSQL doesn't use index because it is faster to read in sequential mode. Try to insert a lot of records. It works, look: hygea=> explain select * from comuni where nome = 'A%'; NOTICE: QUERY PLAN: Index Scan using nome_comune_idx on comuni (cost=2.05 size=2 width=84) ^^^^^ EXPLAIN hygea=> explain select * from comuni where nome > 'A%'; NOTICE: QUERY PLAN: Index Scan using nome_comune_idx on comuni (cost=215.03 size=2781 width=84) ^^^^^ EXPLAIN hygea=> explain select * from comuni where nome <> 'A%'; NOTICE: QUERY PLAN: Seq Scan on comuni (cost=427.29 size=8342 width=84) EXPLAIN hygea=> explain select * from comuni where nome < 'A%'; NOTICE: QUERY PLAN: Index Scan using nome_comune_idx on comuni (cost=215.03 size=2781 width=84) ^^^^^ EXPLAIN hygea=> explain select * from comuni where nome like '%A%'; NOTICE: QUERY PLAN: Seq Scan on comuni (cost=427.29 size=2 width=84) ^^^ Pg uses index with LIKE only if the first char is not a special char. -Jose'-
>hygea=> explain select * from comuni where nome = 'A%'; >NOTICE: QUERY PLAN: >Index Scan using nome_comune_idx on comuni (cost=2.05 size=2 width=84) >^^^^^ The question was about LIKE, not =. Because LIKE uses regexp-style matching and we have no substring index functionality, it cannot use the index. If you're always matching on the first character, you can do something like fulltextindex does and use triggers and a second (indexed) table to be able to match on the first character only. HTH Taral
Hello Taral, venerdì, 4 dicembre 98, you wrote: >>hygea=> explain select * from comuni where nome = 'A%'; >>NOTICE: QUERY PLAN: >>Index Scan using nome_comune_idx on comuni (cost=2.05 size=2 width=84) >>^^^^^ T> The question was about LIKE, not =. Because LIKE uses regexp-style matching and T> we have no substring index functionality, it cannot use the index. If you're T> always matching on the first character, you can do something like fulltextindex T> does and use triggers and a second (indexed) table to be able to match on the T> first character only. Sorry, my fault. hygea=> explain select * from comuni where nome like 'A%'; NOTICE: QUERY PLAN: Index Scan using nome_comune_idx on comuni (cost=72.34 size=1 width=84) EXPLAIN -Jose'-
> >hygea=> explain select * from comuni where nome = 'A%'; > >NOTICE: QUERY PLAN: > >Index Scan using nome_comune_idx on comuni (cost=2.05 size=2 width=84) > >^^^^^ > > The question was about LIKE, not =. Because LIKE uses regexp-style matching and > we have no substring index functionality, it cannot use the index. If you're > always matching on the first character, you can do something like fulltextindex > does and use triggers and a second (indexed) table to be able to match on the > first character only. If the start of the search string is anchored. From the FAQ: When using wild-card operators like <I>LIKE</I> or <I>~,</I> indices can only be used if the beginning of the search is anchored to the start of the string. So, to use indices, <I>LIKE</I> searches can should not begin with <I>%,</I> and <I>~</I>(regular expression searches) should start with <I>^.</I> -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Fri, 4 Dec 1998, Taral wrote: > >hygea=> explain select * from comuni where nome = 'A%'; > >NOTICE: QUERY PLAN: > >Index Scan using nome_comune_idx on comuni (cost=2.05 size=2 width=84) > >^^^^^ > > The question was about LIKE, not =. Because LIKE uses regexp-style matching and > we have no substring index functionality, it cannot use the index. If you're > always matching on the first character, you can do something like fulltextindex > does and use triggers and a second (indexed) table to be able to match on the > first character only. Actually, on a beginging string only search you can do: explain select * from td_prices where manu~'^IBM'; NOTICE: QUERY PLAN: Index Scan on td_prices (cost=682.34 size=1 width=64) Thats using a btree index. :) You can't do a case insensitive search though.