Обсуждение: Querying a time range across multiple partitions

On 05 Sep 2014, at 19:31, Cal Heldenbrand <cal@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
efficientat finding a specific time range. It’s quite possible that that makes PG think that “time” is not a very good
candidateto 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.

Number of child tables: 1581

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

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 not exist: timestamp without time zone = timestamp
> without time zone[]
>
> explain analyze select time,event from logins

Probably doesn't help but the above would have to be written:

WHERE time = ANY(
SELECT array_agg(...) FROM ...
)

I don't know whether the planner is smart enough to optimize on "= ANY()" -
whether statically generated or determined at runtime.

I am pretty sure that constraint exclusion must be done during the planning
stages and that you have to pass in a literal set of values (or an array)
that you generate in a previous query.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Querying-a-time-range-across-multiple-partitions-tp5817958p5817989.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Fri, Sep 5, 2014 at 2:17 PM, John R Pierce <pierce@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, 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.

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

John,

> On Fri, Sep 5, 2014 at 2:17 PM, John R Pierce < pierce@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, 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.

can you explain that further? In the end, that argument sounds like it would always be more efficient to use a single
tableand its index instead, rather than partitioning it (log(N) < c*log(N/c) for any c > 1, if I'm not totally lost
today).

So, is there any insights of how many partitions are still useful?

I have tables with roughly 1000 partitions and did not have any issues so far. Even with having INSERT rules that are
linedup worst-case (from past to current, while data is always being inserted for the current date), I haven't seen any
considerabledegradation of INSERT performance so far. 

Thanks,
Andreas

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
tableand 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 indeed would.

good reasons for partitioning include...

  * efficient date based bulk deletion (we have a very large table that
has 6 months retention, so we partition by week and delete the oldest
week when a new week starts... dropping a partition is far faster than
deleting 20 million records by date)

  * needing to put data across several tablespaces - I haven't had to do
this.

  * more efficient vacuuming - really really large tables, like 100 GB,
take a LONG time to vacuum.   sane sized partitions will vacuum in less
time, and since older time-based partitions aren't typically updated,
they can be frozen.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast