> -----Original Message-----
> From: Hegedus, Tamas . [mailto:[EMAIL PROTECTED]
> Sent: Friday, June 04, 2004 5:18 PM
> To: '[EMAIL PROTECTED]'
> 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]
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
