Attacking low performance

After this basic inspection, it is time to get rid of a very common problem: wrong and missing indexes.

Reviewing indexes

At this point, most of you may say, "indexing? Come on! What is the point? We know that; let's work on real stuff!" From experience, I can tell you with absolute certainty that broken indexing is the single most common cause of bad performance in the world. So, before focusing on anything else in the system, it always makes sense to carry out a safety check to ensure that indexing is definitely okay.

What is the best way to check for missing indexes? In my daily life as a consultant, I use this query:

test=# SELECT relname, seq_scan, seq_tup_read, 
      idx_scan AS idx, 
      seq_tup_read / seq_scan AS ratio 
   FROM  pg_stat_user_tables 
   WHERE seq_scan > 0 
   ORDER BY seq_tup_read DESC
   LIMIT 10;
 relname | seq_scan |   seq_tup_read | idx | ratio 
---------+----------+----------------+-----+----------
 t_user  |  4564324...