Re: Querying a time range across multiple partitions

On 05 Sep 2014, at 19:31, Cal Heldenbrand <cal@fbsdata.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
efficientat finding a specific time range. It’s quite possible that that makes PG think that “time” is not a very good
candidateto 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.