Loading feed…
Planet PostgreSQL0 Hot0 comments20 min read
The Sixth Execution
Prepared statements in PostgreSQL follow a specific rule: for the first five executions, a custom plan is built using the actual parameter values, giving the same plan quality as textual substitution. On the sixth execution, PostgreSQL builds a generic plan (no parameter values) and compares its estimated cost to the average custom plan cost; if generic wins, PostgreSQL switches to it and sticks with it until invalidation. Generic plans use blind selectivity estimates (e.g., non-null fraction divided by n_distinct for equality, one-third of the table for inequality), which can be badly wrong on skewed data, causing queries to mysteriously slow down after the sixth execution. The decision is based purely on estimated cost, never actual runtime, and is asymmetrically sticky. Common culprits include PL/pgSQL functions, and drivers like psycopg 3 and JDBC that auto-promote to named statements after a threshold (default 5), causing the switch around the tenth or eleventh execution instead. Fixes include plan_cache_mode=force_custom_plan for skewed queries, driver-level prepare=False, query restructuring, or partial indexes on skewed columns.
Source: https://postgr.es/p/9t3. 8 Sync News only summarizes and links out; content copyright belongs to the authors and original sources.
Recommended for you
Bình luận
Đang tải bình luận…
