What constitutes a concerning large value for the seq_scan column in PostgreSQL's pg_stat_user_tables

Question

I'm trying to identify which tables in my Postgres DB are missing indexes, for which I'm looking at the pg_stat_user_tables. I read that "large" values in the seq_scan column are an indicator of missing indexes, but I'm looking for more clarity on what constitutes "large".

Is it an absolute value, or is it dependent on the number of rows in the table? As an example, when running this query, the largest values I see for seq_scan and tup_per_scan are 7,617,366 and 3,253,864 respectively.

SELECT relname,
       seq_scan,
       seq_tup_read / seq_scan AS tup_per_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0;

Thank you!

Answer 1

First, you have to take into account since when these statistics are collected:

SELECT datname, stats_reset
FROM pg_stat_database;

It makes a difference if those 7 million scans happened in a month or five years.

I would correlate this finding with the queries in pg_stat_statements that have the highest total_exec_time: if one of your top statements involves this query, look at the execution plan. Perhaps an index can reduce your overall resource consumption.