explain analyze select time,event from logins where username='bob' and hash='1234' and time > (current_date - interval '1 week’)::timestamp without time zone;
Also, you don’t appear to be having an index that starts from “time”, so none of the indexes will be particularly efficient at finding a specific time range. It’s quite possible that that makes PG think that “time” is not a very good candidate to filter on, simply because the optimizer doesn’t look that far.
That didn't seem to work either. The thought did occur to me that the query planner wasn't using my combined column indexes. I tried adding just a btree index on time and it still did the same problem.
Your example query there also goes back to 2010 for scanning tables.
Interestingly enough, this query actually works:
# explain analyze select time,event from logins where username='bob' and hash='1234' and time > '2014-08-29';
Result (cost=0.00..8.21 rows=8 width=20) (actual time=0.074..0.074 rows=0 loops=1) -> Append (cost=0.00..8.21 rows=8 width=20) (actual time=0.074..0.074 rows=0 loops=1) -> Seq Scan on logins (cost=0.00..0.00 rows=1 width=66) (actual time=0.002..0.002 rows=0 loops=1) Filter: (("time" > '2014-08-29 00:00:00'::timestamp without time zone) AND ((username)::text = 'bob'::text) AND ((hash)::text = '1234'::text)) -> Index Scan using logins_20140829_username on logins_20140829 logins (cost=0.00..1.21 rows=1 width=14) (actual time=0.021..0.021 rows=0 loops=1) Index Cond: ((username)::text = 'bob'::text)
So it seems to me that the query parser isn't preprocessing "current_date - interval", but a statically defined time span *does* work.