首页 » PostgreSQL/GaussDB » PostgreSQL 中的Join连接策略和性能
PostgreSQL 中的Join连接策略和性能
PostgreSQL 中有三种连接策略，它们的工作原理截然不同。如果 PostgreSQL 选择了错误的策略，查询性能可能会受到很大影响。本文介绍了连接策略、如何使用索引支持它们、它们可能出现的问题以及如何调整连接以获得更好的性能。
PostgreSQL 连接策略汇总表
| 嵌套循环连接 | 哈希连接 | 合并连接 | |
|---|---|---|---|
| 算法 | 对于每个外表行，扫描内表 | 从内部表构建哈希，扫描外部表，探测哈希 | 对表进行排序并合并行 |
| Index帮助 | 内部表连接键上的索引 | 没有任何 | 两个表的连接键上的索引 |
| 好的策略 | 外面的表很小 | 哈希表适合work_mem |
两张表都很大 |
对查询性能的影响
选择错误的连接策略会导致糟糕的性能：
- 如果优化器低估了行数，它可能会错误地选择嵌套循环连接。然后它会比预想的更频繁地扫描内部关系，从而导致性能不佳。
- 如果优化器高估了行数，它可能会错误地选择哈希或合并连接。然后它必须完全扫描两个关系，其性能可能比在内部表上使用索引的嵌套循环连接差得多。
在这两种情况下，错误的行数估计都是导致问题的原因。因此，虽然连接可能是我们花费大部分执行时间的地方，但问题的原因却是之前发生的错误估计。
如何让 PostgreSQL 选择正确的连接策略
找出最佳的连接策略（也许 PostgreSQL 无论如何都在做正确的事情）。您可以使用SET以下命令暂时禁用不同的连接策略，该命令会更改当前数据库会话中的参数：
|
1
2
3
|
SET enable_hashjoin = off;
SET enable_mergejoin = off;
SET enable_nestloop = off;
|
请注意，您无法真正禁用nestloop嵌套循环连接，只能阻止 PostgreSQL 使用它们。如果没有带运算符=的连接条件，则嵌套循环连接是唯一的方法。
调优查询通常不是一项简单、直接的任务。不过，这里有一些指导原则和想法：
- 如果由于错误估计而选择了错误的连接策略，请尝试改进该估计。
- 更新统计信息
- 用更简单的where改写SQL
- 尝试增加
work_mem并查看是否获得更便宜的哈希连接。 - 配置参数以告知 PostgreSQL 有关您的硬件和资源的信息,这将允许它正确地为索引扫描cost：
- 您可以使用仅索引扫描来加速嵌套循环和合并连接。为此，您必须将所有必需的列添加到索引中（最好使用
INCLUDE子句），并确保经常清理表。
Demo
下面看一例子。 环境 highgo v9.5(pg 14)
highgo=# create database anbob;
CREATE DATABASE
highgo=# \c anbob
IvorySQL-pro-3.2.0
You are now connected to database "anbob" as user "hg".
anbob=# CREATE TABLE t_gender (id int, name text);
CREATE TABLE
anbob=# INSERT INTO t_gender
anbob-# VALUES (1, 'male'), (2, 'female');
INSERT 0 2
anbob=# CREATE TABLE t_person (
anbob(# id serial,
anbob(# gender int,
anbob(# data char(40)
anbob(# );
CREATE TABLE
anbob=# INSERT INTO t_person (gender, data)
anbob-# SELECT x % 2 + 1, 'data'
anbob-# FROM generate_series(1, 5000000) AS x;
INSERT 0 5000000
anbob=# \timing on
Timing is on.
anbob=# SELECT name, count(*)
anbob-# FROM t_gender AS a, t_person AS b
anbob-# WHERE a.id = b.gender
anbob-# GROUP BY 1;
name | count
--------+---------
female | 2500000
male | 2500000
(2 rows)
Time: 1524.158 ms (00:01.524)
anbob=# explain SELECT name, count(*)
anbob-# FROM t_gender AS a, t_person AS b
anbob-# WHERE a.id = b.gender
anbob-# GROUP BY 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=266867.14..266917.81 rows=200 width=40)
Group Key: a.name
-> Gather Merge (cost=266867.14..266913.81 rows=400 width=40)
Workers Planned: 2
-> Sort (cost=265867.12..265867.62 rows=200 width=40)
Sort Key: a.name
-> Partial HashAggregate (cost=265857.47..265859.47 rows=200 width=40)
Group Key: a.name
-> Merge Join (cost=152454.04..238580.52 rows=5455391 width=32)
Merge Cond: (b.gender = a.id)
-> Sort (cost=152365.86..154513.66 rows=859117 width=4)
Sort Key: b.gender
-> Parallel Seq Scan on t_person b (cost=0.00..60138.17 rows=859117 width=4)
-> Sort (cost=88.17..91.35 rows=1270 width=36)
Sort Key: a.id
-> Seq Scan on t_gender a (cost=0.00..22.70 rows=1270 width=36)
(16 rows)
Time: 0.609 ms
anbob=# show work_mem;
work_mem
----------
4MB
(1 row)
anbob=# set work_mem='1GB';
SET
anbob=# explain SELECT name, count(*)
FROM t_gender AS a, t_person AS b
WHERE a.id = b.gender
GROUP BY 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=567135.08..567185.75 rows=200 width=40)
Group Key: a.name
-> Gather Merge (cost=567135.08..567181.75 rows=400 width=40)
Workers Planned: 2
-> Sort (cost=566135.06..566135.56 rows=200 width=40)
Sort Key: a.name
-> Partial HashAggregate (cost=566125.42..566127.42 rows=200 width=40)
Group Key: a.name
-> Merge Join (cost=291122.17..499978.80 rows=13229323 width=32)
Merge Cond: (b.gender = a.id)
-> Sort (cost=291033.99..296242.39 rows=2083358 width=4)
Sort Key: b.gender
-> Parallel Seq Scan on t_person b (cost=0.00..72380.58 rows=2083358 width=4)
-> Sort (cost=88.17..91.35 rows=1270 width=36)
Sort Key: a.id
-> Seq Scan on t_gender a (cost=0.00..22.70 rows=1270 width=36)
anbob=# ANALYZE T_PERSON;
ANALYZE
Time: 348.663 ms
anbob=# ANALYZE T_GENDER;
ANALYZE
Time: 1.150 ms
anbob=# explain SELECT name, count(*)
FROM t_gender AS a, t_person AS b
WHERE a.id = b.gender
GROUP BY 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=112443.43..112443.94 rows=2 width=14)
Group Key: a.name
-> Gather Merge (cost=112443.43..112443.90 rows=4 width=14)
Workers Planned: 2
-> Sort (cost=111443.41..111443.41 rows=2 width=14)
Sort Key: a.name
-> Partial HashAggregate (cost=111443.38..111443.40 rows=2 width=14)
Group Key: a.name
-> Hash Join (cost=1.04..101026.80 rows=2083316 width=6)
Hash Cond: (b.gender = a.id)
-> Parallel Seq Scan on t_person b (cost=0.00..72380.16 rows=2083316 width=4)
-> Hash (cost=1.02..1.02 rows=2 width=10)
-> Seq Scan on t_gender a (cost=0.00..1.02 rows=2 width=10)
(13 rows)
Time: 0.668 ms
anbob=# SELECT name, count(*)
FROM t_gender AS a, t_person AS b
WHERE a.id = b.gender
GROUP BY 1;
name | count
--------+---------
female | 2500000
male | 2500000
(2 rows)
Time: 856.007 ms
anbob=# set work_mem='4MB';
SET
Time: 0.195 ms
anbob=# explain SELECT name, count(*)
FROM t_gender AS a, t_person AS b
WHERE a.id = b.gender
GROUP BY 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=112443.43..112443.94 rows=2 width=14)
Group Key: a.name
-> Gather Merge (cost=112443.43..112443.90 rows=4 width=14)
Workers Planned: 2
-> Sort (cost=111443.41..111443.41 rows=2 width=14)
Sort Key: a.name
-> Partial HashAggregate (cost=111443.38..111443.40 rows=2 width=14)
Group Key: a.name
-> Hash Join (cost=1.04..101026.80 rows=2083316 width=6)
Hash Cond: (b.gender = a.id)
-> Parallel Seq Scan on t_person b (cost=0.00..72380.16 rows=2083316 width=4)
-> Hash (cost=1.02..1.02 rows=2 width=10)
-> Seq Scan on t_gender a (cost=0.00..1.02 rows=2 width=10)
(13 rows)
Time: 0.489 ms
anbob=# SELECT name, count(*)
FROM t_gender AS a, t_person AS b
WHERE a.id = b.gender
GROUP BY 1;
name | count
--------+---------
female | 2500000
male | 2500000
(2 rows)
Time: 857.734 ms
更新统计信息后从merge join改为hash join, 从1524.158 ms 提升到856.007 ms, work_mem在这里影响不大。
anbob=# CREATE INDEX IDX_T_PERSON ON T_PERSON(gender);
CREATE INDEX
Time: 4182.639 ms (00:04.183)
anbob=# VACUUM ANALYZE T_PERSON;
VACUUM
Time: 360.368 ms
anbob=# explain SELECT name, count(*)
FROM t_gender AS a, t_person AS b
WHERE a.id = b.gender
GROUP BY 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=92244.65..92245.16 rows=2 width=14)
Group Key: a.name
-> Gather Merge (cost=92244.65..92245.12 rows=4 width=14)
Workers Planned: 2
-> Sort (cost=91244.63..91244.64 rows=2 width=14)
Sort Key: a.name
-> Partial HashAggregate (cost=91244.60..91244.62 rows=2 width=14)
Group Key: a.name
-> Hash Join (cost=1.48..80828.02 rows=2083316 width=6)
Hash Cond: (b.gender = a.id)
-> Parallel Index Only Scan using idx_t_person on t_person b (cost=0.43..52181.38 rows=2083316 width=4)
-> Hash (cost=1.02..1.02 rows=2 width=10)
-> Seq Scan on t_gender a (cost=0.00..1.02 rows=2 width=10)
(13 rows)
Time: 0.678 ms
anbob=# SELECT name, count(*)
FROM t_gender AS a, t_person AS b
WHERE a.id = b.gender
GROUP BY 1;
name | count
--------+---------
female | 2500000
male | 2500000
(2 rows)
Time: 741.560 ms
Note:
创建了索引，在hash join中 index only scan， 从原来857.734 ms提升到741.560 ms。
anbob=# set max_parallel_workers=1;
SET
anbob=# explain SELECT name, count(*)
FROM t_gender AS a, t_person AS b
WHERE a.id = b.gender
GROUP BY 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=92244.65..92245.16 rows=2 width=14)
Group Key: a.name
-> Gather Merge (cost=92244.65..92245.12 rows=4 width=14)
Workers Planned: 2
-> Sort (cost=91244.63..91244.64 rows=2 width=14)
Sort Key: a.name
-> Partial HashAggregate (cost=91244.60..91244.62 rows=2 width=14)
Group Key: a.name
-> Hash Join (cost=1.48..80828.02 rows=2083316 width=6)
Hash Cond: (b.gender = a.id)
-> Parallel Index Only Scan using idx_t_person on t_person b (cost=0.43..52181.38 rows=2083316 width=4)
-> Hash (cost=1.02..1.02 rows=2 width=10)
-> Seq Scan on t_gender a (cost=0.00..1.02 rows=2 width=10)
(13 rows)
anbob=# SELECT name, count(*)
FROM t_gender AS a, t_person AS b
WHERE a.id = b.gender
GROUP BY 1;
name | count
--------+---------
female | 2500000
male | 2500000
(2 rows)
Time: 1095.960 ms (00:01.096)
Note：
调整了max_parallel_workers为1,执行显示没变，但是速度从741.560 ms 降到1095.960 ms
anbob=# explain (analyze,buffers)WITH x AS ( SELECT gender, count(*) AS res FROM t_person AS a GROUP BY 1 ) SELECT name, res FROM x, t_gender AS y WHERE x.gender = y.id; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------ Merge Join (cost=1001.49..63599.59 rows=2 width=14) (actual time=560.903..568.020 rows=2 loops=1) Merge Cond: (a.gender = y.id) Buffers: shared hit=4220 -> Finalize GroupAggregate (cost=1000.46..63598.51 rows=2 width=12) (actual time=560.872..567.987 rows=2 loops=1) Group Key: a.gender Buffers: shared hit=4219 -> Gather Merge (cost=1000.46..63598.47 rows=4 width=12) (actual time=282.994..567.974 rows=4 loops=1) Workers Planned: 2 Workers Launched: 1 Buffers: shared hit=4219 -> Partial GroupAggregate (cost=0.43..62597.98 rows=2 width=12) (actual time=280.041..557.930 rows=2 loops=2) Group Key: a.gender Buffers: shared hit=4219 -> Parallel Index Only Scan using idx_t_person on t_person a (cost=0.43..52181.38 rows=2083316 width=4) (actual time=0.045..295.435 rows =2500000 loops=2) Heap Fetches: 0 Buffers: shared hit=4219 -> Sort (cost=1.03..1.03 rows=2 width=10) (actual time=0.026..0.027 rows=2 loops=1) Sort Key: y.id Sort Method: quicksort Memory: 25kB Buffers: shared hit=1 -> Seq Scan on t_gender y (cost=0.00..1.02 rows=2 width=10) (actual time=0.012..0.013 rows=2 loops=1) Buffers: shared hit=1 Planning Time: 0.155 ms Execution Time: 568.077 ms (24 rows) anbob=# WITH x AS anbob-# ( anbob(# SELECT gender, count(*) AS res anbob(# FROM t_person AS a anbob(# GROUP BY 1 anbob(# ) anbob-# SELECT name, res anbob-# FROM x, t_gender AS y anbob-# WHERE x.gender = y.id; name | res --------+--------- male | 2500000 female | 2500000 (2 rows) Time: 335.716 ms
Note:
改为with 先做分组后再与其它表关连，速度提升到335.716 ms。目前看是最快的。
anbob=# drop index idx_t_person;
DROP INDEX
Time: 8.404 ms
anbob=# explain WITH x AS
(
SELECT gender, count(*) AS res
FROM t_person AS a
GROUP BY 1
)
SELECT name, res
FROM x, t_gender AS y
WHERE x.gender = y.id;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Hash Join (cost=83797.34..83798.38 rows=2 width=14)
Hash Cond: (y.id = x.gender)
-> Seq Scan on t_gender y (cost=0.00..1.02 rows=2 width=10)
-> Hash (cost=83797.32..83797.32 rows=2 width=12)
-> Subquery Scan on x (cost=83796.79..83797.32 rows=2 width=12)
-> Finalize GroupAggregate (cost=83796.79..83797.30 rows=2 width=12)
Group Key: a.gender
-> Gather Merge (cost=83796.79..83797.26 rows=4 width=12)
Workers Planned: 2
-> Sort (cost=82796.77..82796.77 rows=2 width=12)
Sort Key: a.gender
-> Partial HashAggregate (cost=82796.74..82796.76 rows=2 width=12)
Group Key: a.gender
-> Parallel Seq Scan on t_person a (cost=0.00..72380.16 rows=2083316 width=4)
(14 rows)
Time: 0.739 ms
anbob=# WITH x AS
(
SELECT gender, count(*) AS res
FROM t_person AS a
GROUP BY 1
)
SELECT name, res
FROM x, t_gender AS y
WHERE x.gender = y.id;
name | res
--------+---------
male | 2500000
female | 2500000
(2 rows)
Time: 699.955 ms
Note:
删掉索引后，with CTE的写法速度在699.955 ms。
anbob=# show max_parallel_workers
anbob-# ;
max_parallel_workers
----------------------
8
(1 row)
anbob=# show enable_parallel_hash;
enable_parallel_hash
----------------------
on
(1 row)
anbob=# show work_mem;
work_mem
----------
4MB
(1 row)
anbob=# explain (analyze,buffers,verbose) WITH x AS
(
SELECT gender, count(*) AS res
FROM t_person AS a
GROUP BY 1
)
SELECT name, res
FROM x, t_gender AS y
WHERE x.gender = y.id;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=83797.34..83798.38 rows=2 width=14) (actual time=606.555..611.253 rows=2 loops=1)
Output: y.name, x.res
Inner Unique: true
Hash Cond: (y.id = x.gender)
Buffers: shared hit=11306 read=40256
I/O Timings: read=236.557
-> Seq Scan on public.t_gender y (cost=0.00..1.02 rows=2 width=10) (actual time=0.015..0.017 rows=2 loops=1)
Output: y.id, y.name
Buffers: shared hit=1
-> Hash (cost=83797.32..83797.32 rows=2 width=12) (actual time=606.515..611.208 rows=2 loops=1)
Output: x.res, x.gender
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=11305 read=40256
I/O Timings: read=236.557
-> Subquery Scan on x (cost=83796.79..83797.32 rows=2 width=12) (actual time=606.488..611.184 rows=2 loops=1)
Output: x.res, x.gender
Buffers: shared hit=11305 read=40256
I/O Timings: read=236.557
-> Finalize GroupAggregate (cost=83796.79..83797.30 rows=2 width=12) (actual time=606.486..611.181 rows=2 loops=1)
Output: a.gender, count(*)
Group Key: a.gender
Buffers: shared hit=11305 read=40256
I/O Timings: read=236.557
-> Gather Merge (cost=83796.79..83797.26 rows=4 width=12) (actual time=606.469..611.164 rows=6 loops=1)
Output: a.gender, (PARTIAL count(*))
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=11305 read=40256
I/O Timings: read=236.557
-> Sort (cost=82796.77..82796.77 rows=2 width=12) (actual time=603.188..603.189 rows=2 loops=3)
Output: a.gender, (PARTIAL count(*))
Sort Key: a.gender
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=11305 read=40256
I/O Timings: read=236.557
Worker 0: actual time=601.816..601.816 rows=2 loops=1
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=3992 read=13984
I/O Timings: read=67.126
Worker 1: actual time=601.810..601.811 rows=2 loops=1
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=3933 read=13877
I/O Timings: read=67.367
-> Partial HashAggregate (cost=82796.74..82796.76 rows=2 width=12) (actual time=603.159..603.160 rows=2 loops=3)
Output: a.gender, PARTIAL count(*)
Group Key: a.gender
Batches: 1 Memory Usage: 24kB
Buffers: shared hit=11291 read=40256
I/O Timings: read=236.557
Worker 0: actual time=601.787..601.787 rows=2 loops=1
Batches: 1 Memory Usage: 24kB
Buffers: shared hit=3985 read=13984
I/O Timings: read=67.126
Worker 1: actual time=601.779..601.780 rows=2 loops=1
Batches: 1 Memory Usage: 24kB
Buffers: shared hit=3926 read=13877
I/O Timings: read=67.367
-> Parallel Seq Scan on public.t_person a (cost=0.00..72380.16 rows=2083316 width=4) (actual time=0.031..229.561 rows=1666667 loops=3)
Output: a.id, a.gender, a.data
Buffers: shared hit=11291 read=40256
I/O Timings: read=236.557
Worker 0: actual time=0.018..219.747 rows=1742993 loops=1
Buffers: shared hit=3985 read=13984
I/O Timings: read=67.126
Worker 1: actual time=0.048..219.240 rows=1726832 loops=1
Buffers: shared hit=3926 read=13877
I/O Timings: read=67.367
Planning:
Buffers: shared hit=60
Planning Time: 0.634 ms
Execution Time: 611.478 ms
(71 rows)
anbob=# WITH x AS
(
SELECT gender, count(*) AS res
FROM t_person AS a
GROUP BY 1
)
SELECT name, res
FROM x, t_gender AS y
WHERE x.gender = y.id;
name | res
--------+---------
male | 2500000
female | 2500000
(2 rows)
Time: 472.620 ms
References
https://www.cybertec-postgresql.com/en/join-strategies-and-performance-in-postgresql/
微信扫一扫，打赏作者吧～
目前这篇文章还没有评论(Rss)