Re: Querying a time range across multiple partitions

On 05 Sep 2014, at 19:31, Cal Heldenbrand <cal(at)fbsdata(dot)com> wrote:

> I'm attempting to run a query that looks something like this:
>
> explain analyze select time,event from logins
> where username='bob' and hash='1234' and time > current_date - interval '1 week';
>
> Result (cost=0.00..765.11 rows=1582 width=14)
> -> Append (cost=0.00..765.11 rows=1582 width=14)
> -> Seq Scan on logins (cost=0.00..0.00 rows=1 width=66)
> Filter: (((username)::text = 'bob'::text) AND ((hash)::text = '1234'::text) AND ("time" > (('now'::text)::date - '7 days'::interval)))
> -> Index Scan using logins_20100501_username_time on logins_20100501 logins (cost=0.01..0.48 rows=1 width=14)
> ...
>
> This shows that it's attempting to run the query against all of my 1500 child tables.

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.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.