Scenarios

Run EXPLAIN ANALYZE and find the lowest node where estimated and actual rows differ by more than 10×. Suppose it is the scan of the newest partition: estimated 1 row, actually 9 million.

The new partition has no statistics, so the planner used its fixed placeholder constants and chose a nested loop with index lookups: right for 1 row, fatal for 9 million.

Run ANALYZE on the partition, and make it the last step of the load job, paying the fixed cost at the cheapest moment. Longer term, write statistics at load time, or use a format that stores per-file statistics.

Cost settings such as random_page_cost: the mistake was about row counts, not cost ratios.

Automatic analysis waits for 0.1 × n + 50 changed rows, about 10 days on a billion-row table growing 1% a day.

Check whether the planner can drop the unused joins: the foreign keys must be enforced and the referenced columns unique. Validate the constraints on the data, then enforce them, or mark them as trusted (RELY) only if the load pipeline guarantees them. Removing four joins beats any join-order tuning.

Check that left joins followed by a filter that rejects NULLs become inner joins, so the remaining join order can be searched freely.

Long OR chains may defeat pruning because normalization to conjunctive form is capped; IN lists work.

Put a thin view in front of it that exposes only the needed joins.

Enforced constraints cost load time; trusting unenforced ones trades correctness for speed.

The two are not equivalent when the subquery's column can be NULL: NOT IN with any NULL in the set returns no rows. So the planner may not turn NOT IN into an anti-join unless it can prove there are no NULLs, and it runs the subquery as a nested loop, costing about outer rows × subquery cost. NOT EXISTS has no NULL trap and becomes an anti-join.

If the column should never be NULL, add a NOT NULL constraint, which makes the rewrite legal; or write NOT EXISTS. If NULLs are valid and the intent is "not in the set, ignoring NULLs", NOT EXISTS is also the right meaning.

Check with EXPLAIN that the plan shows an anti-join, and compare the row counts of both forms before switching.

Three views over five tables each is 15 tables. Exhaustive join-order search is practical up to about 12 and costs O(3ⁿ), so beyond that the planner switches to greedy or genetic search. PostgreSQL's genetic optimizer is not deterministic: it can return different plans for the same query, which explains run times of 20 seconds one day and 3 minutes the next.

1. Flatten the view stack to the tables the dashboard actually needs.
2. Eliminate joins using enforced foreign keys.
3. For the few critical queries, constrain join order (for example join_collapse_limit) so the search is small and stable.
4. As a last resort, pin the plan with a baseline and review it on a schedule.

Pinning buys stability at the cost of adapting to data. In production, variance matters more than the mean.

The crossover is about (1 / rows per page) × (sequential cost / random cost). With 100 rows per page and the default random-to-sequential ratio of 4, it is about 0.25%, so at 2% a scan wins. With NVMe and a ratio near 1.1, it is about 0.9%: still below 2%.

The table is clustered by customer_id (correlation near 1), in which case the index is cheap. Wide rows (fewer per page) raise the crossover; columnar storage lowers it.

Do not rely on the 5% rule. Measure both plans with EXPLAIN ANALYZE, check the correlation statistic, and consider a bitmap scan as the middle path.

All of this assumes a good selectivity estimate; if that is off by 10×, the decision is wrong whatever the formula says.

The default threshold waits for 0.1 × n + 50 changes: at a billion rows, 100 million changes, about 10 days. New partitions have no statistics at all.

1. Make ANALYZE of the newly loaded partition the last step of the load.
2. Store statistics at write time if the format supports it (per-file min/max and counts at commit).
3. Define extended statistics for correlated columns that matter, such as city and postcode, or product and promotion.
4. Count distinct values for key columns with a full-pass sketch, not from a sample.
5. Alert when estimated and actual rows differ by more than 10× on the top queries, using captured EXPLAIN ANALYZE output.

Each analysis is a scan with a fixed cost, so choose intervals from cost against benefit; do not analyze everything daily.

The usual argument, scan efficiency, is weak: columnar storage already reads only the columns needed. The real benefit is less risk: no join order to get wrong, no correlation across joins to mis-estimate, no containment assumption to violate, so the tail of bad plans disappears.

