On 8 January 2018 at 10:10, Amit Kapila <amit.kapil...@gmail.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
