Re: Query run in 27s with 15.2 vs 37ms with 14.6 - Mailing list pgsql-bugs

From Jeff Janes
Subject Re: Query run in 27s with 15.2 vs 37ms with 14.6
Date
Msg-id CAMkU=1xDZnL_=2BrMnOd2FgZ62G=qqhFD+ZtxC-74Mnu-qOPuQ@mail.gmail.com
Whole thread Raw
In response to Query run in 27s with 15.2 vs 37ms with 14.6  (Charles <peacech@gmail.com>)
Responses Re: Query run in 27s with 15.2 vs 37ms with 14.6  (Charles <peacech@gmail.com>)
List pgsql-bugs
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?

Cheers,

Jeff

pgsql-bugs by date:

Previous
From: Charles
Date:
Subject: Re: Query run in 27s with 15.2 vs 37ms with 14.6
Next
From: Stephen Frost
Date:
Subject: Re: Query run in 27s with 15.2 vs 37ms with 14.6