Shane <shane-pgsql@xxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
