Re: BETWEEN optimizer problems with single-value

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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