Re: Index not being used unless enable_seqscan=false

Shane <shane-pgsql(at)cm(dot)nu> writes:
> I am working with a simple table and query abut cannot seem
> to get it to use the index I have created.
> ...
> explain analyze select msgid from seen where msgtime < cast(now() - interval '6 months' as timestamp(0) without time zone);

As some other people already pointed out, the problem is the horrible
misestimate of the number of matching rows. You did not say your
Postgres version, but I'm betting it's pre-8.0. Versions before 8.0
would not assume that they could get any useful statistical info from
an expression involving now() (or in general, any non-immutable
function). The default assumption in such cases is that a lot of
rows are retrieved --- too many for an indexscan.

If you cannot update to 8.0.* at the moment, a workaround is to do the
timestamp calculation on the client side so that you can send over
a query that's just a comparison to a constant:

... where msgtime < '2005-02-14 ...'::timestamp;

regards, tom lane