Understanding execution plans

Now that we’ve dug into some important optimizations that are implemented in PostgreSQL, let’s take a closer look at execution plans. You’ve already seen some execution plans in this book. However, to make full use of plans, it’s important to develop a systematic approach when it comes to reading this information.

Approaching plans systematically

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

As you may already know, an EXPLAIN ANALYZE clause will execute the query and return the plan, including real runtime information. Here’s an example:

test=# EXPLAIN ANALYZE SELECT *
FROM
(
    SELECT *
    FROM b
    LIMIT 1000000
) AS b
ORDER BY cos(bid);
                  ...