Custom vs generic plan | Postgres.FM 096 | #PostgreSQL #Postgres podcast

[ 🇬🇧_🇺🇸 Check out the subtitles – we now edit them, ChatGPT+manually! You can also try YouTube's auto-translation of them from English to your language; try it and share it with people interested in Postgres!]

Nikolay and Michael discuss custom and generic planning in prepared statements — how it works, how issues can present themselves, some ways to view the generic plan, and some benefits of avoiding planning (not just time).

Here are some links to things they mentioned:

~~~

~~~

Postgres FM is brought to you by:

~~~

[pdougall]

Ya'll are great! Its really important to hear professional db people talking about how all of this works in practice. Beyond a basic explanation that can be found in books (books are also really important btw)

[kirkwolak]

So, I was wondering... Wouldn't it be nice if there were 2-3 types of plans based on some of the values of the parameters, so you get the most optimum plan and maybe the optimizer does Parameter Peeking to determine which of the X plans to choose...

And then I realized. Wow... The application could do this. Create 3 prepared statements for the same query. And execute against the one TUNED for the query parameter types forcing the best plan to be used by design... Hmmm...

We have this situation. We have a complicated search. But when the value we are searching for is small (lots of hits) vs large (few hits). It wants to choose the wrong one after a few queries and then a switch. Unfortunately, this is inside of a Procedure where the statement is prepared around us. We would have to basically duplicate the complex query just to make the condition so that it executes the right right way. But I might still try that.