Planned lessonMID-013-L04

Statistics and estimates

On This Lesson 4 topics

This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.

In this lesson

explain where estimates come from and how to improve them.

ANALYZE samples each column into pg_stats: null fraction, distinct count, most common values, histogram.
Autovacuum runs analyze automatically, but after bulk loads run it by hand.
Columns that are correlated (city and postcode) are assumed independent; fix with CREATE STATISTICS.
Raise precision for skewed columns with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS n.

Draft outline details

sql

SELECT most_common_vals, most_common_freqs, n_distinct FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

What do you run after loading 10 M rows so the planner has good estimates?

Show answer

ANALYZE on the table.

Apply this lesson with the module's lab, exercises and quiz.

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.