PostgreSQL Tutorial (10): Performance Improvement Tips

1. Use EXPLAIN:

PostgreSQL generates a query plan for each query, because choosing the correct query path has an extremely critical impact on performance. PostgreSQL itself already includes a planner for finding optimal planning. We can view the query plans generated by the planner for each query by using the EXPLAIN command.
The query plan generated in PostgreSQL is a planning tree composed of 1 to n planning nodes. The lowest node is a table scan node, which is used to return the retrieved data rows from the data table. However, different scanning node types represent different table access modes, such as sequential scanning, index scanning, and bitmap index scanning. If the query still requires connection, aggregation, sorting, or other operations on the original row, there will be other additional nodes "above" the scan node. And there are usually multiple methods for these operations, so different node types may also appear in these locations. EXPLAIN will output a row of information for each node in the planning tree, displaying the basic node type and the estimated overhead value calculated by the planner for executing this planning node. The first row (the uppermost node) is the estimate of the total execution overhead of the plan, which is the value the planner is trying to minimize.
Here is a simple example, as follows:
 

The data referenced by EXPLAIN is:
1). Expected startup overhead (time consumed before the output scan starts, such as the time to schedule in a sorting node).
2). The estimated total overhead.
3). The estimated number of rows output by the planned node.
4). The estimated average line width (units: bytes) of the planned node.
The calculation unit of overhead here is the number of accesses to disk pages. For example, 1.0 will represent the order of disk page readings in one order. The overhead of the upper node will include the overhead of all its child nodes. The output rows here are not the number of rows processed/scanned by the planning node, and are usually smaller. Generally speaking, the expected number of rows at the top level will be closer to the number of rows actually returned by the query.
Now we execute the following query based on the system table:
 
From the query results, we can see that the tenk1 table occupies 358 disk pages and 10,000 records. However, in order to calculate the cost value, we still need to know another system parameter value.
 
Let’s take a look at a query plan with WHERE conditions.
 
The output of EXPLAIN shows that the WHERE clause is applied as a "filter", which means that the planning node will scan each row of data in the table, and then determine whether they meet the filtering conditions, and finally output only the number of rows passing the filtering conditions. Here, due to the existence of the WHERE clause, the expected number of output lines is reduced. Even so, the scan will access all 10000 rows of data, so the overhead is not really reduced, and it actually adds some additional CPU overhead due to data filtering.
The above data is just an expected number, and it will change accordingly even after each execution of the ANALYZE command, because the statistics generated by ANALYZE are calculated by randomly drawn samples from the table.
If we set the above query conditions more strictly, we will get different query plans, such as:
 
Here, the planner decides to use two-step planning. The innermost planning node accesses an index, finds the location of the rows that match the index conditions, and then the upper-level planning node reads these rows from the table. Reading data rows alone is much more expensive than reading them sequentially, but because not all disk pages of the table are accessed, the overhead of this method is still less expensive than the overhead of a sequential scan. The reason why two-layer planning is used here is that the upper-layer planning nodes sort the physical locations of rows retrieved through the index first, which can minimize the overhead of reading disk pages separately. The "bitmap" mentioned in the node name is the mechanism for sorting.

Now we can also set the WHERE conditions more strictly, such as:
 

In this SQL, the data rows of the table are read in index order, which will make the overhead of reading them greater. However, in fact, the number of rows to be retrieved here is pitifully small, so there is no need to sort them in the physical position based on the rows.
Now we need to add another condition to the WHERE clause, such as:
 
The added filter condition stringu1 = 'xxx' only reduces the number of rows expected to be output, but does not reduce the actual overhead because we still need to access the same number of rows of data. This condition is not used as an index condition, but is regarded as a filter condition for the index result.
If multiple fields in the WHERE condition are indexed, the planner may use a combination of AND or OR of the index, such as:
 
Such a result will result in accessing two indexes. This method may not be better than using only one index and treating the other condition only as a filter.
Now let’s take a look at the query planning for table joining based on index fields, such as:
 
From the query planning, we can see that (Nested Loop) the query statement uses nested loops. The outer scan is a bitmap index, so its overhead is the same as the overhead of the row count and previous query, because the condition unique1 < 100 plays a role. At this time, the t1.unique2 = t2.unique2 condition clause has not had any effect yet, so it will not affect the row count of the outer scan. However, for inner layer scanning, the data rows of the current outer layer scanning will be inserted into the inner layer index scan and a similar condition t2.unique2 = constant is generated. So, inner layer scan will get the same plan and overhead as EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42. Finally, set the overhead of the loop node based on the overhead of the outer layer scan, plus an iteration of each outer layer row (106 * 3.01 here), and a little CPU time required for the connection processing.
If you do not want to use nested loops to plan the above query, then we can close the nested loop by executing the following system settings, such as:
 
