分析PostgreSQL中的大表连接
短信预约 -IT技能 免费直播动态提醒
这篇文章主要介绍“分析PostgreSQL中的大表连接”，在日常操作中，相信很多人在分析PostgreSQL中的大表连接问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”分析PostgreSQL中的大表连接”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
数据库配置
主机CPU 4核，内存4G，PG共享缓存128MB，work_mem 4MB。
测试数据
创建4张表，每张表1000w行，数据量约1G，是PG共享内存的8倍。
drop table t_big_1;
drop table t_big_2;
drop table t_big_3;
drop table t_big_4;
create table t_big_1(id int,c1 varchar(30),c2 varchar(30),c3 varchar(30));
create table t_big_2(id int,c1 varchar(30),c2 varchar(30),c3 varchar(30));
create table t_big_3(id int,c1 varchar(30),c2 varchar(30),c3 varchar(30));
create table t_big_4(id int,c1 varchar(30),c2 varchar(30),c3 varchar(30));
insert into t_big_1 select x,rpad('c1'||x,30,'c1'),rpad('c2'||x,30,'c2'),rpad('c3'||x,30,'c3') from generate_series(1,10000000) as x;
insert into t_big_2 select x,rpad('c1'||x,30,'c1'),rpad('c2'||x,30,'c2'),rpad('c3'||x,30,'c3') from generate_series(1,10000000) as x;
insert into t_big_3 select x,rpad('c1'||x,30,'c1'),rpad('c2'||x,30,'c2'),rpad('c3'||x,30,'c3') from generate_series(1,10000000) as x;
insert into t_big_4 select x,rpad('c1'||x,30,'c1'),rpad('c2'||x,30,'c2'),rpad('c3'||x,30,'c3') from generate_series(1,10000000) as x;
show shared_buffers;
show effective_cache_size;
show work_mem;
select pg_size_pretty(pg_table_size('t_big_1'));
select pg_size_pretty(pg_table_size('t_big_2'));
select pg_size_pretty(pg_table_size('t_big_3'));
select pg_size_pretty(pg_table_size('t_big_4'));
analyze t_big_1,t_big_2,t_big_3,t_big_4;
explain verbose
select a.*
from t_big_1 a join t_big_2 b on a.c1 = b.c1;
explain verbose
select a.id,b.c1,c.c2,d.c3
from t_big_1 a,t_big_2 b,t_big_3 c,t_big_4 d
where a.id = b.id and b.id = c.id and c.id = d.id;
explain verbose
select a.id,b.c1,c.c2,d.c3
from t_big_1 a,t_big_2 b,t_big_3 c,t_big_4 d
where a.id = b.id and b.c1 = c.c1 and c.c2 = d.c2;大表连接
未分析数据表前
[local:/data/run/pg12]:5120 pg12@testdb=# explain verbose
pg12@testdb-# select a.id,b.c1,c.c2,d.c3
pg12@testdb-# from t_big_1 a,t_big_2 b,t_big_3 c,t_big_4 d
pg12@testdb-# where a.id = b.id and b.c1 = c.c1 and c.c2 = d.c2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Merge Join (cost=164722831406.26..1096915306139605248.00 rows=73127676034285903872 width=238)
Output: a.id, b.c1, c.c2, d.c3
Merge Cond: ((b.c1)::text = (c.c1)::text)
-> Sort (cost=58799667920.13..59102008117.66 rows=120936079012 width=82)
Output: a.id, b.c1
Sort Key: b.c1
-> Merge Join (cost=2124653.55..1816202724.10 rows=120936079012 width=82)
Output: a.id, b.c1
Merge Cond: (a.id = b.id)
-> Sort (cost=894232.27..906527.40 rows=4918050 width=4)
Output: a.id
Sort Key: a.id
-> Seq Scan on public.t_big_1 a (cost=0.00..213115.50 rows=4918050 width=4)
Output: a.id
-> Materialize (cost=1230421.27..1255011.52 rows=4918050 width=82)
Output: b.c1, b.id
-> Sort (cost=1230421.27..1242716.40 rows=4918050 width=82)
Output: b.c1, b.id
Sort Key: b.id
-> Seq Scan on public.t_big_2 b (cost=0.00..213115.50 rows=4918050 width=82)
Output: b.c1, b.id
-> Materialize (cost=105923163486.13..106527843881.19 rows=120936079012 width=234)
Output: c.c2, c.c1, d.c3
-> Sort (cost=105923163486.13..106225503683.66 rows=120936079012 width=234)
Output: c.c2, c.c1, d.c3
Sort Key: c.c1
-> Merge Join (cost=3066006.55..1817144077.10 rows=120936079012 width=234)
Output: c.c2, c.c1, d.c3
Merge Cond: ((c.c2)::text = (d.c2)::text)
-> Sort (cost=1533003.27..1545298.40 rows=4918050 width=156)
Output: c.c2, c.c1
Sort Key: c.c2
-> Seq Scan on public.t_big_3 c (cost=0.00..213115.50 rows=4918050 width=156)
Output: c.c2, c.c1
-> Materialize (cost=1533003.27..1557593.52 rows=4918050 width=156)
Output: d.c3, d.c2
-> Sort (cost=1533003.27..1545298.40 rows=4918050 width=156)
Output: d.c3, d.c2
Sort Key: d.c2
-> Seq Scan on public.t_big_4 d (cost=0.00..213115.50 rows=4918050 width=156)
Output: d.c3, d.c2
(41 rows)可以看到，未分析前，执行计划使用merge join，计划的cost是一个大数。
执行分析后
[local:/data/run/pg12]:5120 pg12@testdb=# explain (analyze,buffers,verbose)
select a.id,b.c1,c.c2,d.c3
from t_big_1 a,t_big_2 b,t_big_3 c,t_big_4 d
where a.id = b.id and b.c1 = c.c1 and c.c2 = d.c2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=896126.19..2564935.91 rows=9999844 width=97) (actual time=393803.655..404902.025 rows=10000000 loops=1)
Output: a.id, b.c1, c.c2, d.c3
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=266 read=655676 dirtied=486717 written=486414, temp read=473954 written=486068
-> Parallel Hash Join (cost=895126.19..1563951.51 rows=4166602 width=97) (actual time=393672.896..398825.027 rows=3333333 loops=3)
Output: a.id, b.c1, c.c2, d.c3
Hash Cond: ((c.c2)::text = (d.c2)::text)
Buffers: shared hit=266 read=655676 dirtied=486717 written=486414, temp read=473954 written=486068
Worker 0: actual time=393629.565..399028.498 rows=3549817 loops=1
Buffers: shared hit=118 read=218079 dirtied=161599 written=161495, temp read=162307 written=161880
Worker 1: actual time=393585.994..399049.295 rows=3609509 loops=1
Buffers: shared hit=119 read=217313 dirtied=161014 written=160913, temp read=163324 written=160736
-> Parallel Hash Join (cost=592683.65..1070481.02 rows=4166681 width=66) (actual time=328335.871..378143.916 rows=3333333 loops=3)
Output: a.id, b.c1, c.c2
Hash Cond: ((b.c1)::text = (c.c1)::text)
Buffers: shared hit=63 read=491773 dirtied=352782 written=352575, temp read=267125 written=274312
Worker 0: actual time=328475.430..378240.528 rows=3325497 loops=1
Buffers: shared hit=25 read=164024 dirtied=117445 written=117373, temp read=88941 written=91448
Worker 1: actual time=328084.038..377943.176 rows=3311112 loops=1
Buffers: shared hit=29 read=163900 dirtied=117550 written=117481, temp read=88747 written=91320
-> Parallel Hash Join (cost=290238.33..609558.42 rows=4166681 width=35) (actual time=158380.042..198763.345 rows=3333333 loops=3)
Output: a.id, b.c1
Hash Cond: (a.id = b.id)
Buffers: shared hit=63 read=327838 dirtied=218847 written=218710, temp read=98317 written=100856
Worker 0: actual time=158518.764..199077.411 rows=3331104 loops=1
Buffers: shared hit=25 read=109394 dirtied=72893 written=72845, temp read=32790 written=33668
Worker 1: actual time=158520.409..198920.394 rows=3332824 loops=1
Buffers: shared hit=29 read=109323 dirtied=73002 written=72956, temp read=32934 written=33560
-> Parallel Seq Scan on public.t_big_1 a (cost=0.00..205601.81 rows=4166681 width=4) (actual time=239.830..75704.152 rows=3333333 loops=3)
Output: a.id
Buffers: shared read=163935 dirtied=109449 written=109391
Worker 0: actual time=239.584..75677.703 rows=3327794 loops=1
Buffers: shared read=54554 dirtied=36489 written=36468
Worker 1: actual time=240.355..75258.837 rows=3347802 loops=1
Buffers: shared read=54882 dirtied=36486 written=36467
-> Parallel Hash (cost=205601.81..205601.81 rows=4166681 width=35) (actual time=65812.428..65812.431 rows=3333333 loops=3)
Output: b.c1, b.id
Buckets: 65536 Batches: 256 Memory Usage: 3328kB
Buffers: shared hit=32 read=163903 dirtied=109398 written=109319, temp written=70136
Worker 0: actual time=65812.900..65812.904 rows=3345876 loops=1
Buffers: shared hit=11 read=54840 dirtied=36404 written=36377, temp written=23428
Worker 1: actual time=65812.873..65812.875 rows=3321816 loops=1
Buffers: shared hit=15 read=54441 dirtied=36516 written=36489, temp written=23320
-> Parallel Seq Scan on public.t_big_2 b (cost=0.00..205601.81 rows=4166681 width=35) (actual time=1.490..47839.237 rows=3333333 loops=3)
Output: b.c1, b.id
Buffers: shared hit=32 read=163903 dirtied=109398 written=109319
Worker 0: actual time=1.464..47814.446 rows=3345876 loops=1
Buffers: shared hit=11 read=54840 dirtied=36404 written=36377
Worker 1: actual time=1.470..47104.413 rows=3321816 loops=1
Buffers: shared hit=15 read=54441 dirtied=36516 written=36489
-> Parallel Hash (cost=205601.81..205601.81 rows=4166681 width=62) (actual time=113720.080..113720.080 rows=3333333 loops=3)
Output: c.c2, c.c1
Buckets: 65536 Batches: 512 Memory Usage: 2432kB
Buffers: shared read=163935 dirtied=133935 written=133865, temp written=103856
Worker 0: actual time=113719.124..113719.124 rows=3332395 loops=1
Buffers: shared read=54630 dirtied=44552 written=44528, temp written=34648
Worker 1: actual time=113720.557..113720.558 rows=3329197 loops=1
Buffers: shared read=54577 dirtied=44548 written=44525, temp written=34576
-> Parallel Seq Scan on public.t_big_3 c (cost=0.00..205601.81 rows=4166681 width=62) (actual time=0.126..80608.068 rows=3333333 loops=3)
Output: c.c2, c.c1
Buffers: shared read=163935 dirtied=133935 written=133865
Worker 0: actual time=0.260..80737.065 rows=3332395 loops=1
Buffers: shared read=54630 dirtied=44552 written=44528
Worker 1: actual time=0.049..80943.448 rows=3329197 loops=1
Buffers: shared read=54577 dirtied=44548 written=44525
-> Parallel Hash (cost=205601.02..205601.02 rows=4166602 width=62) (actual time=10279.722..10279.722 rows=3333333 loops=3)
Output: d.c3, d.c2
Buckets: 65536 Batches: 512 Memory Usage: 2400kB
Buffers: shared hit=32 read=163903 dirtied=133935 written=133839, temp written=103004
Worker 0: actual time=10222.812..10222.812 rows=3297904 loops=1
Buffers: shared hit=9 read=54055 dirtied=44154 written=44122, temp written=34236
Worker 1: actual time=10222.839..10222.839 rows=3258559 loops=1
Buffers: shared hit=6 read=53413 dirtied=43464 written=43432, temp written=33504
-> Parallel Seq Scan on public.t_big_4 d (cost=0.00..205601.02 rows=4166602 width=62) (actual time=0.163..7282.409 rows=3333333 loops=3)
Output: d.c3, d.c2
Buffers: shared hit=32 read=163903 dirtied=133935 written=133839
Worker 0: actual time=0.108..7244.071 rows=3297904 loops=1
Buffers: shared hit=9 read=54055 dirtied=44154 written=44122
Worker 1: actual time=0.034..7223.191 rows=3258559 loops=1
Buffers: shared hit=6 read=53413 dirtied=43464 written=43432
Planning Time: 1.134 ms
Execution Time: 405878.841 ms
(83 rows)
[local:/data/run/pg12]:5120 pg12@testdb=#可以看到，执行计划中的成本回归一个正常的数值，算法使用Hash Join。由于内存不足，PG把数据拆分为N份，使用临时表来临时缓存Hash Table，使用不同的Batch来执行Join。
到此，关于“分析PostgreSQL中的大表连接”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/15.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
