In last blog we learned about partial indexes. In this blog lets see how we can take advantage of Postgresql Stats for optimizing our queries and find out on which tables there are no indexes.

Postgresql provide various statistics about the system and provides us with lots of information on how we can optimize our databases.

In this blog we will discuss on sequential scans on tables and how we should optimize them.

Query to find table with sequential scan:

SELECT
    schemaname, relname,
    seq_scan, seq_tup_read,
    seq_tup_read / seq_scan as avg_seq_tup_read
FROM pg_stat_all_tables
WHERE seq_scan > 0
ORDER BY 5 DESC LIMIT 50;
 schemaname |       relname       | seq_scan | seq_tup_read | avg_seq_tup_read 
------------+---------------------+----------+--------------+------------------
 public     | tickets             |        3 |     24000000 |          8000000
 public     | student             |       18 |      5600010 |           311111
 pg_catalog | pg_class            |    22200 |      6789240 |              305
 pg_catalog | pg_index            |       17 |          503 |               29
 pg_catalog | pg_database         |    10553 |       168788 |               15
 public     | provider            |     1109 |         7763 |                7
 pg_catalog | pg_namespace        |       35 |          208 |                5
 pg_catalog | pg_attribute        |       22 |           96 |                4
 pg_catalog | pg_amproc           |        4 |            8 |                2
 pg_catalog | pg_am               |     6679 |         6679 |                1
 pg_catalog | pg_opclass          |        4 |            4 |                1
 pg_catalog | pg_authid           |        2 |            2 |                1
 pg_toast   | pg_toast_159046     |        1 |            0 |                0
 pg_toast   | pg_toast_159120     |        1 |            0 |                0
 pg_catalog | pg_publication      |     1109 |            0 |                0
 pg_catalog | pg_subscription_rel |        2 |            0 |                0

Now from the above view we can see that on tickets table 3 seq scan ran and row count in our table is also (8000000), which means there are bad queries or bad usage of the system is done as system is sequentially searching on this table.

Now what we should do here is figure out what all queries are which runs on the system via the postgresql and logs and see what index we can create so that this query dosen’t do sequential scan.