Mastering PostgreSQL 9.6

Approaching plans systematically

The first thing you have to know is that an EXPLAIN clause can do quite a lot for you and I would highly recommend to make full use of those features.

As many readers might already know, an EXPLAIN ANALYZE clause will execute the query and return the plan including real runtime information. Here is an example:

test=# EXPLAIN ANALYZE SELECT *     FROM (SELECT *           FROM  b           LIMIT 1000000         ) AS b     ORDER BY cos(bid);                             QUERY PLAN                                                               ----------------------------------------------------------------  Sort  (cost=146173.12..148673.12 rows=1000000)           (actual time=837.049..1031.587 rows=1000000)          Sort Key: (cos((b.bid)::double precision))          Sort Method: external merge  Disk: 25408kB    ->  Subquery Scan on b    (cost=0.00..29424.78 rows=1000000 ...