Which of my PostgreSQL indexes are getting used most heavily?
• Andy • Open source, Programming • indexes, PostgreSQL, SSD, tablespaces, tuning
Ever since we got the fast new database server with SSDs, I’ve been monitoring which tables are getting heavy traffic and should go live on the SSDs. We have two tablespaces, “fast” which is faster but smaller, and “slow” which is bigger but slower. I’ve been using this query to determine which indexes should live in which tablespace. There are different forms of this query around the web, but I needed to see the tablespaces, too.
SELECT i.idx_scan, i.idx_tup_read, i.idx_tup_fetch, i.indexrelname AS index, it.spcname AS index_tablespace, i.relname AS table, tt.spcname AS table_tablespace, pg_size_pretty(pg_relation_size(i.indexrelname::text)) as index_size FROM pg_stat_all_indexes i INNER JOIN pg_class ic ON (i.indexrelid = ic.oid) LEFT OUTER JOIN pg_tablespace it ON (ic.reltablespace = it.oid) INNER JOIN pg_class tc ON (i.relid = tc.oid) LEFT OUTER JOIN pg_tablespace tt ON (tc.reltablespace = tt.oid) ORDER BY 1 desc, 2 desc, 3 desc
The output looks like this (in \x mode because of the width):
-[ RECORD 1 ]----+---------------------------------------------------- idx_scan | 395974172 idx_tup_read | 432974893 idx_tup_fetch | 426070104 index | testbook_pkey index_tablespace | fast table | testbook table_tablespace | fast index_size | 289 MB -[ RECORD 2 ]----+---------------------------------------------------- idx_scan | 133416135 idx_tup_read | 133441801 idx_tup_fetch | 133413399 index | lists_listid_custid index_tablespace | fast table | lists table_tablespace | fast index_size | 7096 kB -[ RECORD 3 ]----+---------------------------------------------------- idx_scan | 50310975 idx_tup_read | 1286116 idx_tup_fetch | 742639 index | listdetail_bkkey_listid_where_ctr2_is_zero index_tablespace | fast table | listdetail table_tablespace | fast index_size | 682 MB
I have one case where a heavily-trafficked table is still staying on the slow tablespace. It’s a log of user login history that is only ever appended to, and is searched only a few times a day. SSDs are great at random reads, but not much faster than physical spindles on sequential writes. Therefore, my login history would not benefit much from moving to the SSD tablespace, and I can allocate that precious space to another table or index instead.