Re: Partitioned table question

Hi,

we have a table partitioned by time. Each month goes into a separate
child table. Primary key in each table is (underlying, ts). The
resulting index is perfect for ordering like in the query below. Each
child table has a constraint like:

CHECK(ts>= '2011-1-1' and ts<'2011-1-1'::DATE + interval '1 month')

Now, we have queries of this type:

SELECT * FROM tick
WHERE underlying = 'R_50' AND ts <= '2013-05-02'
ORDER BY ts DESC LIMIT 100

The query plan for this is at http://explain.depesz.com/s/fB6

According to this plan it fetches all the result tuples from tick_2013_4
which is fine because tick_2013_5 obviously does not contain matches.

My question is, why does it then try to fetch one row from every other
index? Can that be avoided without a lower bound on ts?

Thanks,
Torsten

On 11/13/2013 06:22 AM, Torsten Förtsch wrote:
> Hi,
>
> we have a table partitioned by time. Each month goes into a separate
> child table. Primary key in each table is (underlying, ts). The
> resulting index is perfect for ordering like in the query below. Each
> child table has a constraint like:
>
> CHECK(ts>= '2011-1-1' and ts<'2011-1-1'::DATE + interval '1 month')
>
> Now, we have queries of this type:
>
> SELECT * FROM tick
> WHERE underlying = 'R_50' AND ts <= '2013-05-02'
> ORDER BY ts DESC LIMIT 100
In the query plan the condition shown is ... AND ts <= '2013-05-01' Did
you mean 01 in the above query?
>
> The query plan for this is at http://explain.depesz.com/s/fB6
>
> According to this plan it fetches all the result tuples from tick_2013_4
> which is fine because tick_2013_5 obviously does not contain matches.
Since the constraint is not strict (i.e. you allow dates equal to
2013-05-01 to pass), the 2013-05 table has to be scanned.
>
> My question is, why does it then try to fetch one row from every other
> index? Can that be avoided without a lower bound on ts?
If you don't set a lower bound, since every other table has dates below
2013-05-01, they have to be scanned too. I'm not sure what happens on
actual execution if it searches in '2013_4' first and finds 100 or more
rows. I don't know if the query planner uses constraint exclusion rules
to figure out the order in which tables will be scanned. I suspect not,
because I've read and seen that the constraint exclusion rules behavior
is rather simple. If you set a lower bound the constraint exclusion
rule should kick in and limit the tables searched. Have you tried that?

>
> Thanks,
> Torsten
>

On 11/13/2013 06:22 AM, Torsten Förtsch wrote:
> Hi,
>
> we have a table partitioned by time. Each month goes into a separate
> child table. Primary key in each table is (underlying, ts). The
> resulting index is perfect for ordering like in the query below. Each
> child table has a constraint like:
>
> CHECK(ts>= '2011-1-1' and ts<'2011-1-1'::DATE + interval '1 month')
>
> Now, we have queries of this type:
>
> SELECT * FROM tick
> WHERE underlying = 'R_50' AND ts <= '2013-05-02'
> ORDER BY ts DESC LIMIT 100
In the query plan the condition shown is ... AND ts <= '2013-05-01' Did
you mean 01 in the above query?
>
> The query plan for this is at http://explain.depesz.com/s/fB6
>
> According to this plan it fetches all the result tuples from tick_2013_4
> which is fine because tick_2013_5 obviously does not contain matches.
Since the constraint is not strict (i.e. you allow dates equal to
2013-05-01 to pass), the 2013-05 table has to be scanned.
>
> My question is, why does it then try to fetch one row from every other
> index? Can that be avoided without a lower bound on ts?
If you don't set a lower bound, since every other table has dates below
2013-05-01, they have to be scanned too. I'm not sure what happens on
actual execution if it searches in '2013_4' first and finds 100 or more
rows. I don't know if the query planner uses constraint exclusion rules
to figure out the order in which tables will be scanned. I suspect not,
because I've read and seen that the constraint exclusion rules behavior
is rather simple. If you set a lower bound the constraint exclusion
rule should kick in and limit the tables searched. Have you tried
that? It should not take long.
>
> Thanks,
> Torsten
>

On 13/11/13 13:49, Gabriel Sánchez Martínez wrote:
>> My question is, why does it then try to fetch one row from every other
>> index? Can that be avoided without a lower bound on ts?

> If you don't set a lower bound, since every other table has dates below
> 2013-05-01, they have to be scanned too. I'm not sure what happens on
> actual execution if it searches in '2013_4' first and finds 100 or more
> rows. I don't know if the query planner uses constraint exclusion rules
> to figure out the order in which tables will be scanned.

