Explain Explained

The EXPLAIN command is the best tool to understand how a relational database is going to run a query. Databases, such as Postgres, collect stats on each table. When a query is executed, it’s first run through a planner to determine if it will use indices or simply scan tables. This tool has a couple of variations. By itself, it will simply look at stats, but you can also run EXPLAIN ANALYZE. This will pull stats and run the query giving you accurate query times for each step.

Basic Example

To use the tool, run a query prefixed with EXPLAIN like this:

EXPLAIN SELECT * FROM my_table;

                          QUERY PLAN
-------------------------------------------------------------
 Seq Scan on my_table  (cost=0.00..512.00 rows=12345 width=74)

In this example, it’s going to use a table scan noted by the Seq Scan or Sequential Scan.

The cost is a derived number loosely based on how much work is involved in the query. It factors in things like how much it’s going to have to read from the hard drive. The lower the number, generally speaking, the faster the query. The first number, in this case 0.00, is the startup cost. This refers to any actions that have happened before this step can run. The second number, 512.00, is after the step has retrieved all of the rows that it’s going to. Keep in mind that the cost includes any sub tasks, so take the difference between the start up cost and the total cost to determine how expensive a certain step is.

The rows parameter refers to how many rows the planner estimates will be returned by the query. This typically isn’t 100% correct. The planner is using statistics it has gathered rather than running the actual query so the stats are always an estimate. Running ANALYZE on the table will regenerate stats and make this more accurate. Postgres will run ANALYZE automatically when a certain threshold of updates have occurred. If there has recently been a lot of changes, the stats can be inaccurate. Also, it doesn’t refer to how many rows were actually scanned, but how many this step emitted. If it scanned some rows and filtered them out, they won’t count in this number.

The width is the average number of bytes in the rows returned.

Statistics

All of Postgres’ stats are queryable. Understanding how this is set up can really help get the most out of the database. For example, running this query

SELECT relpages, reltuples FROM pg_class WHERE relname = 'my_table';

will tell you things like the number of disk pages (8KB blocks on the hard drive) and number of rows in the table. The cost is derived from these values. If the table is fragmented, i.e. there are a lot of pages in relation to the number of rows, running a VACUUM FULL on the table can help lower the cost. Keep in mind that this type of vacuum locks the table because it rewrites it. If you’re doing this in production, you may have to schedule some down time when doing this.

Things to look for in the Explain output

Later in the chapter, we’ll talk about how to use this information to optimize queries.