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

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