On Thu, Aug 11, 2005 at 12:10:33AM -0400, Tom Lane wrote:
> 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
timezone);
>
> 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.
Hi Tom,
The version being used is 7.4.7. However, as an
experiment, I ran pg8 on a different port and loaded the
dataset. It needed a vacuum analyze but after that pg8 was
using the index and got the row estimate correct.
Thanks for the pointer,
Shane