On 05 Sep 2014, at 19:31, Cal Heldenbrand <cal@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