This plan still tries to use the same index scan to take out 100 rows that meet the requirements from tenk1, store them in the hash (hash) table in memory, then do a full table-sequential scan of tenk2, and query the hash (hash) table for each record in tenk2, looking for rows that may match t1.unique2 = t2.unique2. Reading tenk1 and establishing a hash table is the entire startup overhead of this hash join, because we cannot get any output rows before we start reading tenk2.

In addition, we can also use the EXPLAIN ANALYZE command to check the accuracy of the planner's estimated value. This command will execute the query first, and then display the actual running time in each planning node, as well as the estimated overhead displayed by the simple EXPLAIN command, such as:
 

Note that the "actual time" value is calculated in milliseconds of real time, while the "cost" estimate is calculated in the number of disk page reads, so they are likely to be inconsistent. However, what we need to pay attention to is whether the ratios of the two sets of data are consistent.

In some query plans, a sub-planning node is likely to run multiple times. For example, in the previous nested loop plan, the inner index scan will be performed once for each outer row. In this case, "loops" will report the total number of executions of the node, while the actual time and number of rows displayed are the average of each execution. This is done to make these real values ​​more comparable to the values ​​expected to display. If you want to get the total amount of time spent on this node, the calculation is to multiply the value by the "loops" value.
The "Total runtime" displayed by EXPLAIN ANALYZE includes the time when the executor is started and closed, as well as the time when the result row is processed, but it does not include the time for analysis, rewriting or planning.
If the EXPLAIN command can only be used for testing environments and cannot be used for real environments, then it will be of no use. For example, if EXPLAIN is performed on a table with less data, it cannot be applied to large tables with a large number of numbers, because the overhead calculation of the planner is not linear, so it is likely to choose different plans for larger or smaller tables. An extreme example is a table that only occupies a single disk page, on which you almost always get sequential scan planning, regardless of whether it has an index or not. The planner knows that it will read a disk page in any case, so it is meaningless to add a few disk pages to find the index.

2. Batch data insertion:

There are several methods for optimizing batch insertion of data.

1. Turn off automatic submission:

When inserting data in batches, if each data is automatically submitted, when a system failure occurs in the middle, not only will the data consistency of this batch of inserts not only will it not be guaranteed, but the entire insertion efficiency will also be greatly affected due to multiple submission operations. The solution is to turn off the automatic submission of the system, and before the insertion starts, execute the begin transaction command displayed, and then execute the commit command to submit all the insertion operations after all the insertion operations are completed.
    
2. Use COPY:

Use COPY to load all records in one command instead of a series of INSERT commands. The COPY command is optimized for data lines with huge loads. It is not as flexible as the INSERT command, but when loading large amounts of data, the system overhead is also much less. Because COPY is a single command, there is no need to turn off automatic submission when filling the table.
    
3. Delete the index:

If you are loading a newly created table, the fastest way is to create the table, load it in batches with COPY, and then create any indexes the table needs. Because creating indexes on tables with existing data is faster than maintaining row by row. Of course, during the absence of index, the performance of other query operations related to the table will be affected to a certain extent, and the uniqueness constraints may be damaged.
    
4. Delete foreign key constraints:
Like indexes, checking foreign key constraints "batch" is more efficient than checking one row by one. Therefore, we can first delete the foreign key constraint, load the data, and then rebuild the constraint.
    
5. Increase maintenance_work_mem:
When loading a large amount of data, temporarily increasing the value of the maintenance_work_mem system variable can improve performance. This system parameter can improve the execution efficiency of the CREATE INDEX command and the ALTER TABLE ADD FOREIGN KEY command, but it will not have much impact on the COPY operation itself.
    
6. Increase checkpoint_segments:
Temporarily increasing the value of the checkpoint_segments system variable can also improve the efficiency of large-scale data loading. This is because when loading large amounts of data to PostgreSQL, checkpoint operations (declared by the system variable checkpoint_timeout) will occur more frequently than usual. At each checkpoint, all dirty data must be flushed to disk. By increasing the value of the checkpoint_segments variable, the number of checkpoints can be effectively reduced.
    
7. Run ANALYZE afterwards:
After adding or updating a large amount of data, the ANALYZE command should be run immediately, which ensures that the planner obtains the latest data statistics based on the table. In other words, if there are no statistics or the statistics are too old, the planner will likely choose a poor query plan, resulting in too inefficient query.