本节介绍了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。