PostgreSQL 9 Administration Cookbook - Second Edition

Finding out what makes SQL slow

A SQL statement can be slow for a lot of reasons. Here, we will give a short list of these, with at least one way of recognizing each reason.

How to do it…

Perform the following steps:

1. Run the query with EXPLAIN ANALYZE to see how much data is processed in order to complete the query, as follows:

   mydb=# EXPLAIN ANALYZE SELECT count(*) FROM t;
                           QUERY PLAN
   ------------------------------------------------------------------ Aggregate  (cost=4427.27..4427.28 rows=1 width=0) \
                 (actual time=32.953..32.954 rows=1 loops=1)
      ->  Seq Scan on t  (cost=0.00..4425.01 rows=901 width=0) \
                 (actual time=30.350..31.646 rows=901 loops=1)
    Total runtime: 33.028 ms
   (3 rows)
   

2. See how many rows are processed and how many blocks of data are ...