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.
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.
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.
Filter means the table was scanned and the row was filtered out. This typically happens in WHERE clauses that don’t use indices. Sometimes an index isn’t used, even if it exists for that column. This might be on a table with a small number of rows, or when using a range (i.e. using > or <) that is too large. The planner will determine that it’s faster to simply scan the table rather than to scan both the index and the data file.
Index Cond is generally the preferred thing to see. It means an index was used and is most commonly found when checking for equality (i.e. using the =)
Bitmap refers to rows that have been returned in another step that are sitting in memory. For example, if your query has WHERE foo='abc' AND bar='def', PG will scan for any rows that have the value of “abc” in the foo column and put that in a bitmap. It will do the same for the bar column. Note: bitmaps aren’t cached or saved beyond the query.
Recheck if you have two bitmaps that both need to be true, PG will scan through them and filter out any rows that don’t match both.
Limit shows up when using a LIMIT clause. In this case, the planner generally does things very differently since it knows it doesn’t have to return everything.
Materialize happens a lot in JOIN steps. The results of the table are saved into memory so it doesn’t have to fetch the data again as it loops over it.
Later in the chapter, we’ll talk about how to use this information to optimize queries.