2

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?

  • 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) – a_horse_with_no_name Apr 5 '18 at 9:13
  • @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. – guettli Apr 5 '18 at 10:09
  • 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 – Lennart Apr 30 '18 at 19:52
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.