On Wed, Jul 17, 2019 at 5:20 PM Darafei "Komяpa" Praliaskouski <me@komzpa.net> wrote: > Indeed, it seems I failed to minimize my example. > > Here is the actual one, on 90GB table with 16M rows: > https://gist.github.com/Komzpa/8d5b9008ad60f9ccc62423c256e78b4c > > I can share the table on request if needed, but hope that plan may be enough.
What I taught the planner to do here had to do with making the costing more accurate for cases like this. It now figures out that if it's going to stick a Gather in at that point, computing the expressions below the Gather rather than above the Gather makes a difference to the cost of that plan vs. other plans. However, it still doesn't consider any more paths than it did before; it just costs them more accurately. In your first example, I believe that the planner should be able to consider both GroupAggregate -> Gather Merge -> Sort -> Parallel Seq Scan and GroupAggregate -> Sort -> Gather -> Parallel Seq Scan, but I think it's got a fixed idea about which fields should be fed into the Sort. In particular, I believe it thinks that sorting more data is so undesirable that it doesn't want to carry any unnecessary baggage through the Sort for any reason. To solve this problem, I think it would need to cost the second plan with projection done both before the Sort and after the Sort and decide which one was cheaper.
This class of problem is somewhat annoying in that the extra planner cycles and complexity to deal with getting this right would be useless for many queries, but at the same time, there are a few cases where it can win big. I don't know what to do about that.
A heuristic I believe should help my case (and I hardly imagine how it can break others) is that in presence of Gather, all the function calls that are parallel safe should be pushed into it. In a perfect future this query shouldn't even have a subquery that I have extracted for the sake of OFFSET 0 demo. Probably as a single loop that in case of presence of a Gather tries to push down all the inner part of the nested functions call that is Parallel Safe.
If we go as far as starting more workers, it really makes sense to load them with actual work and not only wait for the master process.
