Which of my PostgreSQL indexes are getting used most heavily?

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.

Advertisements

Tags: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: