Table of Contents

1. What is ANALYZE?

ANALYZE is a PostgreSQL command that collects statistics about the contents of tables and stores them in the pg_statistic system catalog. The query planner uses these statistics to choose the most efficient execution plan for your queries.

• Row count estimates: How many rows are in the table.
• Column distribution: Most common values, their frequencies, and histograms.
• NULL fraction: Percentage of NULL values per column.
• Distinct values: Estimated cardinality of each column.

2. Why Statistics Matter to the Planner

Think of the PostgreSQL query planner as a GPS. It needs up-to-date traffic data to find the fastest route. Without accurate statistics, the planner may:

• Choose a Sequential Scan when an Index Scan would be faster.
• Pick the wrong join order or join type (nested loop vs hash vs merge).
• Underestimate or overestimate rows, causing poor memory allocation.

Stale statistics are one of the most common causes of unexpected query slowdowns in PostgreSQL.

3. Create Demo Table & Insert Data

-- Create a demo orders table
postgres=# DROP TABLE IF EXISTS orders;
DROP TABLE
postgres=# CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT,
    amount NUMERIC(10,2),
    status TEXT,
    created_at TIMESTAMP DEFAULT now()
);
CREATE TABLE
postgres=#

-- Insert 100,000 rows with random data
postgres=# INSERT INTO orders (customer_id, amount, status)
SELECT
    (random() * 1000)::int,
    (random() * 500)::numeric(10,2),
    (ARRAY['pending','shipped','delivered','cancelled'])[floor(random()*4+1)]
FROM generate_series(1, 100000);
INSERT 0 100000
postgres=#

4. Check Statistics Before ANALYZE

Right after inserting data, PostgreSQL has not yet collected fresh statistics (unless autovacuum kicked in). Let’s confirm.

-- Check when ANALYZE last ran
postgres=# SELECT
    relname,
    n_live_tup,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';
 relname | n_live_tup | last_analyze |       last_autoanalyze
---------+------------+--------------+-------------------------------
 orders  |     0      |              | 
(1 row)

postgres=#


-- Check pg_class row estimate
postgres=# SELECT relname, reltuples, relpages FROM pg_class WHERE relname = 'orders';
 relname | reltuples | relpages
---------+-----------+----------
 orders  |        -1 |        0
(1 row)

postgres=#

The planner is blind. reltuples = -1 means “unknown”, and last_analyze is NULL. Any query plan generated now will be based on guesses.

5. Run ANALYZE & Verify Stats Collected

-- Run ANALYZE on the table
postgres=# ANALYZE orders;
ANALYZE
postgres=#

-- Verify statistics are now recorded
postgres=# SELECT
    relname,
    n_live_tup,
    last_analyze
FROM pg_stat_user_tables
WHERE relname = 'orders';
 relname | n_live_tup |         last_analyze
---------+------------+-------------------------------
 orders  |     100000 | 2026-09-24 14:58:42.724088+08
(1 row)

postgres=#

-- pg_class now has real numbers
postgres=# SELECT relname, reltuples, relpages FROM pg_class WHERE relname = 'orders';
 relname | reltuples | relpages
---------+-----------+----------
 orders  |    100000 |      786
(1 row)

postgres=#

Statistics collected! The planner now knows the table has 100,000 rows across 786 pages and can make informed decisions.

6. See ANALYZE Impact on Query Plans

The best way to see ANALYZE in action is to compare estimated rows vs actual rows in EXPLAIN ANALYZE.

-- Run a query and inspect the plan
postgres=# EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'cancelled';
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..2036.00 rows=24850 width=30) (actual time=0.017..25.889 rows=24944 loops=1)
   Filter: (status = 'cancelled'::text)
   Rows Removed by Filter: 75056
 Planning Time: 0.242 ms
 Execution Time: 28.015 ms
(5 rows)

postgres=#

Look for this key comparison:

 (cost=0.00..2036.00 rows=24850 width=30) (actual time=0.017..25.889 rows=24944 loops=1)
                        ^^^^^^^^^^                                           ^^^^^^^^^^
                         Estimated                                             Actual

What good statistics look like:

• Estimated rows: 24,850
• Actual rows: 24,944
• Difference: less than 1% — the planner is highly accurate.

When estimates and actuals diverge sharply (10x or more), the planner is likely making poor choices. That’s when you need to re-run ANALYZE or tune statistics.

7. EXPLAIN Plan Before vs After ANALYZE (Side-by-Side)

Let’s build a clean scenario to directly compare the same query’s plan before and after running ANALYZE. We’ll create an index and disable autovacuum on the demo table so we can control exactly when statistics are collected.

-- Fresh table with autovacuum disabled so we control the timing
postgres=# CREATE TABLE orders_demo (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    amount NUMERIC(10,2) NOT NULL,
    status TEXT NOT NULL,
    region TEXT
) WITH (autovacuum_enabled = false);
CREATE TABLE
postgres=#


