PostgreSQL: Log statements which do sequential scans

Question

I use this snippet to detect missing indexes:

https://stackoverflow.com/a/12818168/633961

Example:

SELECT
   relname                                               AS TableName,
   to_char(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
   to_char(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
   to_char(n_live_tup, '999,999,999,999')                AS TableRows,
   pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
 FROM pg_stat_all_tables
 WHERE schemaname = 'public'
       AND 50 * seq_scan > idx_scan -- more then 2%
       AND n_live_tup > 10000
       AND pg_relation_size(relname :: regclass) > 5000000
 ORDER BY relname ASC;

Result:

tablename | totalseqscan | totalindexscan | tablerows | tablesize
----------+--------------+----------------+-----------+----------
 mytable  |      112,479 |      2,978,344 | 1,293,536 |   1716 MB

I am curious - I would like to see which SQL statements actually does a seq scan on table mytable.

Is there a way to let PostgreSQL emit a warning if it does a sequential scan on this table?

Answer 1

I think the only way to do this is to use the auto_explain module and enable dumping of execution plans if a statement is slower than e.g. a second.

The plan will be written into the Postgres log file

Then you can have a job that monitors the log file and takes actions if a Seq Scan is part of the plan.

---

A Seq Scan is not something that should be avoided at all costs. It often the fastest way to get the result.

I would focus on plans with Seq Scans, but simply try to find (and then fix) slow queries.

For analysing the logfile you want to have a log at pgbadger or you can use POWA to monitor your server in real time