Re: count(*), EXISTS, indexes

>> Define:
>> CREATE TABLE A (x int PRIMARY KEY, real v);
>> CREATE TABLE B (x int);
>
>> 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.

> would probably work well. In current releases I think all you can do is
> add an index to B.x and settle for the EXISTS() approach.

I don't think indexing B will help speed-wise if it still does the seq
scan through A.

>> PS. B is relatively small, a few thousand rows, while A has well over
>> 500,000 rows. The DISTINCT A.x should be about 10,000-50,000.
>
> BTW, how can a PRIMARY KEY column have fewer DISTINCT values than there
> are rows?

Oh, I just meant, "the DISTINCT A.x that are returned by:

SELECT DISTINCT A.x FROM A,B WHERE A.x=B.x

". Sorry for my lack of clarity...

--
Itai Zukerman <http://www.math-hat.com/~zukerman/>