Using EXPLAIN and Understanding the Optimizer

Using EXPLAIN and Understanding the Optimizer

Understanding the internals of the database is important when tuning a system. Knowing what is going on inside the database will allow you to detect bottlenecks and inefficient sequences of code. This section will guide you through the world of PostgreSQL's internals, and you will see what PostgreSQL does internally to find a way through a query.

Understanding Execution Plans

PostgreSQL users can use EXPLAIN to determine how the optimizer will execute a query. Simply submit a query to the database by using EXPLAIN to the database, and the database will provide a list of all necessary steps to process a query. The following is an overview of the EXPLAIN command:

 performance=#  \h EXPLAIN  Command:     EXPLAIN Description: Shows statement execution plan Syntax: EXPLAIN [ VERBOSE ] query 

Simply add the query you want to analyze to EXPLAIN , and the database will show the execution plan:

 performance=#  EXPLAIN SELECT * FROM perftest WHERE anumber=68374313;  NOTICE:  QUERY PLAN: Seq Scan on perftest  (cost=0.00..193494.00 rows=1 width=20) EXPLAIN 

Because we have not created an index on anumber , the database can only perform a sequential scan on perftest to find the right value. Sequential scan means that the database has to read the entire table to find the result. In many books about databases, sequential scans are also called full table scans.

In the previous example, we can see that the database estimates the cost of executing the query. The cost is measured in units of disk page fetches, and PostgreSQL counts only these disk page fetches that are important for the query planner. Everything that is not affecting the query plan will silently be omitted.

The database also estimates how many rows will be returned by the query and tells us how many bytes per row are used (this value is also estimated by the database).

In the previous example, we have shown the short version of the execution plan. For those who want to go deeper into detail, we have included a short and a long version of the execution plan of 1 + 1. You will see in the long version how complicated simple operations can be and the huge amount of information the PostgreSQL tells the user .

The following is the short version:

 performance=#  EXPLAIN SELECT 1 + 1;  NOTICE:  QUERY PLAN: Result  (cost=0.00..0.00 rows=0 width=0) EXPLAIN 

As you might have expected, the short version looks very simple, but let's come to the long version now. We have to use EXPLAIN with VERBOSE enabled:

 performance=#  EXPLAIN VERBOSE SELECT 1 + 1;  NOTICE:  QUERY DUMP: {  RESULT :startup_cost 0.00 :total_cost 0.00 :rows 0 :width 0 :state <> :qptargetlist ({  TARGETENTRY :resdom {  RESDOM :resno 1 :restype 23 :restypmod -1 :resname ?column? :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }  :expr {  CONST :consttype 23 :constlen 4 :constisnull false :constvalue  4 [ 2 0 0 0 ]  :constbyval true } } ) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0  :resconstantqual <>} NOTICE:  QUERY PLAN: Result  (cost=0.00..0.00 rows=0 width=0) 

The whole thing looks a little more difficult now, but if you take a closer look at the query, plan you will see that it is not as difficult as it seems at first sight. In line two, for example, you can see the name of the column displayed in the result ( ?column? ) but also most other columns can be understood when dealing with PostgreSQL more extensively. We want to go further into detail at this point, because this would lead too far and won't help you when tuning your database. We have just included this here to show you that building a database server is truly a little more complicated than writing Hello World applications. As you can see, verbose makes PostgreSQL display a lot of information that is used only internally, such as the data types involved in the query (see restype 23 or resname ?column? ) that tells us the name of a column in the result of the query.

After this small insight into PostgreSQL's internals, we will go through some queries including the optimizer information now.

The following example shows a very simple query using an index. The query retrieves a certain ID from the table.

 performance=#  EXPLAIN SELECT * FROM perftest WHERE id=1;  NOTICE:  QUERY PLAN: Index Scan using idx_id_perftest on perftest  (cost=0.00..4.98 rows=1 width=20) 

id is indexed and the database uses that index to perform the query quickly. You can see that the database tells us which index is used and which is the table for which the index was defined. If you take a closer look at the cost, you will recognize that the database needs only 4.98 units instead of 193494.00 units, as we had before.

The next query is a little more complex. We want to count how often an ID appears in the table.

 performance=#  EXPLAIN SELECT id, COUNT(*) FROM perftest GROUP BY id;  NOTICE:  QUERY PLAN: Aggregate  (cost=0.00..1551879.15 rows=1000000 width=4)   ->  Group  (cost=0.00..1526879.15 rows=10000000 width=4)         ->  Index Scan using idx_id_perftest on perftest  (cost=0.00..1501879.15 rows=10000000 width=4) cx EXPLAIN 

