Обсуждение: 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? 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.foertsch@gmx.net> wrote:
On 13/11/13 13:49, Gabriel Sánchez Martínez wrote:It probably does. According to the "analyze" part of the query plan it
>> 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.
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
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 >