To: pgsql-sql@postgresql.org
Subject: count(*), EXISTS, indexes
From: Itai Zukerman <zukerman@math-hat.com>
Date: Fri, 11 Apr 2003 16:39:49 -0400
Message-ID: <874r54sr8q.fsf@matt.w80.math-hat.com>
User-Agent: Gnus/5.090018 (Oort Gnus v0.18) Emacs/21.2 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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