As you can see, the database has to perform three different types of operations: Aggregation, grouping, and scanning the index.

The result of the previous query is 10 million rows long. For debugging purposes, it is sometimes useful to display only the first few values of the result. We can use the LIMIT command to skip records. Now we want to compare the query plan of the following query by using LIMIT with the previous query that does not use LIMIT .

 performance=#  EXPLAIN SELECT id, COUNT(*) FROM perftest GROUP BY id LIMIT 5;  NOTICE:  QUERY PLAN: Aggregate  (cost=0.00..1551879.15 rows=1000000 width=4)   ->  Group  (cost=0.00..1526879.15 rows=10000000 width=4)         ->  Index Scan using idx_id_perftest on perftest  (cost=0.00..1501879.15 rows=10000000 width=4) EXPLAIN 

You can see that the query plan is exactly the same. The query that does not use LIMIT takes much longer to complete because the database starts to display the result when the query is ready and a query that returns 10 millions of records will take longer to complete.

This shows that LIMIT can have a significant advantage, but it also shows that the way the database processes a query internally won't differ .

The next example performs the same query but returns only records where the result of the COUNT operation is higher than one.

 performance=#  EXPLAIN SELECT id, COUNT(*) FROM perftest GROUP BY id   HAVING COUNT(*) > 1;  NOTICE:  QUERY PLAN: Aggregate  (cost=0.00..1576879.15 rows=1000000 width=4)   ->  Group  (cost=0.00..1526879.15 rows=10000000 width=4)         ->  Index Scan using idx_id_perftest on perftest  (cost=0.00..1501879.15 rows=10000000 width=4) EXPLAIN 

The query plan is the same again because the HAVING clause is not more than an additional condition that won't affect the way the data is read. Grouping, sorting, and aggregation usually take longer than testing conditions, so there is no reason for the optimizer to change the query plan.

The whole thing looks a little different when we try to find values that occur more than once in a column that is not indexed.

 performance=#  EXPLAIN SELECT astring, COUNT(*) FROM perftest GROUP BY astring   HAVING COUNT(*) > 1 LIMIT 10;  NOTICE:  QUERY PLAN: Aggregate  (cost=2104428.83..2179428.83 rows=1000000 width=12)   ->  Group  (cost=2104428.83..2129428.83 rows=10000000 width=12)         ->  Sort  (cost=2104428.83..2104428.83 rows=10000000 width=12)               ->  Seq Scan on perftest  (cost=0.00..168494.00 rows=10000000 width=12) EXPLAIN 

The database has to sort the data now and can't use an index because no index has been defined. In reality, an index is no more than a sorted list; if you take this into consideration, it seems obvious that the database has to sort all records if no index is defined. In general, sorting takes a lot of time, and in most cases it is very bad for the performance of your system.

Analyzing queries is extremely interesting when dealing with joins. As you can see in the following, we create a table called jointab and insert three values into it. Keep in mind that the values we are going to insert are taken from pertest table and have been generated stochastically. To test the example, select values from your table and use them instead of the stochastic values used here:

 CREATE TABLE jointab(id int4, bstring text, bnumber integer, [ic::ccc]PRIMARY KEY (id)); INSERT INTO jointab VALUES(183,'ggzrsFgz',59457349); INSERT INTO jointab VALUES(207,'Aht5d1wa',59457349); INSERT INTO jointab VALUES(56841,'loghwtfc',45671023); 

We write a query that returns all IDs existing in the perftest and jointab tables. This is a very simple join operation that can be performed quickly by PostgreSQL:

 performance=#  EXPLAIN SELECT a.id AS perftest, b.id AS jointab FROM perftest   AS a, jointab AS b WHERE a.id=b.id;  NOTICE:  QUERY PLAN: Nested Loop  (cost=0.00..16.02 rows=3 width=8)   ->  Seq Scan on jointab b  (cost=0.00..1.03 rows=3 width=4)   ->  Index Scan using idx_id_perftest on perftest a (cost=0.00..4.98 rows=1 width=4) EXPLAIN 

