From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kelly Burkhart <kelly@tradebotsystems.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: optimizer tuning/forcing correct index use
Date: Tue, 19 Mar 2002 12:32:23 -0500
Message-ID: <160.1016559143@sss.pgh.pa.us> (raw)
In-Reply-To: <02031911121009.00735@krbdev>
References: <02031907224907.00735@krbdev>
<29145.1016551722@sss.pgh.pa.us>
<02031911121009.00735@krbdev>
Kelly Burkhart <kelly@tradebotsystems.com> writes:
>> Offhand I am guessing that the table is fairly well ordered by fill_ts
>> and the planner is underestimating the effects of this. There is a
>> provision in there to try to account for data ordering, but it's new
>> code in 7.2 and doubtless still needs refinement.
fill | fill_ts | 0 | 8 | 152655 | {"2001-10-22 15:28:07-05","2001-10-22 15:28:16-05","2001-10-22 12:43:28-05","2001-08-13 08:49:19-05","2001-08-13 08:49:41-05","2001-09-25 16:13:41-05","2001-10-10 09:04:33-05","2001-10-22 14:50:05-05","2001-10-31 14:05:43-06","2002-01-07 13:35:48-06"} | {0.002,0.002,0.001,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667} | {"2001-01-10 14:13:01-06","2001-07-10 13:05:01-05","2001-09-10 09:26:08-05","2001-10-15 09:01:54-05","2001-11-02 09:49:58-06","2001-11-28 09:36:33-06","2001-12-21 08:38:03-06","2002-01-15 09:34:59-06","2002-02-04 11:34:20-06","2002-02-25 09:39:55-06","2002-03-08 14:43:10-06"} | -0.546947
Hmm. So the correlation of fill_ts with physical position is actually
negative, according to the analyze results. Still, -0.54 represents
rather strong correlation which would reduce the cost of the index scan.
There was some discussion a couple weeks ago on the pgsql-bugs list about
changing the equation the planner uses to estimate the effects of
correlation order. Are you interested in experimenting? I previously
said:
: If you look in cost_index (see approx. lines 270-340 in
: src/backend/optimizer/path/costsize.c) you'll see that it computes
: access cost estimates for both the perfectly sequential case and
: the perfectly uncorrelated case, and then tries to interpolate
: between them. I have reasonable faith in both of the endpoint
: estimation methods, but very little in the interpolation equation ---
: it was chosen on the spur of the moment and hasn't really been tested.
:
: It might be interesting to replace csquared with just
: fabs(indexCorrelation) to see if the results are better.
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, kelly@tradebotsystems.com
Subject: Re: optimizer tuning/forcing correct index use
In-Reply-To: <160.1016559143@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