在數據庫中經常會碰到一些表的列是稀疏列，只有很少的值，例如性別字段，一般就只有2種不同的值。
但是當我們求這些稀疏列的唯一值時，如果表的數據量很大，速度還是會很慢。
例如：
創建測試表
|
1
2
3
4
5
6
|
bill=# create table t_sex (sex char(1), otherinfo text);CREATE TABLEbill=# insert into t_sex select 'm', generate_series(1,10000000)||'this is test';INSERT 0 10000000bill=# insert into t_sex select 'w', generate_series(1,10000000)||'this is test';INSERT 0 10000000 |
查詢：
可以看到下面的查詢速度很慢。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
bill=# select count(distinct sex) from t_sex; count------- 2(1 row)Time: 8803.505 ms (00:08.804)bill=# select sex from t_sex t group by sex; sex----- m w(2 rows)Time: 1026.464 ms (00:01.026) |
那么我們對該字段加上索引又是什么情況呢？
速度依然沒有明顯
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
bill=# create index idx_sex_1 on t_sex(sex);CREATE INDEXbill=# select count(distinct sex) from t_sex; count------- 2(1 row)Time: 8502.460 ms (00:08.502)bill=# select sex from t_sex t group by sex; sex----- m w(2 rows)Time: 572.353 ms |
的變化，可以看到執行計劃已經使用Index Only Scan了。
|
1
2
3
4
5
6
|
bill=# explain select count(distinct sex) from t_sex; QUERY PLAN---------------------------------------------------------------------------------------------- Aggregate (cost=371996.44..371996.45 rows=1 width=8) -> Index Only Scan using idx_sex_1 on t_sex (cost=0.44..321996.44 rows=20000000 width=2)(2 rows) |
同樣的SQL我們看看在Oracle中性能如何？
創建測試表：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> create table t_sex (sex char(1), otherinfo varchar2(100));Table created.SQL> insert into t_sex select 'm', rownum||'this is test' from dual connect by level <=10000000;10000000 rows created.SQL> commit;Commit complete.SQL> insert into t_sex select 'w', rownum||'this is test' from dual connect by level <=10000000;10000000 rows created.SQL> commit;Commit complete. |
性能測試：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
|
SQL> set lines 1000 pages 2000SQL> set autotrace onSQL> set timing onSQL> select count(distinct sex) from t_sex;COUNT(DISTINCTSEX)------------------ 2Elapsed: 00:00:01.58Execution Plan----------------------------------------------------------Plan hash value: 3915432945----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 3 | 20132 (1)| 00:00:01 || 1 | SORT GROUP BY | | 1 | 3 | | || 2 | TABLE ACCESS FULL| T_SEX | 14M| 42M| 20132 (1)| 00:00:01 |----------------------------------------------------------------------------Note----- - dynamic statistics used: dynamic sampling (level=2)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 74074 consistent gets 0 physical reads 0 redo size 552 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processedSQL> select sex from t_sex t group by sex;SE--mwElapsed: 00:00:01.08Execution Plan----------------------------------------------------------Plan hash value: 3915432945----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14M| 42M| 20558 (3)| 00:00:01 || 1 | SORT GROUP BY | | 14M| 42M| 20558 (3)| 00:00:01 || 2 | TABLE ACCESS FULL| T_SEX | 14M| 42M| 20132 (1)| 00:00:01 |----------------------------------------------------------------------------Note----- - dynamic statistics used: dynamic sampling (level=2)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 74074 consistent gets 0 physical reads 0 redo size 589 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2 rows processed |
可以看到Oracle的性能即使不加索引也明顯比PostgreSQL中要好。
那么我們在PostgreSQL中是不是沒辦法繼續優化了呢？這種情況我們利用pg中的遞歸語句結合索引可以大幅提升性能。
SQL改寫：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
bill=# with recursive tmp as (bill(# (bill(# select min(t.sex) as sex from t_sex t where t.sex is not nullbill(# )bill(# union allbill(# (bill(# select (select min(t.sex) from t_sex t where t.sex > s.sex and t.sex is not null)bill(# from tmp s where s.sex is not nullbill(# )bill(# )bill-# select count(distinct sex) from tmp; count------- 2(1 row)Time: 2.711 ms |
查看執行計劃：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
bill=# explain with recursive tmp as (bill(# (bill(# select min(t.sex) as sex from t_sex t where t.sex is not nullbill(# )bill(# union allbill(# (bill(# select (select min(t.sex) from t_sex t where t.sex > s.sex and t.sex is not null)bill(# from tmp s where s.sex is not nullbill(# )bill(# )bill-# select count(distinct sex) from tmp; QUERY PLAN---------------------------------------------------------------------------------------------------------------------- Aggregate (cost=53.62..53.63 rows=1 width=8) CTE tmp -> Recursive Union (cost=0.46..51.35 rows=101 width=32) -> Result (cost=0.46..0.47 rows=1 width=32) InitPlan 3 (returns $1) -> Limit (cost=0.44..0.46 rows=1 width=2) -> Index Only Scan using idx_sex_1 on t_sex t (cost=0.44..371996.44 rows=20000000 width=2) Index Cond: (sex IS NOT NULL) -> WorkTable Scan on tmp s (cost=0.00..4.89 rows=10 width=32) Filter: (sex IS NOT NULL) -> CTE Scan on tmp (cost=0.00..2.02 rows=101 width=32)(11 rows)Time: 1.371 ms |
可以看到執行時間從原先的8000ms降低到了2ms，提升了幾千倍！
甚至對比Oracle，性能也是提升了很多。
但是需要注意的是：這種寫法僅僅是針對稀疏列，換成數據分布廣泛的字段，顯然性能是下降的, 所以使用遞歸SQL不適合數據分布廣泛的字段的group by或者count(distinct)操作。
到此這篇關于PostgreSQL利用遞歸優化求稀疏列唯一值的文章就介紹到這了,更多相關PostgreSQL遞歸優化內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://blog.csdn.net/weixin_39540651/article/details/112850560
