count(distinct xx)優化
更新時間 2025-02-05 09:36:58
最近更新時間: 2025-02-05 09:36:58
分享文章
本頁介紹天翼云TeleDB數據庫的count(distinct xx)優化案例。
count(distinct xxx)發生在CN節點，對于TP類業務，需要操作的數據量少的情況下，性能開銷是沒有問題的，而且往往比下推執行的性能開銷還要小。但如果一次要操作的數據量比較大的AP類業務，則網絡傳輸就會成功瓶頸。
針對distinct，可以改寫為group by寫法，將數據去重操作下推到DN上執行，提高效率。
下面是針對count(distinct xx)寫法的SQL優化案例：
- 測試數據準備：
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); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command. CREATE TABLE Time: 89.938 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: 14849.045 ms (00:14.849) teledb=# analyze t1; ANALYZE Time: 1340.387 ms (00:01.340) - 改寫前的SQL執行計劃和耗時：
teledb=# explain (verbose) select count(distinct f2) from t1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=103320.00..103320.01 rows=1 width=8) Output: count(DISTINCT f2) -> Remote Subquery Scan on all (dn01,dn02,dn03,dn04,dn05,dn06,dn07,dn08,dn09,dn10) (cost=100.00..100820.00 rows=1000000 width=33) Output: f2 -> Seq Scan on public.t1 (cost=0.00..62720.00 rows=1000000 width=33) Output: f2 (6 rows) Time: 0.748 ms postgres=# select count(distinct f2) from t1; count --------- 1000000 (1 row) Time: 6274.684 ms (00:06.275) teledb=# select count(distinct f2) from t1 where f1 <10; count ------- 9 (1 row) Time: 19.261 ms - 下面看看改寫后的執行計劃和耗時：
可以看到，改寫后的SQL效率提升了5倍。teledb=# explain (verbose) select count(1) from (select f2 from t1 group by f2) as t ; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=355600.70..355600.71 rows=1 width=8) Output: count(1) -> Remote Subquery Scan on all (dn01,dn02,dn03,dn04,dn05,dn06,dn07,dn08,dn09,dn10) (cost=355600.69..355600.70 rows=1 width=0) Output: PARTIAL count(1) -> Partial Aggregate (cost=355500.69..355500.70 rows=1 width=8) Output: PARTIAL count(1) -> Group (cost=340500.69..345500.69 rows=1000000 width=33) Output: t1.f2 Group Key: t1.f2 -> Sort (cost=340500.69..343000.69 rows=1000000 width=0) Output: t1.f2 Sort Key: t1.f2 -> Remote Subquery Scan on all (dn01,dn02,dn03,dn04,dn05,dn06,dn07,dn08,dn09,dn10) (cost=216192.84..226192.84 rows=1000000 width=0) Output: t1.f2 Distribute results by S: f2 -> Group (cost=216092.84..221092.84 rows=1000000 width=33) Output: t1.f2 Group Key: t1.f2 -> Sort (cost=216092.84..218592.84 rows=1000000 width=33) Output: t1.f2 Sort Key: t1.f2 -> Seq Scan on public.t1 (cost=0.00..62720.00 rows=1000000 width=33) Output: t1.f2 (23 rows) teledb=# select count(1) from (select f2 from t1 group by f2) as t ; count --------- 1000000 (1 row) Time: 1328.431 ms (00:01.328) teledb=# select count(1) from (select f2 from t1 where f1<10group by f2) as t ; count ------- 9 (1 row) Time: 24.991 ms
