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