分布鍵join+limit優化
更新時間 2025-02-05 09:36:58
最近更新時間: 2025-02-05 09:36:58
分享文章
本頁介紹天翼云TeleDB數據庫通過分布鍵join+limit優化來提高性能的優化案例。
-
測試數據準備：
teledb=# CREATE TABLE t1(f1 serial not null unique,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 teledb=# CREATE TABLE t2(f1 serial not null unique,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 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 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 teledb=# analyze t1; ANALYZE teledb=# analyze t2; ANALYZE teledb=# teledb=# \timing Timing is on. -
優化前SQL執行計劃和耗時：
此處是將prefer_olap參數設置為off的場景，該參數默認為on。
teledb=# explain select t1.* from t1,t2 where t1.f1=t2.f1 limit 10; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.25..1.65 rows=10 width=367) -> Merge Join (cost=0.25..140446.26 rows=1000000 width=367) Merge Cond: (t1.f1 = t2.f1) -> Remote Subquery Scan on all (dn01,dn02,dn03,dn04,dn05,dn06,dn07,dn08,dn09,dn10) (cost=100.12..434823.13 rows=1000000 width=367) -> Index Scan using t1_f1_key on t1 (cost=0.12..62723.13 rows=1000000 width=367) -> Remote Subquery Scan on all (dn01,dn02,dn03,dn04,dn05,dn06,dn07,dn08,dn09,dn10) (cost=100.12..71823.13 rows=1000000 width=4) -> Index Only Scan using t2_f1_key on t2 (cost=0.12..62723.13 rows=1000000 width=4) (7 rows) Time: 1.372 ms teledb=# explain analyze select t1.* from t1,t2 where t1.f1=t2.f1 limit 10; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.25..1.65 rows=10 width=367) (actual time=2675.437..2948.199 rows=10 loops=1) -> Merge Join (cost=0.25..140446.26 rows=1000000 width=367) (actual time=2675.431..2675.508 rows=10 loops=1) Merge Cond: (t1.f1 = t2.f1) -> Remote Subquery Scan on all (dn01,dn02,dn03,dn04,dn05,dn06,dn07,dn08,dn09,dn10) (cost=100.12..434823.13 rows=1000000 width=367) (actual time=1.661..1.704 rows=10 loops=1) -> Remote Subquery Scan on all (dn01,dn02,dn03,dn04,dn05,dn06,dn07,dn08,dn09,dn10) (cost=100.12..71823.13 rows=1000000 width=4) (actual time=2673.761..2673.783 rows=10 loops=1) Planning time: 0.358 ms Execution time: 2973.948 ms (7 rows) Time: 2976.008 ms (00:02.976) teledb=#可以看到，join+limit寫法，在prefer_olap=off的場景下，會拉大量的數據到CN節點進行計算、排序和limit過濾，消耗了大量的網絡開銷。
-
優化后執行計劃和耗時：
設置參數prefer_olap=on（默認值），將join下推到DN節點執行：
teledb=# set prefer_olap to on; SET Time: 0.291 ms teledb=# explain select t1.* from t1,t2 where t1.f1=t2.f1 limit 10; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Limit (cost=100.25..101.70 rows=10 width=367) -> Remote Subquery Scan on all (dn01,dn02,dn03,dn04,dn05,dn06,dn07,dn08,dn09,dn10) (cost=100.25..101.70 rows=10 width=367) -> Limit (cost=0.25..1.65 rows=10 width=367) -> Merge Join (cost=0.25..140446.26 rows=1000000 width=367) Merge Cond: (t1.f1 = t2.f1) -> Index Scan using t1_f1_key on t1 (cost=0.12..62723.13 rows=1000000 width=367) -> Index Only Scan using t2_f1_key on t2 (cost=0.12..62723.13 rows=1000000 width=4) (7 rows) Time: 1.061 ms teledb=# explain analyze select t1.* from t1,t2 where t1.f1=t2.f1 limit 10; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=100.25..101.70 rows=10 width=367) (actual time=1.527..3.899 rows=10 loops=1) -> Remote Subquery Scan on all (dn01,dn02,dn03,dn04,dn05,dn06,dn07,dn08,dn09,dn10) (cost=100.25..101.70 rows=10 width=367) (actual time=1.525..1.529 rows=10 loops=1) Planning time: 0.360 ms Execution time: 18.193 ms (4 rows) Time: 19.921 ms可以看到，prefer_olap設置為on后，計算、排序和limit過濾在DN上完成，返回CN的數量是每個DN節點limit之后的數據，解決了上面SQL優化前大量數據上拉CN節點的問題，性能提升了150倍。
這里也說明，prefer_olap參數使用也和SQL特點有關，如果將prefer_olap設置off，SQL需要拉大量數據到CN計算的話，性能會明顯下降，我們在設置prefer_olap參數前一定要做好評估。
