To: Jeffrey Tenny <jeffrey.tenny@comcast.net>
cc: "Jim C. Nasby" <jnasby@pervasive.com>, pgsql-performance@postgresql.org
Subject: Re: performance question (something to do w/ parameterized 
In-reply-to: <445F9F6B.9040200@comcast.net> 
References: <445F7FF8.6070707@comcast.net>
	<20060508173812.GW99570@pervasive.com>
	<445F9F6B.9040200@comcast.net>
Comments: In-reply-to Jeffrey Tenny <jeffrey.tenny@comcast.net>
	message dated "Mon, 08 May 2006 15:43:39 -0400"
Date: Mon, 08 May 2006 16:08:40 -0400
Message-ID: <29345.1147118920@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
> Well, since I don't know the exact parameter values, just substituting 
> 1-650 for $1-$650, I get:

>   Index Scan using testindex2 on x  (cost=0.00..34964.52 rows=1503 
> width=16) (actual time=0.201..968.252 rows=677 loops=1)
>     Filter: ((f = 1) OR (f = 2) OR (f = 3) OR (f = 4) ...

> So index usage is presumably good on this one.

No, that's not a very nice plan at all --- the key thing to notice is
it says Filter: not Index Cond:.  What you've actually got here is a
full-index scan over testindex2 (I guess it's doing that to achieve the
requested sort order), then computation of a 650-way boolean OR expression
for each row of the table.  Ugh.

The other way of doing this would involve 650 separate index probes and
then sorting the result.  Which would be pretty expensive too, but just
counting on my fingers it seems like that ought to come out at less than
the 35000 cost units for this plan.  The planner evidently is coming up
with a different answer though.  You might try dropping testindex2
(which I suppose is an index on (f,c)) so that it has only an index on
f to play with, and see what plan it picks and what the estimated/actual
costs are.

			regards, tom lane