Custom vs generic plan | Postgres.FM 096 |

[ 🇬🇧_🇺🇸 Check out the subtitles - we now edit them, ChatGPT+manually! You can also try KZbin'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:
* PREPARE www.postgresql.org/docs/curre...
* track_activity_query_size www.postgresql.org/docs/curre...
* plan_cache_mode www.postgresql.org/docs/curre...
* EXPLAIN (GENERIC_PLAN) www.postgresql.org/docs/curre...
* EXPLAIN (GENERIC_PLAN) in PostgreSQL 16 (blog post by Laurenz from Cybertec) www.cybertec-postgresql.com/e...
* Running EXPLAIN on any query, even with $1 parameters (blog post and video by Lukas Fittl of pganalyze) pganalyze.com/blog/5mins-post...
* EXPLAIN from pg_stat_statements, how to get the generic plan (blog post by Franck Pashto of Yugabyte) dev.to/yugabyte/explain-from-...
* Rework query relation permission checking (commit by Amit Langote) git.postgresql.org/gitweb/pos...
* Partition pruning, prepared statements and generic vs custom query plans (a follow up blog post and video by Lukas) pganalyze.com/blog/5mins-post...
* Our episode on over-indexing (inc LWLock discussion) postgres.fm/episodes/over-ind...
* “The year of the lock manager’s revenge” (from blog post by Jeremy Schneider) ardentperf.com/2024/03/03/pos...
~~~
What did you like or not like? What should we discuss next time? Let us know in the comments, or by tweeting us on @postgresfm / postgresfm , @samokhvalov / samokhvalov and @michristofides / michristofides
~~~
Postgres FM is brought to you by:
- Nikolay Samokhvalov, founder of Postgres.ai postgres.ai/
- Michael Christofides, founder of pgMustard pgmustard.com/
~~~
This is the video version. Check out postgres.fm to subscribe to the audio-only version, to see the transcript, guest profiles, and more.

[@pdougall1]

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)

[@kirkwolak6735]

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.