Re: [GENERAL] Querying a time range across multiple partitions

2014-09-08 Thread Jeff Janes
On Mon, Sep 8, 2014 at 1:40 PM, Andreas Brandl wrote: > John, > > > On Fri, Sep 5, 2014 at 2:17 PM, John R Pierce < pie...@hogranch.com > > > wrote: > >> On 9/5/2014 10:31 AM, Cal Heldenbrand wrote: > >> Number of child tables: 1581 > >> that's an insane number of children. We try and limit it to

Re: [GENERAL] Querying a time range across multiple partitions

2014-09-08 Thread John R Pierce
On 9/8/2014 1:40 PM, Andreas Brandl wrote: can you explain that further? In the end, that argument sounds like it would always be more efficient to use a single table and its index instead, rather than partitioning it (log(N) < c*log(N/c) for any c > 1, if I'm not totally lost today). it ind

Re: [GENERAL] Querying a time range across multiple partitions

2014-09-08 Thread Andreas Brandl
John, > On Fri, Sep 5, 2014 at 2:17 PM, John R Pierce < pie...@hogranch.com > > wrote: >> On 9/5/2014 10:31 AM, Cal Heldenbrand wrote: >> Number of child tables: 1581 >> that's an insane number of children. We try and limit it to 50 or so >> child tables, for instance, 6 months retention by week,

Re: [GENERAL] Querying a time range across multiple partitions

2014-09-08 Thread Cal Heldenbrand
Thanks Jeff! That's what I wanted to confirm, that I need to hard code / pregenerate my dates in the query. I was mainly curious why it didn't work with current_date, and that answers it. And BTW, all my inserts happen on the most recent table, so my insert trigger with a linear search is ordere

Re: [GENERAL] Querying a time range across multiple partitions

2014-09-07 Thread John R Pierce
On 9/7/2014 12:55 PM, Jeff Janes wrote: On Fri, Sep 5, 2014 at 2:17 PM, John R Pierce > wrote: On 9/5/2014 10:31 AM, Cal Heldenbrand wrote: Number of child tables: 1581 that's an insane number of children.We try and limit it to 50 or so child ta

Re: [GENERAL] Querying a time range across multiple partitions

2014-09-07 Thread Jeff Janes
On Fri, Sep 5, 2014 at 2:17 PM, John R Pierce wrote: > On 9/5/2014 10:31 AM, Cal Heldenbrand wrote: > > Number of child tables: 1581 > > > that's an insane number of children.We try and limit it to 50 or so > child tables, for instance, 6 months retention by week, of data will > millions of

Re: [GENERAL] Querying a time range across multiple partitions

2014-09-07 Thread Jeff Janes
On Fri, Sep 5, 2014 at 10:31 AM, Cal Heldenbrand wrote: > > 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

Re: [GENERAL] Querying a time range across multiple partitions

2014-09-05 Thread David G Johnston
Cal Heldenbrand wrote > explain analyze select time,event from logins > where username='bob' and hash='1234' and time in ( > select array_agg(series) > from generate_series(current_date - interval '3 days', current_date, > interval '1 day') > as u(series) > ); > ERROR: operator does

Re: [GENERAL] Querying a time range across multiple partitions

2014-09-05 Thread Cal Heldenbrand
This particular use case is for user behavior data mining. The hardware is beefy, and has tablespaces split out onto SSD/spindle for new & old data. All of my queries are pretty much a nightly cron process, and I don't really care too much about the speed. Scanning the full 4 years of data takes

Re: [GENERAL] Querying a time range across multiple partitions

2014-09-05 Thread Cal Heldenbrand
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

Re: [GENERAL] Querying a time range across multiple partitions

2014-09-05 Thread John R Pierce
On 9/5/2014 10:31 AM, Cal Heldenbrand wrote: Number of child tables: 1581 that's an insane number of children.We try and limit it to 50 or so child tables, for instance, 6 months retention by week, of data will millions of rows/day. -- john r pierce

Re: [GENERAL] Querying a time range across multiple partitions

2014-09-05 Thread Alban Hertroys
On 05 Sep 2014, at 19:31, Cal Heldenbrand 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

[GENERAL] Querying a time range across multiple partitions

2014-09-05 Thread Cal Heldenbrand
Hi everyone, I'm trying to run a select query from a span of child partitions, separated out in daily tables, in Postgres 9.1.5. The parent looks like this: # \d logins Table "public.logins" Column|Type | Modifiers -+---