perfest is processed by scanning the index. A sequential scan is used to retrieve data from jointab . Nested loops are something very dangerous because whole loops have to be processed for all records. Depending on how long the loop takes to be processed, the query can be rather fast or extremely slow. When you find a nested loop in your execution plan, you have to be very careful with the query.

Let's see what the database does when we create an index on id in the jointab table. To create an index, CREATE INDEX can be used. Before we create the index, we will have a look at the syntax overview of CREATE INDEX :

 performance=#  \   h   CREATE INDEX  Command:     CREATE INDEX Description: Constructs a secondary index Syntax: CREATE [ UNIQUE ] INDEX index_name ON table     [ USING acc_name ] ( column [ ops_name ] [, ...] ) CREATE [ UNIQUE ] INDEX index_name ON table     [ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] ) 

With the help of the syntax overview, creating the index will be simple:

  CREATE INDEX idx_id_jointab ON jointab (id);  

After vacuuming the table, we look at the execution plan again:

 performance=#  EXPLAIN SELECT a.id AS perftest, b.id AS jointab FROM perftest AS   a, jointab AS b WHERE a.id=b.id;  NOTICE:  QUERY PLAN: Nested Loop  (cost=0.00..16.02 rows=3 width=8)   ->  Seq Scan on jointab b  (cost=0.00..1.03 rows=3 width=4)   ->  Index Scan using idx_id_perftest on perftest a (cost=0.00..4.98 rows=1 width=4) EXPLAIN 

Things haven't changed. The execution plan is exactly the same as previously shown. This shows that it is not always useful for the database to use an index if an index exists for a column. In the previous query, it seems faster for the execution planner to use a sequential scan instead of an index scan. Indexes are very fast for huge amounts of data, but when the amount of data stored in a table is very small, the overhead of processing the index is much higher than performing a sequential scan.

In the next example, we will show one way the database processes aggregation and join operations:

 performance=#  EXPLAIN SELECT COUNT(*) FROM perftest AS a, jointab AS b   WHERE a.id=b.id AND astring=bstring;  NOTICE:  QUERY PLAN: Aggregate  (cost=16.04..16.04 rows=1 width=32)   ->  Nested Loop  (cost=0.00..16.04 rows=1 width=32)         ->  Seq Scan on jointab b  (cost=0.00..1.03 rows=3 width=16)         ->  Index Scan using idx_id_perftest on perftest a (cost=0.00..4.99 rows=1 width=16) EXPLAIN 

The query is processed nearly the same way as the previous example. The only thing that had to be added was the aggregation.

Subqueries are used to determine unknown criteria for using the information in the main query. In the following example, we will run a subquery to find the average value of all records in anumber . The result of the subquery is used to select all records from the jointab table that are higher than the result of the subquery.

 performance=#  EXPLAIN SELECT jointab.bnumber FROM perftest, jointab   WHERE jointab.bnumber > (SELECT AVG(perftest.anumber) FROM perftest);  NOTICE:  QUERY PLAN: Nested Loop  (cost=0.00..268495.04 rows=10000000 width=8)   InitPlan     ->  Aggregate  (cost=193494.00..193494.00 rows=1 width=4)       ->  Seq Scan on perftest  (cost=0.00..168494.00 rows=10000000 width=4)   ->  Seq Scan on jointab  (cost=0.00..1.04 rows=1 width=4)   ->  Seq Scan on perftest  (cost=0.00..168494.00 rows=10000000 width=4) EXPLAIN 

The database uses a sequential scan to retrieve all data from the perftest table. No index is used because no selection has to be done for the records in the perftest table. A sequential scan is also used for scanning table jointab .

Views are virtual tables that can be defined on top of views or tables. What happens when PostgreSQL has to perform SELECT statements with views involved, and what will the execution plan look like? We will try to answer the question with the help of an example. We create a view first:

  CREATE VIEW view_perftest AS SELECT anumber/id AS first, astring FROM perftest;  

Let's take look at the execution plan of a simple full table scan.

 performance=#  EXPLAIN SELECT * FROM view_perftest;  NOTICE:  QUERY PLAN: Seq Scan on perftest  (cost=0.00..168494.00 rows=10000000 width=20) EXPLAIN 

The database performs a sequential scan on the perftest table. This seems obvious because perftest is the only table used by the view. The execution plan won't tell us anything about the view we use. The most important information for the optimizer is which tables have to be scanned. Views are virtual tables and are based on "real" tables.

