Once we are done with the first three steps, it is important to take a look at performance in general. As I have kept stating throughout this book, missing indexes are fully responsible for super bad performances. So, whenever we face a slow system, it is recommended that we check for missing indexes and deploy whatever is needed.

Usually, customers ask us to optimize the RAID level, tune the kernel, or some other fancy stuff. In reality, these complicated requests often boil down to a handful of missing indexes. By my judgement, it always makes sense to spend some extra time just checking whether all of the desired indexes are there or not. Checking for missing indexes is neither hard nor time-consuming, so it should be done all of the time, regardless of the kind of performance problem that you are facing.

Here is my favorite query to get an impression of where an index might be missing:

SELECT schemaname, relname, seq_scan, seq_tup_read, 
           idx_scan, seq_tup_read / seq_scan AS avg 
FROM   pg_stat_user_tables 
WHERE  seq_scan > 0 
ORDER BY seq_tup_read DESC 
LIMIT 20;

Try and find large tables (with a high avg value) that are scanned often. These tables will typically come on top.