Checking Chado/Drupal Performance Stats

README.md

Checking for Sequential Scans

We want to limit sequential scans which return lots of rows as they are very slow. When you see a high number of sequential scans on a table it lets you know that you are missing an important index.

To check this we are going to look at the pg_stat_all_tables PostgreSQL table which contains one row for each table in the current database and shows statistics about accesses to that specific table. I'm still looking into how to tell the time period over which these stats were collected.

SELECT
    schemaname as schema, 
    relname as table,
    seq_scan as num_seqscans,
    seq_tup_read / seq_scan as avg_rows_per_seqscan,
    idx_scan as num_indexscans,
    idx_tup_fetch / NULLIF(idx_scan, 0) as avg_rows_per_indexscan
FROM pg_stat_all_tables
WHERE seq_scan > 0
ORDER BY 4 DESC LIMIT 10;

Resources:

• Data Egret: Deep dive into postgres stats: pg_stat_all_tables