To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: count(*), EXISTS, indexes
Mail-Copies-To: never
From: Itai Zukerman <zukerman@math-hat.com>
Date: Fri, 11 Apr 2003 17:22:33 -0400
In-Reply-To: <13918.1050095452@sss.pgh.pa.us> (Tom Lane's message of "Fri,
	11 Apr 2003 17:10:52 -0400")
Message-ID: <87vfxkrap2.fsf@matt.w80.math-hat.com>
User-Agent: Gnus/5.090018 (Oort Gnus v0.18) Emacs/21.2 (gnu/linux)
References: <874r54sr8q.fsf@matt.w80.math-hat.com>
	<13918.1050095452@sss.pgh.pa.us>
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.
>
> 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/>