Something Ain’t Right

I’ve been reading the book Introduction to PostgreSQL for the data professional by Ryan Booz and Grant Fritchey. Something they said completely shocked me. “PostgreSQL does not have a query plan cache.” When I read that, as a SQL Server DBA, my eyes genuinely got wide and I had to know more. They go on to talk about how each time a query is executed it compiles a new plan unless it’s prepared. My first thought was “Oh geeze, this sounds a lot like plan cache bloat from ORM queries” It was hard for me to continue reading the chapter because my naturally curious mind wanted to dive in right away and learn more. So, that’s what I did and I had to share it, cause it’s really cool.

We Have Food At Home

In the SQL Server world, the plan cache is a very critical component. It’s SQL Server telling a query don’t bother compiling a plan, I’ve already got a plan that’ll work. Kind of like a kid telling their mom that they want McDonald’s and mom classically replies with “No, we have food at home.”

Cooking From Scratch

So, when a normal query runs, Postgres cooks from scratch every single time. It’ll parse, analyze, rewrite, come up with a plan, and then executes it. Those same five steps happen over, and over, and over again. Postgres will make an entire lasagna for a single person, and when the next customer walks up, instead of grabbing another slice, it creates a fresh meal all over again. Sounds wasteful in my mind.

However, this means that with a normal unprepared query (will talk about this in the next section) Postgres is planning against the actual state of the data, not against whatever assumptions were baked in the first time the query ran. I know I don’t like it when queries use stale plans from last week’s data distribution. Oh yeah, that reminds me, have you updated your stats lately?

PREPARE For Liftoff

There’s a really cool feature in Postgres when you use the PREPARE clause. This will split the work into two stages. When you run PREPARE, Postgres will parse, analyze and rewrite the statement. Then, when you EXECUTE later, it just plans and executes. This is probably the closest thing Postgres has to what I’d think of as plan caching. It does the work once to generate the plan, and then will reuse the plan for that query later on.

But here’s the trippy part that goes beyond caching or not caching a plan. Postgres has to decide between a generic plan and a custom plan. A custom plan gets built fresh and will use the actual parameter values that are passed in, while a generic plan gets built once and will be reused as is.

What really caught my attention is that Postgres doesn’t just blindly pick the generic plan. The first five executions use custom plans. After that, it averages the estimated cost of those five executions and compares that to the estimated cost of a generic plan. Only if the generic plan doesn’t look worse does it start getting reused from then on. But, there’s also plan_cache_mode where you can either force custom or generic if you want one plan model to fully take over.

If you’ve ever gone 10 rounds with parameter sniffing in SQL server like I have, this should kind of sound like a familiar problem solved in a very different way. Instead of just hanging onto the first plan somebody happened to generate and hoping it works for everyone else, Postgres gets a few executions under its belt before deciding what it wants to do.

Good Things Don’t Last Forever

Here’s the tradeoff though. Prepared statements only last for the current session. When the session ends, it’s gone. You have to recreate it before you can use it again. And a single prepared statement can’t be shared across multiple simultaneous clients like how the plan cache works in SQL Server. Each client has to prepare its own.

Nothing Is Foolproof

Even within a single session, there is still potential for Postgres tossing out a prepared plan mid flight. If a DDL change happens to a dependent object, it’ll trigger a full re-analysis and re-plan. Same goes if statistics got updated. So, I learned that in Postgres, “cached” doesn’t mean “cached forever”. It basically means that Postgres will use the prepared plan for now till something makes it think that it won’t be valid anymore. Kind of like SQL Server! In my next post, I’ll break down a lab that I’ve built testing this out myself. I run into a couple nuances which shocked me so I’m excited to share that with you next time.