Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id CEAA7475864 for ; Tue, 19 Mar 2002 12:32:22 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g2JHWNS00163; Tue, 19 Mar 2002 12:32:23 -0500 (EST) To: Kelly Burkhart Cc: pgsql-sql@postgresql.org Subject: Re: optimizer tuning/forcing correct index use In-reply-to: <02031911121009.00735@krbdev> References: <02031907224907.00735@krbdev> <29145.1016551722@sss.pgh.pa.us> <02031911121009.00735@krbdev> Comments: In-reply-to Kelly Burkhart message dated "Tue, 19 Mar 2002 11:12:10 -0600" Date: Tue, 19 Mar 2002 12:32:23 -0500 Message-ID: <160.1016559143@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200203/233 X-Sequence-Number: 7112 Kelly Burkhart 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