Track Counts
By the end of this page you will know which scans track_counts counts, how to read them back per table, who may turn the counting off, and where ScramDB's counts differ from PostgreSQL's.
track_counts is PostgreSQL's setting: on by default, and a superuser setting, so only a superuser changes it. While it is on, every scan a statement starts on a stored table is counted against that table, and the counts are what pg_stat_user_tables reports as seq_scan and idx_scan. While it is off, the session counts nothing, and what other sessions counted stays.
Examples on this page use this table:
CREATE TABLE accounts (id BIGINT PRIMARY KEY, balance BIGINT);
INSERT INTO accounts SELECT i, i * 10 FROM generate_series(1, 100) AS g(i);
What is counted
SELECT count(*) FROM accounts WHERE balance > 500;
SELECT balance FROM accounts WHERE id = 7;
SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE relname = 'accounts';
| A statement that | Counts one |
|---|---|
| Reads the table without an index (a filter on a column with no index, an aggregate over the whole table) | seq_scan |
| Finds its rows through an index (a primary key lookup, an indexed range, a vector index probe) | idx_scan |
| Updates or deletes rows it found by scanning | seq_scan |
| Updates or deletes rows it found through an index | idx_scan |
A statement counts once per scan it starts, however many rows it reads, and a statement a session runs repeatedly counts once per run. A join counts one scan on each table it reads.
UPDATE accounts SET balance = balance + 1 WHERE id = 7;
DELETE FROM accounts WHERE balance > 900;
SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE relname = 'accounts';
The counts belong to the table, not its name: a rename or a rewrite (ALTER TABLE ... ALTER COLUMN ... TYPE) keeps them and they go on counting.
ALTER TABLE accounts RENAME TO ledger;
SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE relname = 'ledger';
ALTER TABLE ledger RENAME TO accounts;
Switching it off
SET track_counts = off;
SELECT count(*) FROM accounts;
SHOW track_counts;
RESET track_counts;
The setting is the session's own: one session's off never stops another session's scans from counting. It lasts for the session, or for the transaction with SET LOCAL. RESET returns to on, and RESET ALL and DISCARD ALL reset it for every role. It is a boolean with PostgreSQL's spellings (on, off, true, false, yes, no, 1, 0); anything else is refused with 22023.
Its context is superuser in pg_settings:
SELECT setting, context, vartype FROM pg_settings WHERE name = 'track_counts';
Any other role is refused on every route (SET, SET LOCAL, set_config, a connection's startup options) with 42501 before the value is read, judged against the role in force, so a superuser who has done SET ROLE to an ordinary role is refused too, and keeps the value already set:
ERROR: permission denied to set parameter "track_counts"
Where the counts differ from PostgreSQL's
- They are this node's, since the server started. A restart begins again at zero, and on a cluster each node counts its own scans.
seq_tup_readis NULL. ScramDB keeps no per-table count of rows read yet, and a NULL says so where a0would claim none were read.- A relation that is no stored table has no counts. An external table, for example, reports NULL for
seq_scanandidx_scan. idx_scanis0for a table with no index. PostgreSQL reports NULL there.- Maintenance scans are not counted. The scan of an
ANALYZE, aVACUUM, aCREATE INDEXor a table rewrite is not a statement's scan; PostgreSQL counts an index build and a rewrite as sequential scans. - An answer read from statistics is not a scan. A
count(*)or aminanswered from the exact per-column figures a table keeps never reads the table, so it counts nothing. - A join in rounds counts a scan per round for the table it reads again each round.
- A grant of
SETon a parameter is not modelled, so only a superuser changes it.
Related
- Query Performance for reading a plan and checking an index helps.
- Observability for the metrics an operator reads.
The statistics views
pg_stat_all_tables, pg_stat_user_tables and pg_stat_sys_tables have PostgreSQL 18's 30 columns in its order, and pg_stat_xact_all_tables, pg_stat_xact_user_tables and pg_stat_xact_sys_tables its 12, so a query that names any of them answers. Every counter is a number, 0 until the thing it counts happens, as in PostgreSQL; only the last_* times are NULL until their event, and the total_*_time columns are milliseconds.
They count on this node since the server started, while a session's track_counts is on:
seq_scanandidx_scancount scans,seq_tup_readthe rows sequential scans read andidx_tup_fetchthe rows index probes returned,last_seq_scanandlast_idx_scanwhen the last began;n_tup_ins,n_tup_updandn_tup_delcount the rows each kind of statement wrote (a statement that was rolled back is counted, as PostgreSQL counts it),n_mod_since_analyzethe rows changed since the last analysis (the count the automatic analysis decides on) andn_ins_since_vacuumthe inserts since the last vacuum.n_tup_hot_updandn_tup_newpage_updare 0: every update writes a new row version, so there is no heap-only update to count;vacuum_count,last_vacuumandtotal_vacuum_timeare those ofVACUUM,autovacuum_*of the background compaction (a pass that merged the table),analyze_*ofANALYZEandautoanalyze_*of the automatic analysis;n_live_tupandn_dead_tupare the table's rows and its deleted rows not yet reclaimed.
The pg_stat_xact_* views are the open transaction block's own counts of the same scans and rows, and 0 outside a block. The sys views list PostgreSQL 18's 64 pg_catalog tables and the four information_schema.sql_* tables under their fixed oids (there are no pg_toast tables to list); a read of a catalog is one scan of it, n_live_tup is the rows it answers now, and the tuple counts are 0 because DDL writes no catalog tuple here.