You want to measure a table's access pattern cleanly after a change. How do you reset just that table's statistics without wiping the cluster?
To reset one table, call pg_stat_reset_single_table_counters with the table's OID, for example 'shipment_manifest'::regclass. It clears that table's activity counters and nothing else. PostgreSQL keeps two separate sets of numbers about a table. Activity counters record what has happened to it: scans, inserts, updates, deletes and tuple estimates. Column statistics describe what is in it: distinct counts, common values and histograms. The reset clears the first set. ANALYZE replaces the second. On a busy system there is one catch: counters reach the statistics views with a delay, so a read right after the reset can still show a stray count.
What the interviewer is scoring
Name the per-table reset function. Separate it from pg_stat_reset (whole database) and pg_stat_statements_reset (per query). Say that ANALYZE replaces column statistics; the counter reset does not touch them.
In short: They want to hear that the reset clears activity counters, not the planner's column statistics.
Short answer (90 seconds, spoken)
First person, as you would speak itpg_stat_reset_single_table_counters is the function, and it takes the table's identifier, usually written as a regclass cast of the name.
What it clears is that one table's cumulative activity: sequential scans, index scans, rows inserted, updated and deleted, and the live and dead tuple estimates. Nothing else in the database is touched, which is the point. I want a clean window to measure a change in, not cluster-wide amnesia.
Two things I would distinguish immediately. A plain pg_stat_reset clears counters for the entire current database, which destroys everyone else's baselines at the same time. And pg_stat_statements_reset is a different subsystem altogether, per normalised query rather than per table.
This resets activity counters. It does not touch planner statistics. The distinct estimate, the most-common-values list and the histogram live in pg_statistic and are replaced by ANALYZE. ANALYZE leaves the scan and tuple counters alone; it only updates last_analyze and n_mod_since_analyze. PostgreSQL 18 adds separate functions to clear planner statistics explicitly, but the counter reset is not one of them.
One caution from doing it for real: counters are accumulated per backend and flushed on a delay, so a read taken immediately afterwards can still show a stray increment from just before.
Hold these beats, not the script
- 1Name the per-table function first
- 2Not the database-wide reset, not the per-query subsystem
- 3Activity counters, never planner statistics
- 4Flushes are delayed, so measure a window
Why
The depth a second question reaches, and where the claim stops.
Treat the reset as the start of a measurement window rather than as proof of zero.
The figures in the statistics views are not written synchronously by each query. Backends accumulate them locally and flush them periodically, so there is a short interval in which activity from before the reset arrives after it. If the measurement matters, take the baseline read a few seconds later, or take two reads and use the difference rather than trusting an absolute value. That is also the correct explanation when an index-scan count is already non-zero on the very next read: it is a flush landing late, not a phantom query. On PostgreSQL 15 and later, also take the two reads in separate transactions: with the default stats_fetch_consistency of cache, repeated reads inside one transaction return the same snapshot.
The other practical caution is that the reset is not transactional and cannot be undone. On a shared cluster somebody else may be mid-measurement on that table, so it is worth saying what you are about to clear.
What I would do if I could not reset at all: capture the counters before and after and subtract. On a busy production system that is strictly better, because it destroys no history and it survives somebody else resetting underneath you.
What to run · 3 captured on PostgreSQL
The table's activity counters before the reset
SELECT seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='shipment_manifest' seq_scan | idx_scan | n_live_tup | n_dead_tup
----------+----------+------------+------------
10 | 1 | 6000032 | 0
(1 row)Copied verbatim from a controlled lab run.
What it shows: The cumulative history we want to clear so a change can be measured against a clean window.
Clearing counters for this one table only
SELECT pg_stat_reset_single_table_counters('shipment_manifest'::regclass) pg_stat_reset_single_table_counters
-------------------------------------
(1 row)Copied verbatim from a controlled lab run.
What it shows: Targeted at one relation. No other table's counters, and nothing cluster-wide, is affected.
Immediately after: scans and tuple estimates cleared, one counter already back at 1
SELECT seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='shipment_manifest' seq_scan | idx_scan | n_live_tup | n_dead_tup
----------+----------+------------+------------
0 | 1 | 0 | 0
(1 row)Copied verbatim from a controlled lab run.
What it shows: The index-scan figure reads 1 on the very next query because counters are accumulated per backend and flushed on a delay, so an increment from just before the reset lands just after it. This is why the reset starts a measurement window rather than guaranteeing a zero. The planner's column statistics are untouched throughout.
Why this answer scores
The two-buttons framing is what gets remembered. Plenty of candidates name the function; far fewer can say cleanly what it does not affect, and that distinction is the actual content of the question. Volunteering the delayed-flush caveat also signals that you have run this on something busier than a laptop.
What they ask next
- You reset, then immediately read, and one counter is already non-zero. Is that a bug?
- No. Counters are accumulated per backend and flushed on a delay, so activity from just before the reset can land just after it. Read a few seconds later, or measure the difference between two reads.
- Does this affect autovacuum?
- Yes, indirectly. Autovacuum compares the table's dead-tuple count with its threshold. The reset sets that count to zero, along with the modification count that triggers autoanalyze. A table that already has dead rows looks clean until new ones accumulate, so the next autovacuum and autoanalyze can be delayed.
- How do you clear the planner's statistics instead?
- You replace them. ANALYZE recomputes them from a fresh sample. Through PostgreSQL 17 there is no reset function for column statistics. PostgreSQL 18 adds pg_clear_attribute_stats and pg_clear_relation_stats.
Pitfall
- Naming the database-wide reset when asked for a single table.
- Claiming the reset also refreshes planner statistics.
- Presenting a read taken immediately after the reset as a guaranteed zero baseline.
- Forgetting that this is irreversible and shared.
Do not memorise
- The counter values from any particular run.
- The full list of columns in the table statistics view.
From the lab to the room
Reset one table's counters on a system with real traffic, then read them straight away and again ten seconds later. The difference between those two reads is the whole caveat, and having seen it makes it a natural thing to mention.
How to reason through it
- Reach for pg_stat_reset_single_table_counters, which zeroes just that table's counters.
- Contrast it: pg_stat_reset clears the whole current database's counters; pg_stat_statements_reset is a separate per-query subsystem.
- Draw the key line: this resets activity counters, not planner column statistics.
- Note that column statistics are refreshed by ANALYZE, not by any reset function.
- Mention that counters are flushed with a delay, so a read taken immediately after can still show a stray increment.
What I would verify
- Take the baseline read a few seconds after the reset, not immediately, so delayed flushes have landed.
- Prefer before-and-after subtraction over an absolute reading when the system is busy.
- Confirm nobody else is mid-measurement on that table before clearing it, since the reset is not reversible.
- Check that planner statistics were untouched if plan behaviour is also in question.
Follow-ups they push on
- You reset, then immediately read, and one counter is already non-zero. Is that a bug?
- Does this affect autovacuum?
- How do you clear the planner's statistics instead?
Worked responses are above, inside the answer.
Concepts tested
Learn it, run it, then say it
Three steps, in order. Nothing here is a detour.
1 · Learn the mechanism
Understand it before you try to say it.
2 · Practise it for real
Run it once so the answer describes something you have seen.
3 · Rehearse the next question
Keep going while the mechanism is fresh.
Questions that go with this one
- senior · ProTake me from that sample to a query plan, what does ANALYZE compute, and how does the planner use it?What ANALYZE maintains, which is the half a counter reset never affects.
- senior · Pron_live_tup and n_dead_tup are estimates. If I need the real number of tuples in a table, what do you reach for?The other question about which of these numbers are estimates and which are measurements.