On 05 Sep 2014, at 19:31, Cal Heldenbrand <c...@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 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.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to