Re: Timestamp indexes

"Mitch Vincent" <mitch(at)venux(dot)net> writes:
> select * from applicants as a where (a.created::date > '05-01-2000' or
> a.resubmitted::date > '05-01-2000') order by (case when a.resubmitted >
> a.created then a.resubmitted else a.created end) desc limit 10 offset 0

> There is one of the queries.. I just remembered that the order by was added
> since last time I checked it's PLAN (in the 6.5.X days) -- could that be the
> problem?

Probably. With the ORDER BY in there, the LIMIT no longer applies
directly to the scan (since a separate sort step is going to be
necessary). Now it's looking at a lot more data to be fetched by
the scan, not just 10 records, so the indexscan becomes less attractive.

Might be interesting to compare the estimated and actual runtimes
between this query and what you get with "set enable_seqscan to off;"

regards, tom lane