Re: TimeOf(Subselects|Joins)FromLargeTables?

> -----Original Message-----
> From: Hegedus, Tamas . [mailto:Hegedus.Tamas@mayo.edu]
> Sent: Friday, June 04, 2004 5:18 PM
> To: 'pgsql-hackers@postgresql.org'
> Subject: [HACKERS] TimeOf(Subselects|Joins)FromLargeTables?
>
>
> Dear All,
>
[snip]
> ---------------------------------------------
> What should I expect from the following (similar with joins)
> queries? How should I optimize the indexes? Which one to use?
>
> SELECT name, seq FROM prots WHERE fid in (SELECT fid FROM kwx
> WHERE kw_acc=812 AND kw_acc=215);

This query is a false tautology and hence will return zero rows.  The
attribute kwx cannot be simultaneously 812 and 215.

> SELECT name, seq FROM prots
> WHERE fid in (SELECT fid FROM kwx WHERE kw_acc=812 OR kw_acc=215);

I suspect that this query would be better formulated as:
SELECT prots.name, prots.seq
FROM prots, kwx
WHERE      prots.fid = kwx.fid AND kwx.kw_acc IN (812, 215);

I think joins give the planner better options than subselects and also
an in-list is going to do better than a list of constants separated by
OR.

Though you should test them both and see what the planner says.

[snip]