Reading the stats

The symptom

The Index page says invoices_customer_id_idx was never used in 55 days. The column inspector says customer_id has about 398,000 distinct values. The Plan tab says orders was analyzed 41 days ago. Before you act on any of them you need to know which kind of number each one is, because each kind lies in its own way.

Two kinds of numbers

PostgreSQL keeps two kinds of statistics about a table.

Estimates come from ANALYZE. It reads a sample of the table, 30,000 rows by default, and writes what it found to pg_stats: distinct values, the share of nulls, the most common values, the physical order of a column. This is the planner's picture of the table, as fresh as the last ANALYZE and sampled, so 398,000 means roughly that.

Counters come from the cumulative statistics system. Every insert, update and index scan adds one to a counter in pg_stat_all_tables or pg_stat_all_indexes. They are exact and count from the last reset. The sample's counters were reset on 2026-08-23, so every "never" below means "not since then".

Sizes are a third kind, measured from the files, and they never reset.

Seqora prints estimates with ≈. A number without one is a counter or a measurement.

correlation

correlation is how closely the physical order of rows in the table matches the order of a column's values. 1 means the table is laid out in that column's order, 0 means no relationship, negative means reversed.

orders.placed_at has a correlation of 0.99: orders are appended as they are placed, so the last 30 days sit in the last few thousand of the table's 183,000 pages. orders.customer_id has 0.01: one customer's orders are scattered across all of them. An index range scan on placed_at reads a run of neighbouring pages. The same scan on customer_id for a customer with 30 orders reads 30 pages from 30 places. The planner prices the second high, and may pick a Seq Scan over the index you expected.

Where Seqora shows it: the Plan tab inspector draws a heap order sketch under Indexes · <table> for the column the scan filters on, with the correlation beside it. The column inspector (Space on a cell in a grid) prints Correlation.

The inspector for Seq Scan on orders. Each section is one of the two kinds of number.

n_distinct

n_distinct is how many different values a column has. Positive is a count. Negative is a fraction of the row count, used when distinct values grow with the table: -1 means every row is different, and -0.92 on orders.placed_at means 92% of rows have a value nobody else has.

orders.status has 5. For WHERE status = 'shipped' the planner expects millions of rows, so the query gets a Seq Scan however good the index on status is. orders.customer_id has 398,000, so WHERE customer_id = $1 expects about 30 rows and the index wins.

Where Seqora shows it: the column inspector prints Distinct as a count, a share of rows, or both, and lists Most common values.

null_frac

null_frac is the share of rows where the column is null. The planner uses it to shrink the estimate for IS NOT NULL and grow it for IS NULL.

Where Seqora shows it: the column inspector draws a bar: the share that have a value and the share that is NULL.

Inserts, updates and HOT updates

billing.invoices has counted 9,000,000 inserts and 1,000,000 updates since the reset, and 640,000 of the updates were HOT. They are n_tup_ins, n_tup_upd and n_tup_hot_upd in pg_stat_all_tables.

A HOT update writes the new row version on the same page as the old one and leaves every index alone. It is only possible when the update touches no indexed column and the page has room. The other 360,000 updates changed a column some index stores, and wrote a new entry into every index on the table. invoices_customer_created_idx stores status through its INCLUDE, so a status change on an invoice cannot be HOT while that index exists. The index pays on every one of those updates whether or not it is ever read.

Where Seqora shows it: on the Index page, under Where the space goes, hover the table's block for Inserts counted and Updates in place, printed as a share of all updates. That hover is the only place these three counters appear.

last_analyze

last_analyze and last_autoanalyze in pg_stat_all_tables say when the estimates were last refreshed. commerce.orders was analysed 41 days ago, which is why the Plan tab expected 612 rows from the last 30 days and found 206,880. Autovacuum analyses a table once about a tenth of it has changed, and a tenth of 12,000,000 rows is a month of orders. Tables that grow in one direction age worst, because the newest values are the ones the sample has never seen.

Where Seqora shows it: double-click a table wheel in the Plan tab for the TABLE WHEEL guide, where Statistics reads analyzed 41 days ago or never analyzed. The column inspector's footer reads Estimate · pg_stats · analyzed 41 days ago.

idx_scan and last_idx_scan

pg_stat_all_indexes counts idx_scan for every index, and since PostgreSQL 16 records last_idx_scan. invoices_customer_id_idx, on customer_id alone, has 0 scans: not once since 2026-08-23. It is 96 MB, and invoices writes to it on every insert and on every update that is not HOT. Beside it, invoices_customer_created_idx on (customer_id, created_at DESC) is 388 MB and has been scanned 2.8 million times. Every lookup the first can answer, the second answers too, which is why the Index page calls the first covered by this index.

idx_tup_read, which the Index page calls Entries read, is how many index entries scans have walked. idx_tup_fetch, Rows fetched, is how many table rows those scans then went and got. Entries read far above rows fetched means the index is walked and then filtered.

Where Seqora shows it: the Index page header prints the size and the usage beside the name, never used in 55 days for invoices_customer_id_idx. A note under the sections reads Usage counted since stats reset on and the date. Entries read and Rows fetched are under All properties.

Size, space and bloat

pg_relation_size is the index on disk and pg_total_relation_size is the table with everything attached. commerce.orders is 1.5 GB of heap and 2.3 GB in total. commerce.order_items is 3.0 GB of heap and 5.6 GB in total: more index than table.

Sizes cannot say how much of an index is dead: deleted and updated rows leave entries behind until vacuum reclaims them, and the space stays inside the page. The Index page's Where the space goes estimates Pages without entries and Free space inside pages for a B-tree, marked Estimated from table statistics. The schema's Indexes report has Estimate bloat (Pro), which runs the estimate across the schema and marks the worst Bloated. Both are estimates. Seqora does not use pgstattuple, which measures it exactly by reading every page.

Caveats

• Counters reset. pg_stat_reset() and crash recovery set them to zero, and a never used index is never used since then.
• pg_stats is sampled. SELECT count(DISTINCT customer_id) is the truth, and it reads the whole table.
• The counters above come from a 12-million-row copy reset on 2026-08-23. A downloaded copy starts from zero.
• MySQL 8 shows histograms in the column inspector, which are not pg_stats. MariaDB and SQLite show no column statistics. MySQL, MariaDB and SQLite has the table.