Skip to main content

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:

sql
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​

sql
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 thatCounts 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 scanningseq_scan
Updates or deletes rows it found through an indexidx_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.

sql
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.

sql
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​

sql
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:

sql
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:

text
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_read is NULL. ScramDB keeps no per-table count of rows read yet, and a NULL says so where a 0 would claim none were read.
  • A relation that is no stored table has no counts. An external table, for example, reports NULL for seq_scan and idx_scan.
  • idx_scan is 0 for a table with no index. PostgreSQL reports NULL there.
  • Maintenance scans are not counted. The scan of an ANALYZE, a VACUUM, a CREATE INDEX or 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 a min answered 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 SET on a parameter is not modelled, so only a superuser changes it.

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_scan and idx_scan count scans, seq_tup_read the rows sequential scans read and idx_tup_fetch the rows index probes returned, last_seq_scan and last_idx_scan when the last began;
  • n_tup_ins, n_tup_upd and n_tup_del count the rows each kind of statement wrote (a statement that was rolled back is counted, as PostgreSQL counts it), n_mod_since_analyze the rows changed since the last analysis (the count the automatic analysis decides on) and n_ins_since_vacuum the inserts since the last vacuum. n_tup_hot_upd and n_tup_newpage_upd are 0: every update writes a new row version, so there is no heap-only update to count;
  • vacuum_count, last_vacuum and total_vacuum_time are those of VACUUM, autovacuum_* of the background compaction (a pass that merged the table), analyze_* of ANALYZE and autoanalyze_* of the automatic analysis;
  • n_live_tup and n_dead_tup are 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.