Re: Unwanted expression simplification in PG12b2

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.