开发者

Does postgresql keep track of full table scans it makes?

I'd like to do something similar to what's described in http://www.bestbrains.dk/Blog/2010/03/25/HowToAssertThatYourSQLDoesN开发者_如何转开发otDoFullTableScans.aspx but for that I'd need postgres to keep track of any full table scans it does. Is there such a thing for postgres?


Have a look at pg_stat_get_numscans(oid) (number of sequential scans on a table) and the statistics collector (in general).


I use the below query. If index usage is > 99 I am fine. So I worry about any table having more than 7000 rows and index usage less than 99.

SELECT 
  relname table_name, 
  idx_scan index_scan,
  seq_scan table_scan,
   100*idx_scan / (seq_scan + idx_scan) index_usage, 
  n_live_tup rows_in_table
FROM 
  pg_stat_user_tables
WHERE 
    seq_scan + idx_scan > 0 and 100 * idx_scan / (seq_scan + idx_scan) < 99 and
    n_live_tup > 70000
ORDER BY 
  4 DESC;

Of course you need to understand the query and its plan. Sometimes the table scan could be more optimized.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