PostgreSQL Constraint Exclusion: Query Planning

Tip: CHECK Constraints for Partition Pruning

CREATE TABLE logs_2024 CHECK (created_at >= '2024-01-01' AND created_at < '2025-01-01')
    INHERITS (logs);

PostgreSQL skips partitions that can't match the WHERE clause.

Gotcha: constraint_exclusion Setting

constraint_exclusion = partition

Default is partition (only for inheritance partitioning). on checks all constraints but is slower.

Tip: EXPLAIN Shows Partition Pruning

EXPLAIN SELECT * FROM logs WHERE created_at >= '2024-06-01';

Shows which partitions are scanned.

Gotcha: Constraints Must Be Immutable

CHECK constraints with functions like now() don't help with constraint exclusion.

Tip: Partial Indexes as Constraint Hints

CREATE INDEX idx_active_users ON users (id) WHERE active = true;

PostgreSQL uses partial indexes to skip inactive rows.

Gotcha: Statistics Matter

Run ANALYZE after bulk inserts to update planner statistics. Stale stats lead to bad plans.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

Constraint exclusion is PostgreSQL's ability to skip partitions or inherited tables when the WHERE clause makes them irrelevant. This only works when the constraint is simple and the query uses immutable operators. I've debugged situations where constraint exclusion silently failed because the WHERE clause used now() (which is STABLE, not IMMUTABLE) instead of a constant date. Use explicit date literals in queries that need partition pruning.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)