⚙️ Why did Postgres get 100x slower on the 6th run?

Why did Postgres get 100x slower on the 6th run?
This one took me a while to figure out. After a load test on staging (10,000 workspaces), one query stood out: the trending list took 155 ms a page instead of 1.3 ms, and it was using 60% of the database's time.
Here is what was happening:
The driver I use (pgx) prepares every query, and PostgreSQL plans a prepared query with its real values for the first 5 runs.
On the 6th run, it can switch to a "generic" plan, made without the values, if that plan doesn't look more expensive.
My lists have optional filters (board, status, tags...). Without the values, Postgres expected to find a single post, so it went through every post of the workspace and sorted them all.
The fix was one setting: plan_cache_mode = force_custom_plan. Every query is now planned with its real values, and it costs a fraction of a millisecond.
Have you ever been bitten by this one?

p/runevr
p/producthunt
p/google-cloud-platform
p/htmldrag
p/kodlo
p/ai-context-flow
p/dex-by-exmergo
p/jot-2-0
p/beamfy
p/refs
p/reso
p/duckfightclub
p/seshka
p/nudgeforme
p/talanoa-2
p/dino-kids
p/nexupira
p/many-displays
p/murror
p/supabase
Replies
Be the first to reply
Have a question or a thought to share? Add a comment above to start the conversation.