Storage multiplied, harder updates, redundancy, and the pre-joined table must be refreshed.

Denormalize only for a few hot queries whose stability matters, as pre-joined marts refreshed after each load; keep the star schema elsewhere; diagnose the rest properly; and pin the plans that must not change.

This reduces risk by removing decisions; it does not make the optimizer any better.

A better mean bought with occasional 100× regressions is usually a bad deal: variance, not the mean, causes incidents.

Replay your own top queries and compare the distribution of per-query ratios, not just the average. Check plan stability across runs and after statistics refreshes.

How it handles missing statistics, correlated columns, and views that expand beyond 12 tables; and whether hints or plan baselines exist for queries that must not change.

A staged rollout with plan capture, and automatic fallback to the old plan on regression.

Check which filters reached the scan, and how many files were read. Typical causes: the store filter sits on a dimension table, so only a runtime filter can use it; and files are in arrival order, so every file's store_id range covers everything.

1. Make the date filter sargable, and declare the partition as a transform of the timestamp.
2. Cluster each day's files by (store_id, order_id), so row-group statistics narrow.
3. Make sure the engine builds a runtime filter from the small stores side: a 400-row build costs almost nothing.
4. Project only the needed columns, nested ones included.
5. Use Top-N for the final LIMIT.

From 4.2 TB to about 11 GB, about 380× less.

Clustering costs write time and gives only one ordering, so a dashboard filtered by product gains nothing; a runtime filter makes the build finish first. If the same page recurs, a result cache keyed by snapshot gives a further large gain.

Parameter sniffing: the cached plan was built for a small tenant (an index lookup) and reused for the large one. Confirm by comparing EXPLAIN of the prepared statement with the literal version, and check whether a statistics refresh recently invalidated plans.

Force custom plans for the known large tenants; use one generic plan for all (robust but mediocre); keep several plans by class of value; or plan for the worst case.

Robust generic plans for the many, custom plans for the few largest, and an alert when a plan's hash changes after analyze.

Re-planning on every call costs planning time each time: fine for a 9-minute query, not for a 1 ms one.

The error is probably before the first stage boundary: a wrong first access path, a filter that cannot be pushed down, a missing partition filter, or a subquery that was not decorrelated. Adaptive execution helps with sizing, join substitution and skew after a shuffle.

Where the first exchange is, and where the estimate first goes wrong (estimated against actual rows per node).

Correct the early part by hand (statistics, a rewrite, a hint), then let adaptive execution handle the rest.

Less determinism, and harder analysis of plan regressions.

Bloom filters per row group.

Min/max statistics are useless on unsorted data: with a million rows per group, each group's range covers (10⁶ − 1) / (10⁶ + 1) ≈ 99.9998% of the domain. No metadata fixes that. Either cluster by customer_id (a write cost, and the table gets only one ordering for other access patterns) or build a real index or a second copy sorted by customer (space and maintenance).

By how common each kind of query is.

A count can be answered from metadata only if the filter is fully resolved by pruning and the stored counts are accurate.

The slow twin uses merge-on-read with deletion vectors, so file counts are not live counts; or a filter remains that pruning cannot fully resolve, for example a partition based on ts while day is a derived column.

Does the scan node read any data files at all?

Compact to apply deletes, maintain a live count, or declare the partition as a transform of the timestamp.

Break-even is n* = C / Δc: with a 50 ms compile and 20 ns saved per row, 2.5 million rows.

If most interactive queries touch fewer rows, compilation only adds latency; for the few heavy ones it pays. Prefer an engine that compiles adaptively, interpreting first and switching once compiled code is ready, or set a cost threshold.

Vectorization and compilation are substitutes for each other; measure on your own workload.

Hit rate × query cost must exceed storage plus invalidation cost.

With versioned, immutable tables, the validity check is one snapshot comparison, so yes for dashboards that repeat. For tables updated in place, tracking changes can cost more than the query.

Exact-text matching has a poor hit rate (whitespace, parameters, session settings); semantic matching is incomplete and fails silently.

Measure the repeat rate from the query log: recurrence is a property of people, not of data.