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

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: Query run in 27s with 15.2 vs 37ms with 14.6
Дата
Msg-id CAMkU=1zVowdPfqPGhL5=oK9PCdZjyw8u6ko8L8xCwegY+znFXQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Query run in 27s with 15.2 vs 37ms with 14.6  (Charles <peacech@gmail.com>)
Список pgsql-bugs
Дерево обсуждения
On Mon, Feb 20, 2023 at 8:38 PM Charles <peacech@gmail.com> wrote:
On Tue, Feb 21, 2023 at 2:25 AM Jeff Janes <jeff.janes@gmail.com> wrote:
On Mon, Feb 20, 2023 at 10:56 AM Charles <peacech@gmail.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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: BUG #17803: Rule "ALSO INSERT ... SELECT ..." fails to substitute default values
Следующее
От: Andres Freund
Дата:
Сообщение: Re: BUG #17800: ON CONFLICT DO UPDATE fails to detect incompatible fields that leads to a server crash