Skip site navigation (1) Skip section navigation (2)

Re: Two fast searches turn slow when used with OR clause

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Craig James <craig_james(at)emolecules(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Two fast searches turn slow when used with OR clause
Date: 2010-08-18 03:22:32
Message-ID: AANLkTin7JK=53ejppqVg5kA_3RQ-6JC7Ee5hd=L4OzBM@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Aug 5, 2010 at 2:34 PM, Craig James <craig_james(at)emolecules(dot)com> wrote:
> => explain analyze select p.price, p.amount, p.units, s.catalogue_id,
> vn.version_id
> -> from plus p join sample s
> ->  on (p.compound_id = s.compound_id and p.supplier_id = s.supplier_id)
> -> join version vn on (s.version_id = vn.version_id) join parent pn
> ->  on (s.parent_id = pn.parent_id)
> -> where vn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1'
> -> or pn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1'
> -> order by price;

Well, you can't evaluate the WHERE clause here until you've joined {s vn pn}.

> If I only query the VERSION table, it's very fast:
>
> x=> explain analyze select p.price, p.amount, p.units, s.catalogue_id,
> vn.version_id
> -> from plus p
> -> join sample s on (p.compound_id = s.compound_id and p.supplier_id =
> s.supplier_id)
> -> join version vn on (s.version_id = vn.version_id)
> -> where vn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1' order by price;

But here you can push the WHERE clause all the way down to the vn
table, and evaluate it right at the get go, which is pretty much
exactly what is happening.

In the first case, you have to join all 297,306 vn rows against s,
because they could be interesting if the other half of the WHERE
clause turns out to hold.  In the second case, you can throw away
297,305 of those 297,306 rows before doing anything else, because
there's no possibility that they can ever be interesting.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

In response to

pgsql-performance by date

Next:From: davidDate: 2010-08-18 06:37:26
Subject: Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
Previous:From: Kevin GrittnerDate: 2010-08-18 03:08:25
Subject: Re: Very poor performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group