The way PostgreSQL presents the execution plan is truly a useful and easy to understand one. To tune your PostgreSQL database, reading execution plans can be recommended because this will make you understand the database in a better way, and you can write faster SQL statements when you know what can go wrong inside the database. We have also tried to show that defining an endless number of indexes is not the fastest way to achieve a result in all cases. Tuning is sometimes nothing more than trial and error; you should keep this in mind. Try to define an index on a column and see how much performance you will gain or loose.

Another important point when working with indexes is indexing and function. In some cases, functions and indexes can be a problem. Like most databases, PostgreSQL can't use an index when a function is involved in the query. Look at the following example. Executing the query will take a very long time, so you can interrupt the query using Ctrl+C:

 performance=#  SELECT id FROM perftest WHERE id*2=4;  Cancel request sent ERROR:  Query was cancelled. performance=#  EXPLAIN SELECT id FROM perftest WHERE id*2=4;  NOTICE:  QUERY PLAN: Seq Scan on perftest  (cost=0.00..218966.00 rows=100000 width=4) EXPLAIN 

We want to find all values in the perftest table where id multiplied by 2 is exactly 4. We have an index on the id column, and this index is used when performing a simple search for a value without a multiplication in the WHERE clause. However, if a function such as a multiplication is performed with a column, PostgreSQL won't be able to use an index anymore. Keep this in mind when working with functions because this knowledge will make you write faster SQL statements, and you don't need to wonder anymore why a query performs ghastly slow.

VACUUM

The VACUUM command removes expired rows from the files in which the table of a database is stored. PostgreSQL creates new records in a file when performing updates and marks the old records as expired ; updated records are not overwritten immediately. This may seem strange , but is very important for the database. Expired rows can be useful for users who are still in a transaction and have to see the expired data for completing the transaction.

VACUUM removes the expired records from a row and speeds up the database. Let's take a look at what the system tells us about VACUUM :

 performance=#  \h VACUUM  Command:     VACUUM Description: Clean and analyze a Postgres database Syntax: VACUUM [ VERBOSE ] [ ANALYZE ] [ table ] VACUUM [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ] 

As you can see, VACUUM can be used for tables and columns. If VACUUM is called without arguments, PostgreSQL vacuums all tables in the database. We recommend that you VACUUM all tables periodically when the load of the system is low. VACUUM ing will speed up your system significantly, especially on systems that have to perform a lot of SQL statements that are used to change data.

Note

PostgreSQL databases version 6.5 have to lock a table completely while performing VACUUM .

With the help of the verbose flag, we can find out a little bit more about what is done by VACUUM :

 performance=#  VACUUM VERBOSE perftest;  NOTICE:  --Relation perftest-- NOTICE:  Pages 68494: Changed 0, reaped 0, Empty 0, New 0; Tup 10000000: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 52, MaxLen 52; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 33.99s/12.25u sec. NOTICE:  Index idx_id_perftest: Pages 34604; Tuples 10000000. CPU 16.05s/15.29u sec. VACUUM 

We can see in the previous listing that the database displays quite a lot of information about the table, such as the number of pages used to store the database (a page is a block of data that is read at once by the database to achieve better performance).

One of the most important commands for tuning a database is VACUUM ANALYZE This command is similar to VACUUM but collects important information when the optimizer needs to find the best way through a query, such as the number of duplicated values and so on. The information collected by VACUUM ANALYZE is stored in pg_statistics .

We have used VACUUM ANALYZE in a previous section to make the query use an index. Before using VACUUM ANALYZE , the optimizer has no suitable statistical information about the table so it did a sequential scan. Every time the data in a table changes significantly, we recommend using VACUUM ANALYZE . Loading a table, as we did in the previous section, is a significant change because we were loading 10 million records into an empty table.

Next, we have added an example of how VACUUM ANALYZE can be used to process one column of a certain table.

 performance=#  VACUUM VERBOSE ANALYZE perftest(id);  NOTICE:  --Relation perftest-- NOTICE:  Pages 68494: Changed 0, reaped 0, Empty 0, New 0; Tup 10000000: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 52, MaxLen 52; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 21.10s/28.76u sec. NOTICE:  Index idx_id_perftest: Pages 34604; Tuples 10000000. CPU 10.28s/18.38u sec. VACUUM 

There are plans for PostgreSQL supporting a separate command called ANALYZE instead of using VACUUM ANALYZE in future versions.