Re: [SQL] Good Optimization

On Wed, 7 Jul 1999, secret wrote:

>
> There is a simple way to optimize SQL queries involving joins to
> PostgreSQL that I think should be handled by Postgre? If one is joining
> a tables a,b on attribute "x" and if one has something like x=3 then it
> helps A LOT to say: a.x=3 and b.x=3 in addition to saying a.x=b.x ...
> The example below shoulds the radical speed gain of doing this, and I
> think it isn't something real obvious to most people...
>

Also if the optimizer works similar to Oracle then the order of
where statements would also help in actual performance. I'm mostly a
lurker on this list but have never seen anything about this issue.
When using Oracle, and won't it be nice when people mention PostgreSQL
functionality when comparing other databases, the statements at the
bottom should be the most restrictive and joins should be near the top.

select *
from po,tickets
where po_id=material_po
and po_id=8888 ;

Would be the best. Whereas

select *
from po,tickets
where po_id=8888
and po_id=material_po;

would do the join then select those tuples that have a po_id of 8888.

Of course this is probably what PostgreSQL is doing already without a
requirement for the positioning.

Rod
--
Roderick A. Anderson
raanders(at)altoplanos(dot)net Altoplanos Information Systems, Inc.
Voice: 208.765.6149 212 S. 11th Street, Suite 5
FAX: 208.664.5299 Coeur d'Alene, ID 83814