Обсуждение: Understanding EXPLAIN ANALYZE estimates when loops != 1
Hi all, I could use some help interpreting EXPLAIN ANALYZE output. -> Index Scan using ix_foo on t1 (cost=0.69..68.57 rows=3283 width=105) (actual time=0.006..0.918 rows=3760 loops=94) The actual rows returned by this plan node ~= 3760 * 94 = 353,440. Did postgres expect (estimate) 3283 rows from this join,or 3283 * 94 = 308,602? Same question for this node. -> Parallel Index Scan using pk_xyz on xyz (cost=0.29..2354.67 rows=54285 width=25) (actual time=0.049..6.326 rows=14864loops=5) Actual rows ~= 14864 * 5 = 74,320, estimate = 54285 or 54285 * 5? THanks Philip
On Thu, 20 Aug 2020 at 09:55, Philip Semanchuk <philip@americanefficient.com> wrote: > I could use some help interpreting EXPLAIN ANALYZE output. > > -> Index Scan using ix_foo on t1 (cost=0.69..68.57 rows=3283 width=105) (actual time=0.006..0.918 rows=3760 loops=94) > > The actual rows returned by this plan node ~= 3760 * 94 = 353,440. Yes. It's total rows / loops rounded to the nearest integer number. > Did postgres expect (estimate) 3283 rows from this join, or 3283 * 94 = 308,602? Yes, that's the case at least when the node is not a Parallel node. If this index scan was part of a parameterized nested loop, then you'll see the estimate of the number of expected loops from the outer side of the join. > Same question for this node. > > -> Parallel Index Scan using pk_xyz on xyz (cost=0.29..2354.67 rows=54285 width=25) (actual time=0.049..6.326 rows=14864loops=5) > > Actual rows ~= 14864 * 5 = 74,320, estimate = 54285 or 54285 * 5? So parallel plans are a bit more complex. The row estimates are the total estimated rows / the amount of workers we expect to do useful work. You might expect the divisor there to be an integer number since you can't really have 0.5 workers. However, it's more complex than that since the leader has other tasks to take care of such as pulling tuples from workers, it's not dedicated to helping out. If you're into reading C code, then there's more information in https://github.com/postgres/postgres/blob/master/src/backend/optimizer/path/costsize.c#L5699 , if you hunt around for usages of that function then you'll see the estimated row counts are divided by the return value of that function. David
> On Aug 19, 2020, at 6:24 PM, David Rowley <dgrowleyml@gmail.com> wrote: > > On Thu, 20 Aug 2020 at 09:55, Philip Semanchuk > <philip@americanefficient.com> wrote: >> I could use some help interpreting EXPLAIN ANALYZE output. >> >> -> Index Scan using ix_foo on t1 (cost=0.69..68.57 rows=3283 width=105) (actual time=0.006..0.918 rows=3760 loops=94) >> >> The actual rows returned by this plan node ~= 3760 * 94 = 353,440. > > Yes. It's total rows / loops rounded to the nearest integer number. > >> Did postgres expect (estimate) 3283 rows from this join, or 3283 * 94 = 308,602? > > Yes, that's the case at least when the node is not a Parallel node. > If this index scan was part of a parameterized nested loop, then > you'll see the estimate of the number of expected loops from the outer > side of the join. Thanks, I was wondering where the 94 came from. > Same question for this node. >> >> -> Parallel Index Scan using pk_xyz on xyz (cost=0.29..2354.67 rows=54285 width=25) (actual time=0.049..6.326 rows=14864loops=5) >> >> Actual rows ~= 14864 * 5 = 74,320, estimate = 54285 or 54285 * 5? > > So parallel plans are a bit more complex. The row estimates are the > total estimated rows / the amount of workers we expect to do useful > work. You might expect the divisor there to be an integer number > since you can't really have 0.5 workers. However, it's more complex > than that since the leader has other tasks to take care of such as > pulling tuples from workers, it's not dedicated to helping out. Sounds like it help to set max_parallel_workers = 1 before running EXPLAIN ANALYZE in order to simplify the numbers, yes?Or is there a possibility that doing so would send the planner down an entirely different path? > > If you're into reading C code, then there's more information in > https://github.com/postgres/postgres/blob/master/src/backend/optimizer/path/costsize.c#L5699 > , if you hunt around for usages of that function then you'll see the > estimated row counts are divided by the return value of that function. Yes, I’ve spent some time reading that file and its relatives. It’s been helpful. Much appreciated Philip
On Fri, 21 Aug 2020 at 03:21, Philip Semanchuk <philip@americanefficient.com> wrote: > > > > > On Aug 19, 2020, at 6:24 PM, David Rowley <dgrowleyml@gmail.com> wrote: > > > > On Thu, 20 Aug 2020 at 09:55, Philip Semanchuk > > <philip@americanefficient.com> wrote: > >> -> Parallel Index Scan using pk_xyz on xyz (cost=0.29..2354.67 rows=54285 width=25) (actual time=0.049..6.326 rows=14864loops=5) > >> > >> Actual rows ~= 14864 * 5 = 74,320, estimate = 54285 or 54285 * 5? > > > > So parallel plans are a bit more complex. The row estimates are the > > total estimated rows / the amount of workers we expect to do useful > > work. You might expect the divisor there to be an integer number > > since you can't really have 0.5 workers. However, it's more complex > > than that since the leader has other tasks to take care of such as > > pulling tuples from workers, it's not dedicated to helping out. > > Sounds like it help to set max_parallel_workers = 1 before running EXPLAIN ANALYZE in order to simplify the numbers, yes?Or is there a possibility that doing so would send the planner down an entirely different path? Since the costs are also divided (see costsize.c) and the path costs are the basis on which paths the planner will choose to use, you're likely to see the plan changing. max_parallel_workers set to 1 wouldn't have been very helpful anyway since that's the leader process + 1 parallel worker resulting in the divisor of 1.7. If you need to, you can just reverse engineer the costs from assuming what get_parallel_divisor() will have returned. You can see it expects each worker to take up 30% of its time. leader + 1 worker = 1.7, leader + 2 workers = 2.4, leader + 3 workers = 3.1, leader + 4 workers = 4. You'll know the number of workers from "Workers Planned" in the EXPLAIN output. You'd need to do something else if you happen to run with parallel_leader_participation = off. David