The Best (Query) Plans of Mice and Men
A developer attempts to optimize a slow PostgreSQL query by adding a partial index for a rare item type, but finds the index isn't used due to PostgreSQL's prepared statement plan caching. The query uses parameterized inputs, leading the database to generate a generic execution plan based on average-case assumptions, which favors index scans unsuitable for rare values. A raw query with inline literals bypasses this issue by enabling a custom plan that leverages the partial index effectively. This highlights a pitfall where ORM-generated parameterized queries can prevent optimal index usage in skewed data distributions.
- ▪PostgreSQL may use a generic query plan for parameterized queries after several executions, which can lead to suboptimal performance for rare values.
- ▪A partial index designed to speed up queries on a rare item type was not used because the generic plan favored a full index scan.
- ▪The same query with inlined constants (literals) triggered a custom plan that used the partial index and executed significantly faster.
- ▪PostgreSQL decides between generic and custom plans based on estimated costs after the first five executions, influenced by the plan_cache_mode setting.
- ▪ORMs like SQLAlchemy default to parameterized queries, which can inadvertently prevent efficient index usage in cases of highly skewed data.
Opening excerpt (first ~120 words) tap to expand
The Best (Query) Plans of Mice and MenApril 28, 2026databasesperformancepython.post .post-content{margin-top:0}pre code{font-size:.75em}Or rather, of elephants 🐘 and men.At $work, we use PostgreSQL a lot.So much in fact, that we tend to be paranoid frugal when adding even more stuff to it.In this case, we wanted to add a new index to speed up a query (with an eye-watering p99 latency of several seconds). We have a single, big table that is shared between a few different features, but we are only interested in a tiny subset of rows from that table:// Less than 0.1% of rows match this. db.Where("item_type = ?", 4) Being paranoid Having learned from past mistakes, we opted to create a partial index instead of a full index:CREATE INDEX CONCURRENTLY ix_special_item ON ..
…
Excerpt limited to ~120 words for fair-use compliance. The full article is at Github.
Discussion
0 commentsMore from Github
