From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeffrey Tenny <jeffrey.tenny@comcast.net>
Cc: Jim C. Nasby <jnasby@pervasive.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: performance question (something to do w/ parameterized
Date: Mon, 08 May 2006 16:08:40 -0400
Message-ID: <29345.1147118920@sss.pgh.pa.us> (raw)
In-Reply-To: <445F9F6B.9040200@comcast.net>
References: <445F7FF8.6070707@comcast.net>
<20060508173812.GW99570@pervasive.com>
<445F9F6B.9040200@comcast.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, jeffrey.tenny@comcast.net, jnasby@pervasive.com
Subject: Re: performance question (something to do w/ parameterized
In-Reply-To: <29345.1147118920@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox