Re: count(*), EXISTS, indexes

Itai Zukerman <zukerman(at)math-hat(dot)com> writes:
>>> I'd like to calculate:
>>> SELECT sum(v) FROM A WHERE EXISTS (SELECT 1 FROM B WHERE A.x=B.x);
>>> ...but then it won't use the primary key index on A.x.
>>
>> In CVS tip (7.4-to-be) I think
>> SELECT sum(v) FROM A WHERE A.x IN (SELECT B.x FROM B);

> Oops. I guess I should've changed the example to "WHERE A.x>=B.x"
> since that's what I'm really doing. Oh, well, my goof.

Anything else you didn't bother to mention? Because in that case I
think your query reduces to

SELECT sum(v) FROM A WHERE A.x >= (SELECT min(x) FROM B);

which ought to run reasonably well (I'm sure it'll still want to
do a seqscan --- but not a nested loop).

regards, tom lane