From: | Cal Heldenbrand <cal(at)fbsdata(dot)com> |
---|---|
To: | Alban Hertroys <haramrae(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Querying a time range across multiple partitions |
Date: | 2014-09-05 21:45:34 |
Message-ID: | CAAcwKhdKJtGwNfRsF9sp2A4iFG9xnrmXbaki4PX6YnqoDqPnxg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Cal Heldenbrand | 2014-09-05 21:50:53 | Re: Querying a time range across multiple partitions |
Previous Message | John R Pierce | 2014-09-05 21:17:48 | Re: Querying a time range across multiple partitions |