Re: Inconsistant use of index.

Ron Mayer <ron(at)intervideo(dot)com> writes:
>> I'm particularly interested in the correlation estimate for the dat
>> column. (Would you happen to have an idea whether the data has been
>> inserted more-or-less in dat order?)

> I beleve much of February was loaded first, then we back-filled January,
> and daily I've been adding March's results. I don't believe the index-usage
> stopped when we did the january fill... something happend a few days ago after
> a pretty routine daily load.

The correlation estimate for dat is pretty low (0.086088), which I think
reflects the fact that on a macro level your data is not very orderly
(Feb/Jan/Mar). However, if it's been loaded on a daily basis then the
records for any single day will be together --- which is why the indexed
probe for a single day is so fast. I don't see any way that we can
expect the system to model this effect with only one ordering-correlation
number :-( ... so a proper fix will have to wait for some future release
when we can think about having more extensive stats about ordering.

In the meantime, it would be interesting to see if re-establishing the
big-picture order correlation would persuade the planner to do the right
thing. Could you do something like this:

CREATE TABLE foo AS SELECT * FROM fact ORDER BY dat;
TRUNCATE TABLE fact;
INSERT INTO fact SELECT * FROM foo;
DROP TABLE foo;
VACUUM ANALYZE fact;

(this should leave you in a state where pg_stats shows correlation 1.0
for fact.dat) and then see what you get from EXPLAIN?

regards, tom lane