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

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

2013/12/18 kolszew73@xxxxxxxxx <kolszew73@xxxxxxxxx>

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance