Re: Query run in 27s with 15.2 vs 37ms with 14.6

On Mon, Feb 20, 2023 at 8:38 PM Charles <peacech(at)gmail(dot)com> wrote:

> On Tue, Feb 21, 2023 at 2:25 AM Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>
>> On Mon, Feb 20, 2023 at 10:56 AM Charles <peacech(at)gmail(dot)com> wrote:
>>
>>
>>> The plan from 15.2:
>>>
>>> Nested Loop (cost=63003.26..64440.14 rows=1 width=89) (actual
>>> time=23.234..27407.834 rows=779 loops=1)
>>>
>>
>>
>>> Plan generated by 14.6
>>>
>>> Merge Join (cost=61456.65..61716.09 rows=1 width=89) (actual
>>> time=25.509..37.185 rows=779 loops=1)
>>>
>>
>> Given how close those plan estimates are to each other, I would say your
>> query was very fragile under 14.6, and it was just a matter of luck of how
>> the statistics were computed that you got the better plan on the older
>> version. As opposed to some important coding changes that happened between
>> versions. To verify that, Can you force each version to choose the other
>> plan, for example by fiddling with enable_nestedloop on one and
>> enable_mergjoin on the other?
>>
>>
> Disabling mergejoin on 14.6 and disabling nestedloop on 15.2 causes both
> to use hashjoin where it runs for 37ms in 14.6 and 208ms in 15.2.
>

So please also disable hashjoin on each (as well as what you already
disabled) and see if that forces at least one of them to switch to using
the other one's plan. Then once you get at least one version to show both
plans, it is a question of whether there was just a small difference in
cost estimates which was still large enough to change the rank order or the
plans, or was it a large difference.

The difference in performance of the hashjoin plans is also interesting and
probably worth investigating, but it is not obviously related to the
original performance difference. So how many different things are you
willing to investigate, and in what order?

If you could offer up a dataset which reproduces the problem but can be
shared without confidentiality problems, that could help. Especially if
you can provide a generator for the data which uses random() and
generate_series(), rather than the data itself.

Cheers,

Jeff