您现在的位置是:首页 > 博客网站制作 > 心得笔记心得笔记

PostgreSQL利用递归优化求稀疏列唯一值的方法

杨青青2022-02-20【心得笔记】人已围观

简介在数据库中经常会碰到一些表的列是稀疏列,只有很少的值,例如性别字段,一般就只有2种不同的值。但是当我们求这些稀疏列的唯一值时,如果表的数据量很大,速度还是会很慢。例如:创建

在数据库中经常会碰到一些表的列是稀疏列,只有很少的值,例如性别字段,一般就只有2种不同的值。

但是当我们求这些稀疏列的唯一值时,如果表的数据量很大,速度还是会很慢。

例如:

创建测试表

bill=#createtablet_sex(sexchar(1),otherinfotext);
CREATETABLE
bill=#insertintot_sexselect'm',generate_series(1,10000000)||'thisistest';
INSERT010000000
bill=#insertintot_sexselect'w',generate_series(1,10000000)||'thisistest';
INSERT010000000

查询:

可以看到下面的查询速度很慢。

bill=#selectcount(distinctsex)fromt_sex;
count
-------
2
(1row)

Time:8803.505ms(00:08.804)
bill=#selectsexfromt_sextgroupbysex;
sex
-----
m
w
(2rows)

Time:1026.464ms(00:01.026)

那么我们对该字段加上索引又是什么情况呢?

速度依然没有明显

bill=#createindexidx_sex_1ont_sex(sex);
CREATEINDEX
bill=#selectcount(distinctsex)fromt_sex;
count
-------
2
(1row)

Time:8502.460ms(00:08.502)
bill=#selectsexfromt_sextgroupbysex;
sex
-----
m
w
(2rows)

Time:572.353ms

的变化,可以看到执行计划已经使用IndexOnlyScan了。

bill=#explainselectcount(distinctsex)fromt_sex;
QUERYPLAN
----------------------------------------------------------------------------------------------
Aggregate(cost=371996.44..371996.45rows=1width=8)
->IndexOnlyScanusingidx_sex_1ont_sex(cost=0.44..321996.44rows=20000000width=2)
(2rows)

同样的SQL我们看看在Oracle中性能如何?

创建测试表:

SQL>createtablet_sex(sexchar(1),otherinfovarchar2(100));

Tablecreated.

SQL>insertintot_sexselect'm',rownum||'thisistest'fromdualconnectbylevel<=10000000;

10000000rowscreated.

SQL>commit;

Commitcomplete.

SQL>insertintot_sexselect'w',rownum||'thisistest'fromdualconnectbylevel<=10000000;

10000000rowscreated.

SQL>commit;

Commitcomplete.

性能测试:

SQL>setlines1000pages2000
SQL>setautotraceon
SQL>settimingon

SQL>selectcount(distinctsex)fromt_sex;

COUNT(DISTINCTSEX)
------------------
2

Elapsed:00:00:01.58

ExecutionPlan
----------------------------------------------------------
Planhashvalue:3915432945

----------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|3|20132(1)|00:00:01|
|1|SORTGROUPBY||1|3|||
|2|TABLEACCESSFULL|T_SEX|14M|42M|20132(1)|00:00:01|
----------------------------------------------------------------------------

Note
-----
-dynamicstatisticsused:dynamicsampling(level=2)


Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
74074consistentgets
0physicalreads
0redosize
552bytessentviaSQL*Nettoclient
608bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
1sorts(memory)
0sorts(disk)
1rowsprocessed

SQL>selectsexfromt_sextgroupbysex;

SE
--
m
w

Elapsed:00:00:01.08

ExecutionPlan
----------------------------------------------------------
Planhashvalue:3915432945

----------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------
|0|SELECTSTATEMENT||14M|42M|20558(3)|00:00:01|
|1|SORTGROUPBY||14M|42M|20558(3)|00:00:01|
|2|TABLEACCESSFULL|T_SEX|14M|42M|20132(1)|00:00:01|
----------------------------------------------------------------------------

Note
-----
-dynamicstatisticsused:dynamicsampling(level=2)


Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
74074consistentgets
0physicalreads
0redosize
589bytessentviaSQL*Nettoclient
608bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
1sorts(memory)
0sorts(disk)
2rowsprocessed

可以看到Oracle的性能即使不加索引也明显比PostgreSQL中要好。

那么我们在PostgreSQL中是不是没办法继续优化了呢?这种情况我们利用pg中的递归语句结合索引可以大幅提升性能。

SQL改写:

bill=#withrecursivetmpas(
bill(#(
bill(#selectmin(t.sex)assexfromt_sextwheret.sexisnotnull
bill(#)
bill(#unionall
bill(#(
bill(#select(selectmin(t.sex)fromt_sextwheret.sex>s.sexandt.sexisnotnull)
bill(#fromtmpswheres.sexisnotnull
bill(#)
bill(#)
bill-#selectcount(distinctsex)fromtmp;
count
-------
2
(1row)

Time:2.711ms

查看执行计划:

bill=#explainwithrecursivetmpas(
bill(#(
bill(#selectmin(t.sex)assexfromt_sextwheret.sexisnotnull
bill(#)
bill(#unionall
bill(#(
bill(#select(selectmin(t.sex)fromt_sextwheret.sex>s.sexandt.sexisnotnull)
bill(#fromtmpswheres.sexisnotnull
bill(#)
bill(#)
bill-#selectcount(distinctsex)fromtmp;
QUERYPLAN
----------------------------------------------------------------------------------------------------------------------
Aggregate(cost=53.62..53.63rows=1width=8)
CTEtmp
->RecursiveUnion(cost=0.46..51.35rows=101width=32)
->Result(cost=0.46..0.47rows=1width=32)
InitPlan3(returns$1)
->Limit(cost=0.44..0.46rows=1width=2)
->IndexOnlyScanusingidx_sex_1ont_sext(cost=0.44..371996.44rows=20000000width=2)
IndexCond:(sexISNOTNULL)
->WorkTableScanontmps(cost=0.00..4.89rows=10width=32)
Filter:(sexISNOTNULL)
->CTEScanontmp(cost=0.00..2.02rows=101width=32)
(11rows)

Time:1.371ms

可以看到执行时间从原先的8000ms降低到了2ms,提升了几千倍!

甚至对比Oracle,性能也是提升了很多。

但是需要注意的是:这种写法仅仅是针对稀疏列,换成数据分布广泛的字段,显然性能是下降的,所以使用递归SQL不适合数据分布广泛的字段的groupby或者count(distinct)操作。

Tags:

很赞哦! ()

文章评论

站点信息

  • 建站时间:2011年01月12日
  • 网站程序:帝国CMS7.5
  • 主题模板《今夕何夕》
  • 文章统计625篇文章
  • 标签管理标签云
  • 微信公众号:扫描二维码,关注我们