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.
It *will* use the index with:
SELECT sum(A.v) FROM A,B WHERE A.x=B.x;
...but that's not the same thing. This is soooo close:
SELECT count(DISTINCT A.x) FROM A,B WHERE A.x=B.x;
Am I going to have to write a plpgsql function for this?
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.
--
Itai Zukerman <http://www.math-hat.com/~zukerman/>