Re: BUG #16885: PostgreSQL upgrade to 12+ changes hash join to slow nested loop

po 22. 2. 2021 v 15:25 odesílatel Tavin Cole <tavin(dot)cole(at)gmail(dot)com> napsal:

> On Mon, Feb 22, 2021 at 3:21 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>> you don't need share query - just send an execution plan -
>> https://explain.depesz.com/ can do anonymization of the execution plan,
>> if it is necessary.
>>
>
> v11 plan: https://explain.depesz.com/s/P5Jj
> v12 plan: https://explain.depesz.com/s/KPr
>

You can see lot of underestimations and overestimations

Hash Join
<http://www.depesz.com/2013/05/09/explaining-the-unexplainable-part-3/#hash-join>
(cost=15,018.84..89,129.05
rows=84,049 width=220) (actual time=2,930.751..7,516.315 rows=778,056
loops=1)

But probably the significant problem is underestimation of aggregation

HashAggregate
<http://www.depesz.com/2013/05/09/explaining-the-unexplainable-part-3/#hash-aggregate>
(cost=275,174.24..278,605.86
rows=343,162 width=487) (actual time=269.775..1,943.691 rows=1,053,462
loops=57)

The estimated cost is less than in real cost, and then the cost of a nested
loop can be cheaper.

Regards

Pavel

> Cheers,
> Tavin
>