Re: Index not being used unless enable_seqscan=false - Mailing list pgsql-general

Shane <shane-pgsql@cm.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