> -----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]

Fri, 4 Jun 2004 17:42:31 -0700
"Dann Corbit" <DCorbit@connx.com>
Re: TimeOf(Subselects|Joins)FromLargeTables?
"Dann Corbit" <DCorbit@connx.com> writes:
> I think ... an in-list is going to do better than a list of constants
> separated by OR.

Right at the moment, there is no performance difference between
	WHERE foo IN (const1, const2, const3)
and
	WHERE foo = const1 OR foo = const2 OR foo = const3
because in fact the parser expands the former into the latter at
a pretty early stage.

Still, I'd suggest using the IN form when you can.  It's more
concise, arguably less prone to typos, and someday we might even
implement it more efficiently than the other form.

			regards, tom lane

Sat, 05 Jun 2004 00:55:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: TimeOf(Subselects|Joins)FromLargeTables? 