From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org, "Andreas Kretschmer" <akretschmer@spamfence.net>
Subject: Re: BETWEEN optimizer problems with single-value
Date: Wed, 15 Mar 2006 14:17:50 -0500
Message-ID: <28798.1142450270@sss.pgh.pa.us> (raw)
In-Reply-To: <44180D33.EE98.0025.0@wicourts.gov>
References: <44180105.EE98.0025.0@wicourts.gov>
<20060315181735.GA22240@KanotixBox>
<44180D33.EE98.0025.0@wicourts.gov>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> Odd. Can you tell us your PG- Version?
> this is 8.1.2 with some 8.1.3 changes plus the string literal patch.)
8.1 is certainly capable of devising the plan you want, for example
in the regression database:
regression=# explain select * from tenk1 where thousand = 10 and tenthous between 42 and 144;
QUERY PLAN
------------------------------------------------------------------------------------
Index Scan using tenk1_thous_tenthous on tenk1 (cost=0.00..6.01 rows=1 width=244)
Index Cond: ((thousand = 10) AND (tenthous >= 42) AND (tenthous <= 144))
(2 rows)
It looks to me like this is a matter of bad cost estimation, ie, it's
thinking the other index is cheaper to use. Why that is is not clear.
Can we see the pg_stats rows for ctofcNo and calDate?
Also, try to force it to generate the plan you want, so we can see what
it thinks the cost is for that. If you temporarily drop the wrong index
you should be able to get there:
begin;
drop index "Cal_CalDate";
explain analyze select ... ;
-- repeat as needed if it chooses some other wrong index
rollback;
I hope you have a play copy of the database to do this in ---
although it would be safe to do the above in a live DB, the DROP would
exclusive-lock the table until you finish the experiment and rollback,
which probably is not good for response time ...
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, Kevin.Grittner@wicourts.gov, akretschmer@spamfence.net
Subject: Re: BETWEEN optimizer problems with single-value
In-Reply-To: <28798.1142450270@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