From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Querying a time range across multiple partitions |
Date: | 2014-09-07 20:20:36 |
Message-ID: | 540CBE14.2000305@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 9/7/2014 12:55 PM, Jeff Janes wrote:
> On Fri, Sep 5, 2014 at 2:17 PM, John R Pierce <pierce(at)hogranch(dot)com
> <mailto:pierce(at)hogranch(dot)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, of
> data will millions of rows/day.
>
>
> I've used more than that many for testing purposes, and there was
> little problem. The main thing is that your insert trigger (if you
> have one on the master table) needs to be structured as a binary
> search-like nesting of if..elsif, not a linear-searching like
> structure. Unless of course almost all inserts go into the newest
> partition, then it might make more sense to do the linear search with
> that being the first test. But for performance, better to just insert
> directly into the correct child table.
any select that can't be preplanned to a specific child will need to
check all 1500 children. this is far less efficient than checking,
say, 50 and letting the b-tree index of each child reject or narrow down
to the specific row(s). The one is roughly 1500*log(N/1500) while the
other is 50*log(N/50) at least to a first order approximation.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
From | Date | Subject | |
---|---|---|---|
Next Message | Sergey Burladyan | 2014-09-07 22:21:39 | xlog min recovery request is past current point -- is it real problem? |
Previous Message | Jeff Janes | 2014-09-07 19:55:24 | Re: Querying a time range across multiple partitions |