-- Create an index BEFORE loading data
postgres=# CREATE INDEX idx_customer ON orders_demo(customer_id);
CREATE INDEX
postgres=# CREATE INDEX idx_status ON orders_demo(status);
CREATE INDEX
postgres=# CREATE INDEX idx_date ON orders_demo(order_date);
CREATE INDEX
postgres=#

-- Load 50,00,000 rows
postgres=# INSERT INTO orders_demo (customer_id, order_date, amount, status, region)
SELECT
    (random() * 10000)::int,
    CURRENT_TIMESTAMP - (random() * INTERVAL '1 year'),
    (100 + random() * 400)::numeric(10,2),
    (ARRAY['pending','shipped','delivered','cancelled','returned'])[floor(random()*5+1)],
    (ARRAY['North','South','East','West','Central'])[floor(random()*5+1)]
FROM generate_series(1, 5000000);
INSERT 0 5000000
postgres=#


-- Confirm stats are empty
postgres=# SELECT relname, reltuples, relpages FROM pg_class WHERE relname = 'orders_demo';
   relname   | reltuples | relpages
-------------+-----------+----------
 orders_demo |        -1 |        0
(1 row)

postgres=#
postgres=# SELECT COUNT(*) as stats_count FROM pg_stats WHERE tablename = 'orders_demo';
 stats_count
-------------
           0   <---------
(1 row)

postgres=#

BEFORE ANALYZE

postgres=# EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
    customer_id,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders_demo
WHERE status = 'delivered'
GROUP BY customer_id
HAVING COUNT(*) > 10
LIMIT 20;
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=28926.06..28926.96 rows=20 width=44) (actual time=4407.894..4407.915 rows=20 loops=1)
   Buffers: shared hit=19 read=42953 dirtied=42172 written=35291
   ->  HashAggregate  (cost=28926.06..28929.06 rows=67 width=44) (actual time=4407.891..4407.908 rows=20 loops=1)
         Group Key: customer_id
         Filter: (count(*) > 10)
         Batches: 1  Memory Usage: 4257kB
         Buffers: shared hit=19 read=42953 dirtied=42172 written=35291
         ->  Bitmap Heap Scan on orders_demo  (cost=186.67..28823.25 rows=13709 width=20) (actual time=91.441..3289.209 rows=1002131 loops=1)
               Recheck Cond: (status = 'delivered'::text)
               Heap Blocks: exact=42181
               Buffers: shared hit=19 read=42953 dirtied=42172 written=35291
               ->  Bitmap Index Scan on idx_status  (cost=0.00..183.25 rows=13709 width=0) (actual time=74.658..74.659 rows=1002131 loops=1)
                     Index Cond: (status = 'delivered'::text)
                     Buffers: shared hit=10 read=781
 Planning:
   Buffers: shared hit=1 read=6
 Planning Time: 0.449 ms
 Execution Time: 4408.625 ms
(18 rows)

postgres=#

What went wrong:

• Planner had missing or severely stale statistics — it estimated rows=13709 for status=’delivered’, but actual rows were 1,002,131 (73x estimation error).
• Row estimation was catastrophically inaccurate because ANALYZE had not been run recently on this table, or statistics on the status column were outdated.
• Planner correctly chose to use the index (Bitmap Index Scan on idx_status), but severely underestimated the result set size.
• HashAggregate had to process 1 million rows instead of 13k, causing massive memory usage and disk I/O.
• Execution time: 4,408.625 ms (4.4+ seconds) — very slow query.
• Heavy buffer activity: read=42953 dirtied=42172 written=35291 — excessive disk reads/writes due to underestimated result set.
• Cost estimate was drastically wrong (cost=28926.06) because of poor row statistics on the filtered column.

AFTER ANALYZE

postgres=# ANALYZE orders_demo;
ANALYZE
postgres=#
postgres=# EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
    customer_id,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders_demo
WHERE status = 'delivered'
GROUP BY customer_id
HAVING COUNT(*) > 10
LIMIT 20;
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..1727.02 rows=20 width=44) (actual time=1.145..17.358 rows=20 loops=1)
   Buffers: shared hit=9553
   ->  GroupAggregate  (cost=0.43..286526.80 rows=3319 width=44) (actual time=1.143..17.344 rows=20 loops=1)
         Group Key: customer_id
         Filter: (count(*) > 10)
         Buffers: shared hit=9553
         ->  Index Scan using idx_customer on orders_demo  (cost=0.43..278685.09 rows=1025646 width=10) (actual time=0.044..16.175 rows=1852 loops=1)
               Filter: (status = 'delivered'::text)
               Rows Removed by Filter: 7741
               Buffers: shared hit=9553
 Planning Time: 0.255 ms
 Execution Time: 17.420 ms
(12 rows)

postgres=#

What changed:

• Planner now has accurate statistics on the status column — it can now estimate the filtered result set much better.
• Query plan COMPLETELY CHANGED to a much better strategy:
  • BEFORE: Bitmap Heap Scan on idx_status → HashAggregate (process 1,002,131 rows)
  • AFTER: Index Scan on idx_customer → GroupAggregate (process only 1,852 rows)
