0Pricing
Advanced PostgreSQL: Indexing, Partitioning, Replication · Lesson

Tuning Index Cost with ANALYZE and Statistics

Learn how the PostgreSQL planner relies on table statistics to choose indexes, and how to keep those statistics fresh so query plans stay fast.

Tuning Index Cost with ANALYZE and Statistics is a free Advanced PostgreSQL: Indexing, Partitioning, Replication lesson on CoddyKit — lesson 4 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 Advanced PostgreSQL: Indexing, Partitioning, Replication learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Planner Needs Data About Data

PostgreSQL's planner picks between index scans and sequential scans using statistics about your tables: row counts, value distributions, and more.

Stale statistics lead to bad plans.

What ANALYZE Does

The ANALYZE command samples a table and updates statistics stored in the system catalogs, so the planner estimates row counts accurately.

ANALYZE orders;

Autovacuum and Autoanalyze

The autovacuum daemon also runs ANALYZE automatically when enough rows change. But heavy bulk loads may need a manual ANALYZE right away.

Reading Estimates vs Actuals

Use EXPLAIN ANALYZE to compare the planner's estimated rows with actual rows. A big gap signals stale or insufficient statistics.

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'paid';

Statistics Target

The default_statistics_target controls sample size. Raise it on columns with skewed data for sharper histograms.

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;

Why Bad Estimates Hurt

If the planner underestimates matching rows it may pick an index scan that becomes slow; overestimate and it may skip a perfectly good index for a seq scan.

Inspecting pg_stats

The pg_stats view exposes the collected statistics like most common values and null fraction per column.

SELECT attname, n_distinct, null_frac FROM pg_stats WHERE tablename = 'orders';

Extended Statistics

For correlated columns, create extended statistics so the planner understands dependencies between them.

CREATE STATISTICS orders_corr (dependencies) ON city, zip FROM orders;

Cost Parameters

Settings like random_page_cost tell the planner how expensive random I/O is. On SSDs, lowering it makes index scans more attractive.

SET random_page_cost = 1.1;

When the Index Is Ignored

If an index exists but is unused, suspect:

  • Stale statistics
  • Low selectivity (most rows match)
  • A type mismatch preventing index use

A Tuning Workflow

  1. Run EXPLAIN ANALYZE
  2. Check estimate vs actual gap
  3. ANALYZE or raise statistics target
  4. Adjust cost settings if needed
  5. Re-measure

Quick Check

Test your statistics tuning knowledge.

Recap

The PostgreSQL planner chooses indexes based on statistics kept fresh by ANALYZE and autovacuum.

Compare estimated versus actual rows with EXPLAIN ANALYZE, raise the statistics target for skewed columns, add extended statistics for correlated ones, and tune cost parameters to guide index choice.

Frequently asked questions

Is the “Tuning Index Cost with ANALYZE and Statistics” lesson free?

Yes — the full text of “Tuning Index Cost with ANALYZE and Statistics” is free to read here on the web, and the Advanced PostgreSQL: Indexing, Partitioning, Replication 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 Advanced PostgreSQL: Indexing, Partitioning, Replication course, upgrade to CoddyKit PRO.

What will I learn in “Tuning Index Cost with ANALYZE and Statistics”?

Learn how the PostgreSQL planner relies on table statistics to choose indexes, and how to keep those statistics fresh so query plans stay fast. You practise Advanced PostgreSQL: Indexing, Partitioning, Replication 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 Advanced PostgreSQL: Indexing, Partitioning, Replication?

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

How long does the “Tuning Index Cost with ANALYZE and Statistics” 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 Advanced PostgreSQL: Indexing, Partitioning, Replication lesson?

Yes. Every Advanced PostgreSQL: Indexing, Partitioning, Replication 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

  1. Analyzing Query Plans with EXPLAIN
  2. Index Usage Monitoring
  3. Reindexing and Index Maintenance
  4. Tuning Index Cost with ANALYZE and Statistics
← Back to Advanced PostgreSQL: Indexing, Partitioning, Replication