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.
Perform the following steps:
Run the query with
EXPLAIN ANALYZEto 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)
See how many rows are processed and how many blocks of data are accessed by comparing the output of the following query before and after the query is run on an idle system (the
pg_stat*views are global and collect information from all parallel queries):SELECT...