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.
精彩评论