Statistics Views and Counters

🔎 Read counters and count the actual rows

Goal: separate a table’s actual contents from its cumulative statistics.

Count the 50,000 fixture rows, then read its counters. seq_scan and idx_scan count scan starts, not rows or full-table passes; n_live_tup and n_dead_tup are estimates. Setup disabled autovacuum only on this fixture so cleanup does not race the experiment. ANALYZE supplied initial planner statistics. Counters can include setup and your inspection, so record the baseline rather than assuming zero.

All hints run at the Linux shell. psql -c executes quoted SQL and exits; each command uses a new connection. -U postgres explicitly selects the administrative SQL role, and -v ON_ERROR_STOP=1 stops on SQL errors. The monitoring tables are separate provided fixtures, not the baked business datasets.

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "SELECT count(*) AS actual_rows FROM monitoring.orders;"

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "
SELECT relname,
       seq_scan,
       idx_scan,
       n_live_tup,
       n_dead_tup
FROM pg_stat_user_tables
WHERE relid='monitoring.orders'::regclass;
"

actual_rows ------------- 50000 (1 row) relname | seq_scan | idx_scan | n_live_tup | n_dead_tup ---------+----------+----------+------------+------------ orders | 3 | 0 | 50000 | 0 (1 row)

🔎 Inspect and repeat an unindexed lookup

Goal: observe a sequential scan, then generate 100 scan starts.

EXPLAIN (COSTS OFF) shows the chosen plan without executing it; expect a Seq Scan below Limit. The provided DO block is a server-side loop: it repeats the lookup 100 times and discards the result. LIMIT 1 can stop a scan early, so 100 scan starts do not mean 100 complete table reads. The shell escapes $ to pass SQL dollar quoting literally. Read the counter again after this client exits so its statistics have been published.

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "EXPLAIN (COSTS OFF) SELECT * FROM monitoring.orders WHERE item='item-7' LIMIT 1;"

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "
DO \$\$ DECLARE r monitoring.orders%rowtype;
BEGIN FOR i IN 1..100 LOOP SELECT * INTO r
FROM monitoring.orders
WHERE item='item-7' LIMIT 1;
END LOOP;
END \$\$;
"

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "
SELECT relname,
       seq_scan,
       idx_scan,
       n_live_tup,
       n_dead_tup
FROM pg_stat_user_tables
WHERE relid='monitoring.orders'::regclass;
"

QUERY PLAN ----------------------------------------- Limit -> Seq Scan on orders Filter: (item = 'item-7'::text) (3 rows) DO relname | seq_scan | idx_scan | n_live_tup | n_dead_tup ---------+----------+----------+------------+------------ orders | 103 | 0 | 50000 | 0 (1 row)

🔎 Inspect and repeat an indexed lookup

Goal: compare the chosen customer index with the previous access path.

The customer lookup should use idx_orders_customer_id; do not infer index use merely from its existence. Repeat it 100 times, then read both the table summary and this index’s own counter. The table’s idx_scan sums its indexes, including the primary key used by other queries. Counters are cumulative; planner choices depend on the data and query, not a rule that indexed columns always force an index.

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "EXPLAIN (COSTS OFF) SELECT * FROM monitoring.orders WHERE customer_id=42 LIMIT 1;"

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "
DO \$\$ DECLARE r monitoring.orders%rowtype;
BEGIN FOR i IN 1..100 LOOP SELECT * INTO r
FROM monitoring.orders
WHERE customer_id=(i%500)+1 LIMIT 1;
END LOOP;
END \$\$;
"

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "
SELECT relname,
       seq_scan,
       idx_scan,
       n_live_tup,
       n_dead_tup
FROM pg_stat_user_tables
WHERE relid='monitoring.orders'::regclass;
"

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "
SELECT indexrelname,
       idx_scan
FROM pg_stat_user_indexes
WHERE indexrelid='monitoring.idx_orders_customer_id'::regclass;
"

QUERY PLAN --------------------------------------------------------- Limit -> Index Scan using idx_orders_customer_id on orders Index Cond: (customer_id = 42) (3 rows) DO relname | seq_scan | idx_scan | n_live_tup | n_dead_tup ---------+----------+----------+------------+------------ orders | 103 | 100 | 50000 | 0 (1 row) indexrelname | idx_scan ------------------------+---------- idx_orders_customer_id | 100 (1 row)

🔎 Calculate the shared-buffer hit ratio

Goal: measure where heap-block requests were satisfied.

Compute hits divided by hits plus reads, as a percentage. NULLIF(...,0) avoids division by zero when no requests have been counted. A hit means the block was already in PostgreSQL shared buffers; a read is a miss there and may still be served by the OS page cache. This ratio does not measure physical disk traffic, current cache occupancy, or whether the whole working set fits. Exact totals depend on earlier activity.

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "
SELECT heap_blks_read,
       heap_blks_hit,
       idx_blks_read,
       idx_blks_hit,
       round(100.0*heap_blks_hit/nullif(heap_blks_hit+heap_blks_read,0),1) AS heap_hit_pct
FROM pg_statio_user_tables
WHERE relid='monitoring.orders'::regclass;
"

heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | heap_hit_pct ----------------+---------------+---------------+--------------+-------------- 0 | 51872 | 2 | 199408 | 100.0 (1 row)

🔎 Update rows and inspect the estimates

Goal: create obsolete row versions while keeping the logical row count unchanged.

Update every fifth ID: expect UPDATE 10000. Confirm 50,000 visible rows and 10,000 shipped rows, then inspect the estimated live/dead counts. UPDATE creates new versions; old versions can occupy space until pruning/vacuum removes them. Estimates are observations, not an exact COUNT(*) substitute.

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "UPDATE monitoring.orders SET status='shipped' WHERE id%5=0;"

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "
SELECT count(*) AS actual_rows,
       count(*) FILTER (WHERE status='shipped') AS shipped_rows
FROM monitoring.orders;
"

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "
SELECT relname,
       seq_scan,
       idx_scan,
       n_live_tup,
       n_dead_tup
FROM pg_stat_user_tables
WHERE relid='monitoring.orders'::regclass;
"

UPDATE 10000 actual_rows | shipped_rows -------------+-------------- 50000 | 10000 (1 row) relname | seq_scan | idx_scan | n_live_tup | n_dead_tup ---------+----------+----------+------------+------------ orders | 105 | 100 | 50000 | 10000 (1 row)

🔎 Reset the table’s own statistics

Goal: show that the table and its indexes have separate counter entries.

regclass resolves the qualified relation name; oid is its catalog identity, not a row ID. Reset the table OID and inspect it. Expect zero table scan/live/dead estimates while index scans remain nonzero. The rows still exist: resetting estimates can also affect autovacuum decisions, so this is a controlled experiment, not routine production cleanup.

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "SELECT pg_stat_reset_single_table_counters('monitoring.orders'::regclass::oid);"

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "
SELECT relname,
       seq_scan,
       idx_scan,
       n_live_tup,
       n_dead_tup
FROM pg_stat_user_tables
WHERE relid='monitoring.orders'::regclass;
"

pg_stat_reset_single_table_counters ------------------------------------- (1 row) relname | seq_scan | idx_scan | n_live_tup | n_dead_tup ---------+----------+----------+------------+------------ orders | 0 | 100 | 0 | 0 (1 row)

🔎 Observe one more customer-index scan

Goal: prove the unreset index is still counting from its prior total.

Count the rows for customer 42: expect 100. Read its index counter and the table summary. The named index should now have at least 101 scans, despite the earlier table reset. Looking at statistics alone does not access the orders heap; queries against the fixture can themselves change its counters.

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "SELECT count(*) AS customer_rows FROM monitoring.orders WHERE customer_id=42;"

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "
SELECT indexrelname,
       idx_scan
FROM pg_stat_user_indexes
WHERE indexrelid='monitoring.idx_orders_customer_id'::regclass;
"

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "
SELECT relname,
       seq_scan,
       idx_scan,
       n_live_tup,
       n_dead_tup
FROM pg_stat_user_tables
WHERE relid='monitoring.orders'::regclass;
"

customer_rows --------------- 100 (1 row) indexrelname | idx_scan ------------------------+---------- idx_orders_customer_id | 101 (1 row) relname | seq_scan | idx_scan | n_live_tup | n_dead_tup ---------+----------+----------+------------+------------ orders | 0 | 101 | 0 | 0 (1 row)

🔎 Reset every index on this table

Goal: reset both the customer index and the primary key, not just one index.

Read pg_index to enumerate the table’s index OIDs and call the reset for each. indexrelid::regclass prints their readable names. Expect two result rows and then zero aggregate idx_scan. Resetting only the customer index would leave any primary-key scans intact.

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "
SELECT indexrelid::regclass AS index_name,
       pg_stat_reset_single_table_counters(indexrelid)
FROM pg_index
WHERE indrelid='monitoring.orders'::regclass
ORDER BY indexrelid::regclass::text;
"

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "
SELECT relname,
       seq_scan,
       idx_scan,
       n_live_tup,
       n_dead_tup
FROM pg_stat_user_tables
WHERE relid='monitoring.orders'::regclass;
"

index_name | pg_stat_reset_single_table_counters -----------------------------------+------------------------------------- monitoring.idx_orders_customer_id | monitoring.orders_pkey | (2 rows) relname | seq_scan | idx_scan | n_live_tup | n_dead_tup ---------+----------+----------+------------+------------ orders | 0 | 0 | 0 | 0 (1 row)

🔎 Verify the data and restore normal maintenance

Goal: finish by proving that resetting statistics did not delete anything.

Run ANALYZE to rebuild row estimates and reset the fixture’s autovacuum override to its default. Count the actual rows and shipped statuses again: 50,000 and 10,000. Statistics describe activity and estimates; they are not the underlying data.

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "ANALYZE monitoring.orders;"

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "ALTER TABLE monitoring.orders RESET (autovacuum_enabled);"

psql -U postgres -d beer_db -v ON_ERROR_STOP=1 -c "
SELECT count(*) AS actual_rows,
       count(*) FILTER (WHERE status='shipped') AS shipped_rows
FROM monitoring.orders;
"

ANALYZE ALTER TABLE actual_rows | shipped_rows -------------+-------------- 50000 | 10000 (1 row)