• Planning time decreased: 0.449 ms → 0.255 ms (faster planning with better statistics).
• Execution time improved dramatically: 4,408.625 ms → 17.420 ms (253x faster).
• Buffer activity improved dramatically: 42,953 disk reads → 9,553 cache hits only (all data now cached, zero disk I/O).
• Memory usage dropped significantly: no more massive HashAggregate memory allocation, using efficient GroupAggregate instead.

Side-by-Side Comparison

✅ Lesson: A single ANALYZE command changed a 4,408.625 ms query into a 17.420 ms query (253x faster) — no code changes, no new indexes created, no configuration tweaks. Just fresh statistics enabling the planner to choose a completely different and superior query plan.

8. Inspect Detailed Column Statistics (pg_stats)

The pg_stats view exposes the detailed statistics ANALYZE collected. This is gold for troubleshooting.

-- Look at stats for the status column
postgres=# SELECT
    attname,
    null_frac,
    n_distinct,
    most_common_vals,
    most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
 attname | null_frac | n_distinct |           most_common_vals            |           most_common_freqs
---------+-----------+------------+---------------------------------------+---------------------------------------
 status  |         0 |          4 | {shipped,delivered,pending,cancelled} | {0.25183332,0.25036666,0.2493,0.2485}
(1 row)

postgres=#

What this tells us:

• null_frac = 0: No NULLs in this column.
• n_distinct = 4: Only 4 unique values exist.
• most_common_vals: The values PostgreSQL knows about.
• most_common_freqs: Each value appears ~25% of the time.

-- Look at stats for a numeric column (has histogram)
postgres=# SELECT
    attname,
    n_distinct,
    histogram_bounds
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'amount';
 attname | n_distinct |                          histogram_bounds
---------+------------+---------------------------------------------------------------------
 amount  |   -0.36494 | {0.00,5.14,10.51,15.43,20.35,25.04,.................,494.94,499.99}
(1 row)

postgres=#

For columns with many distinct values, PostgreSQL builds a histogram instead of storing every value. This lets the planner estimate ranges (e.g., WHERE amount BETWEEN 100 AND 200).

9. ANALYZE on Specific Columns

On huge tables, analyzing all columns can be expensive. You can target specific columns to save time.

-- Analyze only the status column
postgres=# ANALYZE orders(status);
ANALYZE
postgres=#

-- Analyze multiple columns
postgres=# ANALYZE orders(status, customer_id);
ANALYZE
postgres=#

-- Analyze the entire database (careful on large systems)
postgres=# ANALYZE;
ANALYZE
postgres=#

-- Verbose mode to see what's happening
postgres=# ANALYZE VERBOSE orders;
INFO:  analyzing "public.orders"
INFO:  "orders": scanned 786 of 786 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows
ANALYZE
postgres=#

💡 Tip: ANALYZE samples the table (default 30,000 rows) rather than scanning everything. That’s why it’s fast even on large tables.

10. Tune Statistics Target for Better Accuracy

The default_statistics_target (default: 100) controls the sample size and number of buckets in histograms. Higher = more accurate but slower.

-- Check current default
postgres=# SHOW default_statistics_target;
 default_statistics_target
---------------------------
 100
(1 row)

postgres=#

-- Increase target for a specific column with skewed data
postgres=# ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ALTER TABLE
postgres=#

-- Re-analyze to apply new target
postgres=# ANALYZE orders;
ANALYZE
postgres=#

-- Verify the new target
postgres=# SELECT attname, attstattarget
FROM pg_attribute
WHERE attrelid = 'orders'::regclass AND attname = 'status';
 attname | attstattarget
---------+---------------
 status  |           500
(1 row)

postgres=#

When to increase the target:

• Columns with skewed distributions (some values much more common than others).
• Columns used in join conditions where bad estimates cause bad join plans.
• Columns in WHERE clauses where you see estimate/actual row mismatches.

11. VACUUM ANALYZE & Autovacuum Behavior

You can combine cleanup and statistics collection in one command. Autovacuum also runs ANALYZE automatically for you.

-- Run VACUUM and ANALYZE together
postgres=# VACUUM ANALYZE orders;
VACUUM
postgres=#

-- Check autovacuum settings
postgres=# SHOW autovacuum_analyze_threshold;
 autovacuum_analyze_threshold
------------------------------
 50
(1 row)

postgres=# SHOW autovacuum_analyze_scale_factor;
 autovacuum_analyze_scale_factor
---------------------------------
 0.1
(1 row)

postgres=#

How autoanalyze decides to run:

trigger = analyze_threshold + (analyze_scale_factor * table_row_count)
        = 50 + (0.1 * 100000)
        = 10,050 changes

So on our 100K-row table, autovacuum will automatically run ANALYZE once about 10,050 rows have been inserted, updated, or deleted.

⚠️ When to run ANALYZE manually:

• After a large bulk INSERT, COPY, or restore.
• After a big UPDATE or DELETE that changes data distribution.
• After creating a new index (autovacuum will not trigger from this).
• When queries suddenly get slow and estimates look off in EXPLAIN.

12. Quick Cheatsheet