Re: Optimization with dates

Jason Earl <jason(dot)earl(at)simplot(dot)com> writes:
> I have a similar table (~16M rows) with an indexed timestamp, and have
> had similar problems. I have found that even when I am using a
> constant timestamp like in this query.

> SELECT * FROM caseweights1 WHERE dt > '2001-11-01';

> I start getting sequential scans with 7.1.3 long before they are
> faster than index based queries.

Just out of curiosity, do the indexed timestamps correlate closely to
the physical order of the table? I'd expect that to happen if you
are timestamping records by insertion time and there are few or no
updates.

7.1 and before assume that the index order is random with respect to
the physical order, which is a good conservative assumption ... but it
results in drastically overestimating the cost of an indexscan when
strong correlation exists. 7.2 keeps some statistics about ordering
correlation, and may perhaps do better with this sort of situation.
(I have no doubt that its estimates will need further tweaking, but
at least the effect is somewhat accounted for now.)

regards, tom lane