It probably does. According to the "analyze" part of the query plan it
does not find any match in 2013_5. But from 2013_4 it fetches 100 rows.

-> Index Scan Backward using tick_2013_4_pkey on tick_2013_4 tick
(cost=0.00..5025184.53 rows=1336481 width=40)
(actual time=0.047..0.124 rows=100 loops=1) <== rows=100

Of course, it's a good idea to add a lower bound to the query.

I also know that the planner does not know how many rows it can fetch
from each table (it can have a quite accurate guess though). So, the
plan must include all tables before and including 2013_5.

The question, however, was why does the executor try to fetch rows from
the other tables at all.

Torsten

On 11/13/2013 08:26 AM, Torsten Förtsch wrote:
> On 13/11/13 13:49, Gabriel Sánchez Martínez wrote:
>>> My question is, why does it then try to fetch one row from every other
>>> index? Can that be avoided without a lower bound on ts?
>> If you don't set a lower bound, since every other table has dates below
>> 2013-05-01, they have to be scanned too. I'm not sure what happens on
>> actual execution if it searches in '2013_4' first and finds 100 or more
>> rows. I don't know if the query planner uses constraint exclusion rules
>> to figure out the order in which tables will be scanned.
> It probably does. According to the "analyze" part of the query plan it
> does not find any match in 2013_5. But from 2013_4 it fetches 100 rows.
>
> -> Index Scan Backward using tick_2013_4_pkey on tick_2013_4 tick
> (cost=0.00..5025184.53 rows=1336481 width=40)
> (actual time=0.047..0.124 rows=100 loops=1) <== rows=100
>
> Of course, it's a good idea to add a lower bound to the query.
>
> I also know that the planner does not know how many rows it can fetch
> from each table (it can have a quite accurate guess though). So, the
> plan must include all tables before and including 2013_5.
>
> The question, however, was why does the executor try to fetch rows from
> the other tables at all.
I suspect it is because the checks are used just for checking and table
exclusion, not for ordering. The planner does not understand the logic
of how your check constraints are set up, so it does not have a
guarantee that after scanning through 2013_4 there will be no more rows
that should enter the result set in other tables. So all tables pass
the check constraints and none are excluded, and then index scans are
used to figure out everything else from there on.

I don't work with the PostgreSQL source code (I'm just answering based
on what I've observed in my experience as a user, experimenting with
partitioning and constraint exclusion), so perhaps others in the list
who are closer to the source can chime in.

-Gabriel

>
> Torsten

On Wed, Nov 13, 2013 at 5:26 AM, Torsten Förtsch
<torsten(dot)foertsch(at)gmx(dot)net>wrote:

> On 13/11/13 13:49, Gabriel Sánchez Martínez wrote:
> >> My question is, why does it then try to fetch one row from every other
> >> index? Can that be avoided without a lower bound on ts?
>
> > If you don't set a lower bound, since every other table has dates below
> > 2013-05-01, they have to be scanned too. I'm not sure what happens on
> > actual execution if it searches in '2013_4' first and finds 100 or more
> > rows. I don't know if the query planner uses constraint exclusion rules
> > to figure out the order in which tables will be scanned.
>
> It probably does. According to the "analyze" part of the query plan it
> does not find any match in 2013_5. But from 2013_4 it fetches 100 rows.
>
> -> Index Scan Backward using tick_2013_4_pkey on tick_2013_4 tick
> (cost=0.00..5025184.53 rows=1336481 width=40)
> (actual time=0.047..0.124 rows=100 loops=1) <== rows=100
>
> Of course, it's a good idea to add a lower bound to the query.
>
> I also know that the planner does not know how many rows it can fetch
> from each table (it can have a quite accurate guess though). So, the
> plan must include all tables before and including 2013_5.
>
> The question, however, was why does the executor try to fetch rows from
> the other tables at all.
>

The planner uses the check constraints to reason about the relation between
each partition separately and the query, not between the different
partitions. So while it may be possible to know that all rows in 2013_4
must be greater than all in 2013_3, it doesn't make use of that, instead
taking the greatest value from each partition and putting it in a priority
queue. So the one row from each table acts as a sentinel to prove that more
rows from that table are not needed.

Cheers,

Jeff

On 13/11/13 20:21, Jeff Janes wrote:
> The planner uses the check constraints to reason about the relation
> between each partition separately and the query, not between the
> different partitions. So while it may be possible to know that all rows
> in 2013_4 must be greater than all in 2013_3, it doesn't make use of
> that, instead taking the greatest value from each partition and putting
> it in a priority queue. So the one row from each table acts as a
> sentinel to prove that more rows from that table are not needed.

That makes perfect sense. Thank you very much.

Torsten