Speeding up queries without rewriting them
Often, you either can’t or don’t want to rewrite a query. However, you can still try and speed it up through any of the techniques we will discuss here.
How to do it…
By now, we assume that you’ve looked at various problems already, so the following are more advanced ideas for you to try.
Increasing work_mem
For queries involving large sorts or for join queries, it may be useful to increase the amount of working memory that can be used for query execution. Try setting the following:
SET work_mem = '1TB';
Then, run EXPLAIN (not EXPLAIN ANALYZE). If EXPLAIN changes for the query, then it may benefit from more memory. I’m guessing that you don’t have access to 1 terabyte (TB) of RAM; the previous setting was only used to prove that the query plan is dependent on available memory. Now, issue the following command:
RESET work_mem;
Now, choose a more appropriate value for production use, such as the following:
SET work_mem = '128MB';
Remember to increase maintenace_work_mem when creating indexes or adding foreign keys (FKs), rather than work_mem.
Setting recursive_worktable_factor
The recursive_worktable_factor parameter plays a pivotal role in enhancing the performance of recursive queries. It aids the query planner by providing a refined estimate of the working table’s size.
A misjudgment in estimating the working table’s size can cause the planner to opt for a less efficient query plan, hampering performance. Specifically:
- Underestimating the size might lead the planner to choose a plan demanding multiple data passes or utilize an inadequately sized temporary table
- Overestimating the size can make the planner opt for an unnecessarily intricate and costly plan, like using an oversized temporary table or employing a less efficient algorithm for joining the working table with other tables in the query
By correctly adjusting the recursive_worktable_factor parameter, you equip the planner to select the most efficient query plan for your recursive query. Such adjustments can bring about substantial performance boosts, particularly for intricate queries or those handling vast data quantities.
Below is an example of how setting the recursive_worktable_factor parameter can improve the performance of a recursive query:
postgres=# SET recursive_worktable_factor = 1;
SET
postgres=# EXPLAIN ANALYZE WITH RECURSIVE direct_reports (id, manager_id) AS (
SELECT id, manager_id
FROM employees
WHERE manager_id = 3
UNION ALL
SELECT e.id, e.manager_id
FROM employees e
JOIN direct_reports dr ON e.manager_id = dr.id
)
SELECT id
FROM direct_reports
ORDER BY id;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=31027062111.26..31277447865.09 rows=100154301533 width=4) (actual time=11828.285..12404.234 rows=1000001 loops=1)
Sort Key: direct_reports.id
Sort Method: external merge Disk: 11768kB
CTE direct_reports
-> Recursive Union (cost=0.00..2508283266.29 rows=100154301533 width=8) (actual time=85.956..10062.950 rows=1000001 loops=1)
-> Seq Scan on employees (cost=0.00..37739.00 rows=1001533 width=8) (actual time=85.954..722.830 rows=1000001 loops=1)
Filter: (manager_id = 3)
Rows Removed by Filter: 999999
-> Merge Join (cost=430293.53..150670251.20 rows=10015330000 width=8) (actual time=8200.796..8200.804 rows=0 loops=1)
Merge Cond: (dr.id = e.manager_id)
-> Sort (cost=133547.85..136051.68 rows=1001533 width=4) (actual time=1173.078..1173.081 rows=1 loops=1)
Sort Key: dr.id
Sort Method: external merge Disk: 11768kB
-> WorkTable Scan on direct_reports dr (cost=0.00..20030.66 rows=1001533 width=4) (actual time=17.385..614.639 rows=1000001 loops=1)
-> Materialize (cost=296745.69..306745.69 rows=2000000 width=8) (actual time=2499.394..5884.538 rows=1999999 loops=1)
-> Sort (cost=296745.69..301745.69 rows=2000000 width=8) (actual time=2499.389..3695.102 rows=1999999 loops=1)
Sort Key: e.manager_id
Sort Method: external merge Disk: 35248kB
-> Seq Scan on employees e (cost=0.00..32739.00 rows=2000000 width=8) (actual time=0.019..1152.668 rows=2000000 loops=1)
-> CTE Scan on direct_reports (cost=0.00..2003086030.66 rows=100154301533 width=4) (actual time=85.963..11268.729 rows=1000001 loops=1)
Planning Time: 0.133 ms
JIT:
Functions: 15
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.714 ms, Inlining 4.864 ms, Optimization 59.807 ms, Emission 38.585 ms, Total 103.970 ms
Execution Time: 12934.389 ms
(26 rows)
postgres=# SET recursive_worktable_factor = 10;
SET
postgres=# EXPLAIN ANALYZE WITH RECURSIVE direct_reports (id, manager_id) AS (
SELECT id, manager_id
FROM employees
WHERE manager_id = 3
UNION ALL
SELECT e.id, e.manager_id
FROM employees e
JOIN direct_reports dr ON e.manager_id = dr.id
)
SELECT id
FROM direct_reports
ORDER BY id;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=326878354279.47..329382189283.30 rows=1001534001533 width=4) (actual time=9567.752..10143.355 rows=1000001 loops=1)
Sort Key: direct_reports.id
Sort Method: external merge Disk: 11768kB
CTE direct_reports
-> Recursive Union (cost=0.00..25058077949.25 rows=1001534001533 width=8) (actual time=97.913..7801.959 rows=1000001 loops=1)
-> Seq Scan on employees (cost=0.00..37739.00 rows=1001533 width=8) (actual time=97.910..735.372 rows=1000001 loops=1)
Filter: (manager_id = 3)
Rows Removed by Filter: 999999
-> Merge Join (cost=1935481.17..1504270019.49 rows=100153300000 width=8) (actual time=5926.530..5926.537 rows=0 loops=1)
Merge Cond: (e.manager_id = dr.id)
-> Sort (cost=296745.69..301745.69 rows=2000000 width=8) (actual time=2501.454..3668.316 rows=1999999 loops=1)
Sort Key: e.manager_id
Sort Method: external merge Disk: 35248kB
-> Seq Scan on employees e (cost=0.00..32739.00 rows=2000000 width=8) (actual time=0.031..1152.541 rows=2000000 loops=1)
-> Materialize (cost=1638735.48..1688812.13 rows=10015330 width=4) (actual time=1169.809..1169.813 rows=1 loops=1)
-> Sort (cost=1638735.48..1663773.81 rows=10015330 width=4) (actual time=1169.803..1169.805 rows=1 loops=1)
Sort Key: dr.id
Sort Method: external merge Disk: 11768kB
-> WorkTable Scan on direct_reports dr (cost=0.00..200306.60 rows=10015330 width=4) (actual time=17.372..614.035 rows=1000001 loops=1)
-> CTE Scan on direct_reports (cost=0.00..20030680030.66 rows=1001534001533 width=4) (actual time=97.921..9007.778 rows=1000001 loops=1)
Planning Time: 0.221 ms
JIT:
Functions: 15
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.948 ms, Inlining 5.361 ms, Optimization 68.912 ms, Emission 40.936 ms, Total 116.157 ms
Execution Time: 10673.570 ms
(26 rows)
More ideas with indexes
Try to add a multicolumn index that is specifically tuned for that query.
If you have a query that, for example, selects rows from the t1 table on the a column and sorts on the b column, then creating the following index enables PostgreSQL to do it all in one index scan:
CREATE INDEX t1_a_b_idx ON t1(a, b);
PostgreSQL 9.2 introduced a new plan type: index-only scans. This allows you to utilize a technique known as covering indexes. If all of the columns requested by the SELECT list of a query are available in an index, that particular index is a covering index for that query. This technique allows PostgreSQL to fetch valid rows directly from the index, without accessing the table (heap), so performance improves significantly. If the index is non-unique, you can just add columns onto the end of the index, like so – however, please be aware that this only works for non-unique indexes:
CREATE INDEX t1_a_b_c_idx ON t1(a, b, c);
PostgreSQL 11+ provides syntax to identify covering index columns in a way that works for both unique and non-unique indexes, like this:
CREATE INDEX t1_a_b_cov_idx ON t1(a, b) INCLUDE (c);
Another often underestimated (or unknown) feature of PostgreSQL is partial indexes. If you use SELECT on a condition, especially if this condition only selects a small number of rows, you can use a conditional index on that expression, like this:
CREATE INDEX t1_proc_ndx ON t1(i1)
WHERE needs_processing = TRUE;
The index will be used by queries that have a WHERE clause that includes the index clause, like so:
SELECT id, ... WHERE needs_processing AND i1 = 5;
There are many types of indexes in PostgreSQL so you may find that there are multiple types of indexes that can be used for a particular task and many options to choose from:
- ID data:
BTREEandHASH - Categorical data:
BTREE - Text data:
GISTandGIN - JSONB or XML data:
GIN, plus selective use ofbtree - Time-range data:
BRIN(and partitioning) - Geographical data:
GIST,SP-GIST, andBRIN
Performance gains in Postgres can also be obtained with another technique: clustering tables on specific indexes. However, index access may still not be very efficient if the values that are accessed by the index are distributed randomly, all over the table. If you know that some fields are likely to be accessed together, then cluster the table on an index defined on those fields. For a multicolumn index, you can use the following command:
CLUSTER t1_a_b_ndx ON t1;
Clustering a table on an index rewrites the whole table in index order. This can lock the table for a long time, so don’t do it on a busy system. Also, CLUSTER is a one-time command. New rows do not get inserted in cluster order, and to keep the performance gains, you may need to cluster the table every now and then.
Once a table has been clustered on an index, you don’t need to specify the index name in any cluster commands that follow. It is enough to type this:
CLUSTER t1;
It still takes time to rewrite the entire table, though it is probably a little faster once most of the table is in index order.
There’s more…
We will complete this recipe by listing four examples of query performance issues that can be addressed with a specific solution.
Time-series partitioning
Refer to the Creating time-series tables recipe for more information on this.
Using a view that contains TABLESAMPLE
Where some queries access a table, replace that with a view that retrieves fewer rows using a TABLESAMPLE clause. In this example, we are using a sampling method that produces a sample of the table using a scan lasting no longer than 5 seconds; if the table is small enough, the answer is exact; otherwise, progressive sampling is used to ensure that we meet our time objective:
CREATE EXTENSION tsm_system_time;
CREATE SCHEMA fast_access_schema;
CREATE VIEW fast_access_schema.tablename AS
SELECT *
FROM data_schema.tablename TABLESAMPLE system_time(5000); --5 secs
SET search_path = 'fast_access_schema, data_schema';
So, the application can use the new table without changing the SQL. Be careful, as some answers can change when you’re accessing fewer rows (for example, sum()), making this particular idea somewhat restricted; the overall idea of using views is still useful.
In case of many updates, set fillfactor on the table
If you often update only some tables and can arrange your query/queries so that you don’t change any indexed fields, then setting fillfactor to a lower value than the default of 100 for those tables enables PostgreSQL to use heap-only tuples (HOT) updates, which can be an order of magnitude (OOM) faster than ordinary updates. HOT updates not only avoid creating new index entries but can also perform a fast mini-vacuum inside the page to make room for new rows:
ALTER TABLE t1 SET (fillfactor = 70);
This tells PostgreSQL to fill only 70% of each page in the t1 table when performing insertions so that 30% is left for use by in-page (HOT) updates.
Rewriting the schema – a more radical approach
In some cases, it may make sense to rewrite the database schema and provide an old view for unchanged queries using views, triggers, rules, and functions.
One such case occurs when refactoring the database, and you would want old queries to keep running while changes are made.
Another case is an external application that is unusable with the provided schema but can be made to perform OK with a different distribution of data between tables.
