SQL Academy · Lesson

ANALYZE and pg_statistic

Keep planner statistics current with ANALYZE, inspect pg_statistic, and use extended statistics for correlated columns.

ANALYZE and pg_statistic is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why ANALYZE?

The query planner needs to estimate row counts and selectivities to pick a good plan. Those estimates come from per-column statistics gathered by ANALYZE.

When to Run ANALYZE

Autovacuum runs ANALYZE automatically based on row-change thresholds. After bulk loads or big DELETEs, run it manually so plans don't degrade:

ANALYZE orders;
ANALYZE (VERBOSE) orders;

Sampling

ANALYZE samples a few hundred rows per column. Adjust statistics target if defaults give bad estimates:

ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
-- Up from default 100. ANALYZE will sample more rows.

pg_statistic

The system catalog where stats live (use pg_stats view for readability):

SELECT attname, n_distinct, most_common_vals, most_common_freqs, histogram_bounds
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders';

What the Planner Looks At

n_distinct — how many distinct values
most_common_vals — top values + their frequencies
histogram_bounds — buckets for range queries
correlation — physical vs logical order (affects scan cost)

Extended Statistics

Per-column stats miss correlations between columns. CREATE STATISTICS captures them:

CREATE STATISTICS orders_country_status (dependencies)
  ON country, status FROM orders;
ANALYZE orders;

-- Now the planner knows that  country='US' AND status='paid' is correlated
-- (e.g. most US orders happen to be 'paid').

Multivariate Stats Kinds

dependencies — functional dependencies (one column predicts another)
ndistinct — distinct-value combinations
mcv — most-common combined values (PG 12+)

Bad Estimates → Bad Plans

The most common "why is my query slow" reason is bad row estimates. The planner picks Nested Loop because it expects 1 row; reality is 1,000,000.

EXPLAIN ANALYZE SELECT * FROM ... ;
-- Look at Plan rows vs actual rows. Big gap = run ANALYZE or add extended stats.

Forcing ANALYZE in Migrations

After a big bulk load:

COPY users FROM ... ;
ANALYZE users;
-- Without ANALYZE, the planner has no idea the table just grew.

Stats Don't Auto-Update on Data Skew

If today's data is dramatically different from yesterday's, stats may still be stale until autoanalyze fires. Manual ANALYZE after data shape changes.

pg_class.reltuples

The planner also uses an estimated row count from pg_class. Updated by VACUUM/ANALYZE. Quick to check:

SELECT relname, reltuples FROM pg_class WHERE relname = 'orders';

Recap

ANALYZE feeds the planner.

Run after big data changes
Increase STATISTICS target for skewed columns
CREATE STATISTICS for column correlations
Big estimate-vs-actual gap = first thing to fix

Quick Check

EXPLAIN ANALYZE shows estimated rows=1 but actual rows=500,000 on a single-column WHERE. What's the first fix?

Frequently asked questions

Is the “ANALYZE and pg_statistic” lesson free?

Yes — the full text of “ANALYZE and pg_statistic” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “ANALYZE and pg_statistic”?

Keep planner statistics current with ANALYZE, inspect pg_statistic, and use extended statistics for correlated columns. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “ANALYZE and pg_statistic” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy