Home > PostgreSQL > Finding top read tables in the database

Finding top read tables in the database

A quick and good way to find the most used tables in a database with regards to reads can be:

PostgreSQL provides a system catalog view “pg_stat_all_tables” for helping DBAs with finding such statistics.

The view looks like this:

Columns
-------------
relid
relname
seq_scan
seq_tup_read
idx_scan
idx_tup_fetch
n_tup_ins
n_tup_upd
n_tup_del

This view has one entry for each table in the database. The seq_scan column tells you how many sequential scans have been performed for a given table and seq_tup_read tells you how many rows were used during those sequence scan. The idx_scan and idx_tup_fetch gives the same kind of information for the index scan usage. The n_tup_ins, n_tup_upd and n_tup_del gives you information on how many rows were inserted, update and deleted.

We can use this view in the following way to get the top 10 most read tables…

SELECT relname, idx_tup_fetch + seq_tup_read as TotalReads from pg_stat_all_tables
WHERE idx_tup_fetch + seq_tup_read != 0
order by TotalReads desc
LIMIT 10;
(With thanks to my favorite book "PostgreSQL by Korry Douglas")


Shoaib Mir
shoaibmir[@]gmail.com

Advertisements
  1. lou fridkis
    September 15, 2009 at 6:43 am

    I don’t understand why you need, “WHERE idx_tup_fetch + seq_tup_read != 0”?

  2. September 15, 2009 at 9:50 am

    Because I don’t need any rows with zero reads in my result set

  1. No trackbacks yet.

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: