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?

• user1822: I don't think there is a way to get that information as Postgres does not store the execution plans (e.g. like Oracle does)
• guettli: @a_horse_with_no_name I see no need for storing the execution plan. A warning which gets emitted at the moment of the seq scan is enough for me.
• Lennart - Slava Ukraini: just curious, pg_stat_all_tables appears to be a view. Is the view definition accessible from the catalog? I don't have a running psql installation to play with, I tried db-fiddle.com but could not find anything

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