This is my first blog in a series of SQL optimization blogs. So expect some basic information in here along with some nice insights. My aim is to help you walk through a complete process of understanding and optimizing queries for improved performance. A PostgreSQL server attempts to find the most effective way of building a result set for the query. The first step in that direction is the ability to understand what the “EXPLAIN” command is.
When a statement is sent to a PostgreSQL server for execution, it must decipher various parts of that query and define an execution plan. Given that data can be retrieved and managed/manipulated in various different ways, a PostgreSQL server attempts to find the most effective way of building a result set for the query. Especially in case of long running queries where performance matters, an execution plan for our statement on a given platform with our existing server configuration determines overall query (and in most cases, application) performance. It is therefore important to be able to understand how a PostgreSQL server breaks down a query execution in form of an execution plan. This is where the “EXPLAIN” command comes in. In very basic terms, “EXPLAIN” command shows (explains) the execution plan of a statement.
Query processing in PostgreSQL is divided into 5 main components:
- Parser
- Analyzer
- Rewriter
- Planner
- Executor
Whereas (1) parser and (2) analyzer are concerned with ensuring that query is written correctly, the result is a query tree which is passed onto (3) rewriter that may transform it based on rules defined in the pg_rules system catalog table. So this transformed query tree is passed on the (4) planner which generates a plan for execution by (5) executor.
The EXPLAIN Command
“EXPLAIN” command has a simple grammar as defined in PostgreSQL documentation. “EXPLAIN” command only explains a given statement, however when run with the “ANALYZE” option, it also executes it. The result set is never thrown back to the client.
So beware, running “EXPLAIN ANALYZE” explains and runs the given statement. Let’s have a look at “EXPLAIN” command’s output by first creating and populating a table called “foo_explain” and then running “EXPLAIN” command on a select statement on “foo_explain”.
postgres=# CREATE TABLE foo_explain(A INT);
CREATE TABLE
postgres=# INSERT INTO foo_explain SELECT GENERATE_SERIES(1,50000);
INSERT 0 50000
postgres=# EXPLAIN SELECT * FROM foo_explain;
QUERY PLAN
----------------------------------------------------------------------------------
Seq Scan on foo_explain (cost=10000000000.00..10000000015.00 rows=50000 width=4)
(1 row)
EXPLAIN Command’s Output
The query plan generated is a very basic one, but there are some important parts that we need to understand to appreciate what the planner has done.
The query plan tells us about a row fetch strategy, “Seq Scan” (sequential scan) on the table “foo_explain”, a startup cost for this operation “10000000000.00” and total cost of “10000000015.00” for the “Seq Scan”. It also gives an estimate of how many rows are expected to be returned (1000) as a result along with the size of a row (4 bytes).
The startup and total costs are arbitrary numbers that provide the planner a way to compare different strategies to select the optimum one. Generally, lower costs indicate faster execution.
The Wrong Estimates
So far so good? It is really important to understand that rows and widths are estimates and not the actual numbers. So there is a likelihood that these might not be accurate at times. Here is an example of what I mean.
postgres=# CREATE TABLE t(a INT, b VARCHAR(1));
CREATE TABLE
postgres=# INSERT INTO t VALUES(1, 'h');
INSERT 0 1
postgres=# SELECT * FROM t;
a | b
---+--------
1 | hello1
(1 row)
postgres=# EXPLAIN SELECT * FROM t;
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on t (cost=10000000000.00..10000000030.40 rows=2040 width=12)
(1 row)
Ouch! For some very odd reason, the planner believes that our select statement will return 2040 rows and 12 bytes per row which amounts to 24,480 bytes if we multiply both numbers. So what’s wrong here? The rows and width are fields are best guesses based on some table data and statistics by PostgreSQL. These can be easily seen in the “pg_class” and “pg_stats” table.
postgres=# SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname = 't'; relname | relkind | reltuples | relpages ---------+---------+-----------+---------- t | r | 0 | 0 (1 row) postgres=# SELECT attname, inherited, n_distinct, postgres-# array_to_string(most_common_vals, E'\n') as most_common_vals postgres-# FROM pg_stats postgres-# WHERE tablename = 't'; attname | inherited | n_distinct | most_common_vals ---------+-----------+------------+------------------ (0 rows)
So clearly, our server thinks that there are no tuples in our table, hence no "relpages" either and there are no stats to help us better estimate output of our select statement. This problem is easily fixed by running “VACUUM ANALYZE” command on the table.
postgres=# VACUUM ANALYZE t; VACUUM
Re-querying “pg_class” and “pg_stats” gives us a more sane picture now.
postgres=# SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname = 't'; relname | relkind | reltuples | relpages ---------+---------+-----------+---------- t | r | 1 | 1 (1 row) postgres=# SELECT attname, inherited, n_distinct, postgres-# array_to_string(most_common_vals, E'\n') as most_common_vals postgres-# FROM pg_stats postgres-# WHERE tablename = 't'; attname | inherited | n_distinct | most_common_vals ---------+-----------+------------+------------------ a | f | -1 | b | f | -1 | (2 rows)
This output makes more sense. Let’s run “EXPLAIN” again.
postgres=# EXPLAIN SELECT * FROM t;
QUERY PLAN
---------------------------------------------------------------------
Seq Scan on t (cost=10000000000.00..10000000001.01 rows=1 width=6)
(1 row)
Clearly now this is an actual reflection of the table. Only 1 row will be returned, as expected, and the size of a row is 6 bytes as compared to 12 bytes given earlier. Needless is to mention that poor statistics can lead to a suboptimal plan leading to poor query performance.
Building a Query from a Given Plan Output
If we can reverse engineer and build a query from a given plan output, then we can safely say that we understand the output. Given that the following plan is the not the most complex one, it still has various different node types. To make it simple, the following “EXPLAIN” query was run with “VERBOSE ON” option.
QUERY PLAN
------------------------------------------------------------------------------------------------
GroupAggregate (cost=10000000010.30..10000000010.35 rows=1 width=23)
Output: count(*), t1.id, t1.name, t2.id
Group Key: t1.id, t1.name, t2.id
Filter: (count(*) > 1)
-> Sort (cost=10000000010.30..10000000010.30 rows=2 width=15)
Output: t1.id, t1.name, t2.id
Sort Key: t1.id DESC, t1.name
-> Nested Loop (cost=10000000004.20..10000000010.29 rows=2 width=15)
Output: t1.id, t1.name, t2.id
Join Filter: (t1.id = t2.id)
-> Seq Scan on public.t1 (cost=10000000000.00..10000000001.25 rows=6 width=11)
Output: t1.id, t1.name
Filter: (t1.id < 8)
-> Materialize (cost=4.20..8.34 rows=8 width=4)
Output: t2.id
-> Bitmap Heap Scan on public.t2 (cost=4.20..8.30 rows=8 width=4)
Output: t2.id
Recheck Cond: (t2.id < 5)
-> Bitmap Index Scan on t2_idx_id (cost=0.00..4.20 rows=8 width=0)
Index Cond: (t2.id < 5)
(20 rows)
Before we start reading the plan in more detail, we see that line 4 indicates the selected columns. So our select statement starts with "SELECT COUNT(*), t1.id, t1.name, t2.id".
Now taking it a step further and starting from the leaf node and traversing upwards as we re-engineer the SQL query:
- Line 20 shows a filter condition on t2.id; i.e. possibly part of WHERE clause;
t2.id < 5 - Lines 16 – 22 indicate that table t2 is part of our query. Additionally, these also indicate that index t2_idx_id on table t2 is being utilised by the plan.
- Similarly, lines 13 – 15 tell us that table t1 is part of our query and is has a filter condition of
t1.id < 8. - Lines 10 – 12 tells us that it is an
INNER JOIN ON t1.id = t2.id.
So these portions of the plan indicate that the where clause and from clause and our query at the moment looks like:
SELECT COUNT(*), t1.id, t1.name, t2.id FROM t1 INNER JOIN t2 ON t1.id = t2.id WHERE t1.id < 8 AND t2.id < 5;
Moving one level up the plan tree we find a SORT node on t1.id DESC, t1.name. Further up, we see a GROUP BY clause on t1.id, t1.name, t2.id with a filter condition; i.e. a HAVING clause with COUNT(*) > 1. Now that we have traversed the entire plan tree, we can pretty much write down the complete SQL statement.
SELECT COUNT(*), t1.id, t1.name, t2.id FROM t1 INNER JOIN t2 ON t1.id = t2.id WHERE t1.id < 8 AND t2.id < 5 GROUP BY t1.id, t1.name, t2.id HAVING COUNT(*) > 1 ORDER BY t1.id DESC, t1.name;
And this matches the original query against which the query plan was generated.
Conclusion
In this blog, we’ve seen the very basics of the “EXPLAIN” command why it is important to ensure that statistics are up to date for a good query performance. In the next blog, I’ll dig a little more deeply into the output of the “EXPLAIN” command with slightly more complex queries and what different factors like indexes, scans, joins and sorting methods impact the query plan. Stay tuned!
In the meantime, feel free to explore the auto_explain which can help you log query plans of slow queries and this very nice query plan visualisation tool: http://tatiyants.com/pev
Hamid has more than 19 years of professional software development experience and has been involved with PostgreSQL for more than 8 years now. He was part of EnterpriseDB and managed PostgreSQL official installers for Windows, Linux and MacOS. Hamid has recently joined HighGo Software Inc. as Senior DevOps and Database Architect.
Recent Comments