From: David Johnston <polobo@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Problem with slow query with WHERE conditions with OR clause on primary keys
Date: Wed, 18 Dec 2013 12:12:29 -0800 (PST)
Message-ID: <1387397549054-5783942.post@n5.nabble.com> (raw)
In-Reply-To: <1387394601821-5783927.post@n5.nabble.com>
References: <52A7A428.8070104@gmail.com>
<1386746318622-5782822.post@n5.nabble.com>
<1387394601821-5783927.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
kolszew73@gmail.com wrote
> 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
I suppose the equivalent query that you'd want would be:
SELECT ... FROM gd JOIN gd_data USING (id_gd)
WHERE id_gd IN (
SELECT id_gd FROM gd WHERE ...
UNION ALL -distinct not required in this situation
SELECT id_gd FROM gd_data WHERE ...
) --ignoring NULL implications
It does make sense conceptually...
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Problem-with-slow-query-with-WHERE-conditions-with-OR-clause...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: polobo@yahoo.com
Subject: Re: Problem with slow query with WHERE conditions with OR clause on primary keys
In-Reply-To: <1387397549054-5783942.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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