Re: Parallel append plan instability/randomness

On 8 January 2018 at 10:10, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> regression=# explain select round(avg(aa)), sum(aa) from a_star;
> QUERY PLAN
> -------------------------------------------------------------------------------
> Finalize Aggregate (cost=2.30..2.31 rows=1 width=40)
> -> Gather (cost=2.28..2.29 rows=3 width=40)
> Workers Planned: 3
> -> Partial Aggregate (cost=2.28..2.29 rows=1 width=40)
> -> Parallel Append (cost=0.00..2.20 rows=15 width=4)
> -> Seq Scan on d_star (cost=0.00..1.16 rows=16 width=4)
> -> Seq Scan on f_star (cost=0.00..1.16 rows=16 width=4)
> -> Seq Scan on e_star (cost=0.00..1.07 rows=7 width=4)
> -> Seq Scan on b_star (cost=0.00..1.04 rows=4 width=4)
> -> Seq Scan on c_star (cost=0.00..1.04 rows=4 width=4)
> -> Seq Scan on a_star (cost=0.00..1.03 rows=3 width=4)
> (11 rows)
>
> The above indicates that paths are listed in the order as expected.
> What makes you think that the order of sub-scans can be random? Is it
> possible that the number of rows in child relations can vary across
> runs?
>
> One theory that can explain above failure is that the costs of
> scanning some of the sub-paths is very close due to which sometimes
> the results can vary. If that is the case, then probably using
> fuzz_factor in costs comparison (as is done in attached patch) can
> improve the situation, may be we have to consider some other factors
> like number of rows in each subpath. However, it might be better to
> first somehow reproduce this case and see what is going wrong, any
> ideas?

The fact that b_star gets moved from 5th position to the first
position in the scans, indicates that it's cost shoots up from 1.04 to
a value greater than 1.16. It does not look like a case where two
costs are almost same due to which their positions swap sometimes. I
am trying to figure out what else can it be ...

--
Thanks,
-Amit Khandekar
EnterpriseDB Corporation
The Postgres Database Company