Tuning query performance in PostgreSQL

Tuning database performance - developers usually either love it or hate it. I enjoy this and want to share some of the methods that I have used recently to tune poorly executed queries in PostgreSQL. My methods are not exhaustive, but rather a textbook for those who just trudge about tuning.

Search for slow queries

The first obvious way to start tuning is to find specific operators that work poorly.


The pg_stats_statements module is a great place to start. It simply keeps track of SQL statement execution statistics and can be an easy way to find inefficient queries.

Once you have installed this module, a system view called pg_stat_statements will be available with all its properties. Once he has the opportunity to collect enough data, look for queries that have a relatively high total_time value . Focus on these operators first.

SELECT * FROM pg_stat_statements ORDER BY total_time DESC; 

16384163852948SELECT address_1 FROM addresses a INNER JOIN people p ON a.person_id = p.id WHERE a.state = @state_abbrev;3948315224.670
1638416385924SELECT person_id FROM people WHERE name = name ;2648312225.670
1638416385395SELECT _ FROM orders WHERE EXISTS (select _ from products where is_featured = true)18583224.67


The auto_explain module is also useful for finding slow queries, but it has 2 obvious advantages: it registers the actual execution plan and supports recording nested statements using the log_nested_statements option. Nested statements are those statements that are executed inside a function. If your application uses many features, auto_explain is invaluable for getting detailed execution plans.

The log_min_duration option controls which query execution plans are logged based on how long they run. For example, if you set the value to 1000, all records that take longer than 1 second will be registered.

Index Tuning

Another important tuning strategy is to ensure that indexes are used correctly. As a prerequisite, we need to include the Statistics Collector.

Postgres Statistics Collector is a first-class subsystem that collects all kinds of useful performance statistics.

By enabling this collector, you get tons of pg_stat _... views that contain all the properties. In particular, I found this to be especially useful for finding missing and unused indexes.

Missing Indexes

Missing indexes may be one of the easiest solutions to improve query performance. However, they are not a silver bullet and should be used correctly (more on this later). If you have statistics collector enabled, you can run the following query ( source ).

 SELECT relname, seq_scan - idx_scan AS too_much_seq, CASE WHEN seq_scan - coalesce(idx_scan, 0) > 0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan FROM pg_stat_all_tables WHERE schemaname = 'public' AND pg_relation_size(relname::regclass) > 80000 ORDER BY too_much_seq DESC; 

The query finds tables that have more sequential scans (Index scans) than index scans - a clear indication that the index will help. This will not tell you which columns to create the index on, so it will take a bit more work. However, knowing which tables need them is a good first step.

Unused Indexes

Index all entities, right? Did you know that unused indexes can adversely affect write performance? The reason is that when creating an index, Postgres is burdened with the task of updating this index after write operations (INSERT / UPDATE / DELETE). Thus, adding an index is a balancing act, because it can speed up the reading of data (if it is created correctly), but it will slow down write operations. To find unused indexes, you can run the following query.

 SELECT indexrelid::regclass as index, relid::regclass as table, 'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan = 0 AND indisunique is false; 

Note about development environment statistics

Relying on statistics from a local development database can be problematic. Ideally, you can get the above statistics from your working machine or generate it from a restored working backup. What for? Environmental factors can change the behavior of the Postgres query optimizer. Two examples:

Understanding Execution Plans

Now that you've found a few slow queries, it's time to start the fun.


The EXPLAIN command is certainly required when setting up queries. He tells you what is really happening. To use it, just add EXPLAIN to the query and run it. PostgreSQL will show you the execution plan that it used.

When using EXPLAIN for tuning, I recommend that you always use the ANALYZE option ( EXPLAIN ANALYZE ), as it gives you more accurate results. The ANALYZE option actually executes the statement (rather than just evaluating it), and then explains it.

Let's take a dip and begin to understand the output of EXPLAIN . Here is an example:


The first thing to understand is that each block indented with the previous “->” (along with the top line) is called a node. A node is a logical unit of work (a “step”, if you like) with associated cost and lead time. The cost and time presented on each node are cumulative and bring together all the child nodes. This means that the topmost line (node) shows the total cost and the actual time for the entire operator. This is important because you can easily drill down to determine which nodes are the bottleneck.



The first number is the initial cost (the cost of obtaining the first record), and the second number is the cost of processing the entire node (total cost from start to finish).

In fact, this is the cost that PostgreSQL estimates will have to be met in order to execute the statement. This number does not mean how long it will take to complete the request, although usually there is a direct relationship needed to complete. Cost is a combination of 5 working components used to evaluate the required work: sequential sampling, inconsistent (random) sampling, row processing, processing operator (function) and recording the processing index. Cost is the input / output and processor load, and it is important to know that the relatively high cost means that PostgresSQL believes that it will have to do more work. The optimizer decides which execution plan to use based on cost. The optimizer prefers lower costs.

Actual time

 actual time=55.009..55.012 

In milliseconds, the first number is the start time (time to retrieve the first record), and the second number is the time required to process the entire node (total time from start to finish). Easy to understand, right?

In the above example, it took 55.009 ms to get the first record and 55.012 ms to complete the entire node.

Learn more about execution plans.

There are some really good articles for understanding EXPLAIN results. Instead of trying to retell them here, I recommend taking the time to really understand them by going to these 2 wonderful resources:

Request tuning

Now that you know which operators are working poorly and can see your execution plans, it's time to start tuning your query to improve performance. Here you make changes to your queries and / or add indexes to try to get a better execution plan. Start with bottlenecks and see if there are any changes you can make to reduce costs and / or lead time.

Data Cache and Cost Note

When making changes and evaluating implementation plans, in order to see if there will be improvements, it is important to know that future implementations may depend on caching data that gives an idea of ​​the best results. If you run the request once, make a correction and run it a second time, most likely it will run much faster, even if the execution plan is not more favorable. This is because PostgreSQL could cache the data used on the first start, and can use it on the second start. Therefore, you must complete the queries at least 3 times and average the results to compare costs.

Things I learned can help improve execution plans:

Source: https://habr.com/ru/post/461997/

All Articles