非分布鍵 join通常使用hash join性能更好
更新時間 2025-02-05 09:36:58
最近更新時間: 2025-02-05 09:36:58
分享文章
本頁介紹天翼云TeleDB數據庫的非分布鍵 join使用hash join會在性能上更好的場景說明。
非分布鍵關聯，默認下推DN節點執行，DN節點之間會發生數據重分布，重分布數據量大的時候，性能會有明顯下降。這種場景，通常使用Hash Join關聯，性能會比Merge Join和Nested Loop更好。
而為了提高TP類業務查詢的性能，我們經常需要對一些字段創建索引，使用有索引字段join時，優化器往往也會使用Merge Join和Nested Loop。針對此場景，可以通過會話級別關閉Merge Join和Nested Loop，讓優化器走Hash Join，或通過hint指定Hash Join。
以下是關于該場景的測試情況：
-
測試數據準備：
teledb=# CREATE TABLE t1(f1 serial not null,f2 text,f3 text,f4 text,f5 text,f6 text,f7 text,f8 text,f9 text,f10 text,f11 text,f12 text) distribute by shard(f1); CREATE TABLE Time: 481.042 ms teledb=# create index t1_f1_idx on t1(f2); CREATE INDEX Time: 85.521 ms teledb=# CREATE TABLE t2(f1 serial not null,f2 text,f3 text,f4 text,f5 text,f6 text,f7 text,f8 text,f9 text,f10 text,f11 text,f12 text) distribute by shard(f1); CREATE TABLE Time: 75.973 ms teledb=# create index t2_f1_idx on t2(f2); CREATE INDEX Time: 29.890 ms teledb=# insert into t1 select t,md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text) from generate_series(1,1000000) as t; INSERT 0 1000000 Time: 16450.623 ms (00:16.451) teledb=# insert into t2 select t,md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text) from generate_series(1,1000000) as t; INSERT 0 1000000 Time: 17218.738 ms (00:17.219) teledb=# analyze t1; ANALYZE Time: 2219.341 ms (00:02.219) teledb=# analyze t2; ANALYZE Time: 1649.506 ms (00:01.650) teledb=# -
優化前SQL執行計劃和耗時：
可以看到，關聯走了Merge Join，耗時6.6秒。
teledb=# explain select t1.* from t1,t2 where t1.f2=t2.f2 limit 10; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Limit (cost=100.25..102.78 rows=10 width=367) -> Remote Subquery Scan on all (dn001,dn002) (cost=100.25..102.78 rows=10 width=367) -> Limit (cost=0.25..2.73 rows=10 width=367) -> Merge Join (cost=0.25..248056.80 rows=1000000 width=367) Merge Cond: (t1.f2 = t2.f2) -> Remote Subquery Scan on all (dn001,dn002) (cost=100.12..487380.85 rows=1000000 width=367) Distribute results by S: f2 -> Index Scan using t1_f1_idx on t1 (cost=0.12..115280.85 rows=1000000 width=367) -> Materialize (cost=100.12..155875.95 rows=1000000 width=33) -> Remote Subquery Scan on all (dn001,dn002) (cost=100.12..153375.95 rows=1000000 width=33) Distribute results by S: f2 -> Index Only Scan using t2_f1_idx on t2 (cost=0.12..115275.95 rows=1000000 width=33) (12 rows) Time: 4.183 ms teledb=# explain analyze select t1.* from t1,t2 where t1.f2=t2.f2 limit 10; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=100.25..102.78 rows=10 width=367) (actual time=6555.346..6556.296 rows=10 loops=1) -> Remote Subquery Scan on all (dn001,dn002) (cost=100.25..102.78 rows=10 width=367) (actual time=6555.343..6555.349 rows=10 loops=1) Planning time: 0.473 ms Execution time: 6569.828 ms (4 rows) Time: 6614.439 ms (00:06.614) -
關閉Merge Join后的執行計劃和耗時：
可以看到，關閉Merge Join后，關聯走了Nested Loop，耗時5.6秒。
teledb=# set enable_mergejoin to off; SET Time: 0.422 ms teledb=# explain select t1.* from t1,t2 where t1.f2=t2.f2 limit 10; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Limit (cost=100.12..103.57 rows=10 width=367) -> Remote Subquery Scan on all (dn001,dn002) (cost=100.12..103.57 rows=10 width=367) -> Limit (cost=0.12..3.52 rows=10 width=367) -> Nested Loop (cost=0.12..339232.00 rows=1000000 width=367) -> Remote Subquery Scan on all (dn001,dn002) (cost=100.00..434740.00 rows=1000000 width=367) Distribute results by S: f2 -> Seq Scan on t1 (cost=0.00..62640.00 rows=1000000 width=367) -> Materialize (cost=100.12..100.31 rows=1 width=33) -> Remote Subquery Scan on all (dn001,dn002) (cost=100.12..100.30 rows=1 width=33) Distribute results by S: f2 -> Index Only Scan using t2_f1_idx on t2 (cost=0.12..0.27 rows=1 width=33) Index Cond: (f2 = t1.f2) (12 rows) Time: 1.033 ms teledb=# explain analyze select t1.* from t1,t2 where t1.f2=t2.f2 limit 10; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=100.12..103.57 rows=10 width=367) (actual time=5608.326..5609.571 rows=10 loops=1) -> Remote Subquery Scan on all (dn001,dn002) (cost=100.12..103.57 rows=10 width=367) (actual time=5608.323..5608.349 rows=10 loops=1) Planning time: 0.347 ms Execution time: 5669.901 ms (4 rows) Time: 5672.584 ms (00:05.673) -
繼續關閉Nested Loop后的執行計劃和耗時：
可以看到，繼續關閉Nested Loop后，關聯走了Hash Join，耗時1.1秒。
teledb=# set enable_nestloop to off; SET Time: 0.436 ms teledb=# explain select t1.* from t1,t2 where t1.f2=t2.f2 limit 10; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Limit (cost=85983.00..85984.94 rows=10 width=367) -> Remote Subquery Scan on all (dn001,dn002) (cost=85983.00..85984.94 rows=10 width=367) -> Limit (cost=85883.00..85884.89 rows=10 width=367) -> Hash Join (cost=85883.00..274580.00 rows=1000000 width=367) Hash Cond: (t1.f2 = t2.f2) -> Remote Subquery Scan on all (dn001,dn002) (cost=100.00..434740.00 rows=1000000 width=367) Distribute results by S: f2 -> Seq Scan on t1 (cost=0.00..62640.00 rows=1000000 width=367) -> Hash (cost=100740.00..100740.00 rows=1000000 width=33) -> Remote Subquery Scan on all (dn001,dn002) (cost=100.00..100740.00 rows=1000000 width=33) Distribute results by S: f2 -> Seq Scan on t2 (cost=0.00..62640.00 rows=1000000 width=33) (12 rows) Time: 1.141 ms teledb=# explain analyze select t1.* from t1,t2 where t1.f2=t2.f2 limit 10; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=85983.00..85984.94 rows=10 width=367) (actual time=1083.691..1085.962 rows=10 loops=1) -> Remote Subquery Scan on all (dn001,dn002) (cost=85983.00..85984.94 rows=10 width=367) (actual time=1083.688..1083.699 rows=10 loops=1) Planning time: 0.530 ms Execution time: 1108.830 ms (4 rows) Time: 1117.713 ms (00:01.118) teledb=#
