Re: Re: Problem with slow query with WHERE conditions with OR clause on primary keys

2013/12/18 kolszew73(at)gmail(dot)com <kolszew73(at)gmail(dot)com>

> Thanx for your answer
>
> My example is trivial because i want to show strange (for me) postgres
> behavior with dealing with primary keys (extreme example), in real
> situation
> user put search condition e.g. "Panas" and this generates query
> ...
> where gd.other_code like 'Panas%' OR g.code like 'Panas%'
> ..
>
> both columns has very good indexes and selectivity for "like 'Panas%'" ...
>
> I have experience from Oracle with this type of queries, and Oracle have no
> problem with it,
> executes select on index on other_code from gd and join g
> in next step executes select on index on code from g and join gd
> and this two results are connected in last step (like union)
> very fast on minimal cost
>
> and in my opinion read whole huge tables only for 10 rows in result where
> conditions are very good ... is strange
>
>
Maybe index is not in good form

try to build index with varchar_pattern_ops flag

http://postgres.cz/wiki/PostgreSQL_SQL_Tricks_I#LIKE_optimalization

CREATE INDEX like_index ON people(surname varchar_pattern_ops);

Regards

Pavel Stehule

>
>
>
>
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Problem-with-slow-query-with-WHERE-conditions-with-OR-clause-on-primary-keys-tp5782803p5783927.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>