What about:
> 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.
Doesn't that seem weird?
