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

by•


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:

  1. The driver I use (pgx) prepares every query, and PostgreSQL plans a prepared query with its real values for the first 5 runs.

  2. On the 6th run, it can switch to a "generic" plan, made without the values, if that plan doesn't look more expensive.

  3. 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?

10 views

Add a comment

Replies

Be the first to reply

Have a question or a thought to share? Add a comment above to start the conversation.