Re: Parallel append plan instability/randomness

On Sun, Jan 7, 2018 at 11:40 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> 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.

This isn't an acceptable solution because sorting requires that the
comparison operator satisfy the transitive property; that is, if a = b
and b = c then a = c. With your proposed comparator, you could have a
= b and b = c but a < c. That will break stuff.

It seems like the obvious fix here is to use a query where the
contents of the partitions are such that the sorting always produces
the same result. We could do that either by changing the query or by
changing the data in the partitions or, maybe, by inserting ANALYZE
someplace.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company