- Mastering PostgreSQL 10
- Hans Jürgen Sch?nig
- 150字
- 2021-06-30 19:04:01
Investigating performance
Grouping sets is a powerful feature; they help to reduce the number of expensive queries. Internally, PostgreSQL will basically turn to traditional GroupAggregates to make things work. A GroupAggregate node requires sorted data, so be prepared that PostgreSQL might do a lot of temporary sorting:
test=# explain SELECT region, country, avg(production)
FROM t_oil
WHERE country IN ('USA', 'Canada', 'Iran', 'Oman')
GROUP BY GROUPING SETS ( (), region, country);
QUERY PLAN
---------------------------------------------------------------
GroupAggregate (cost=22.58..32.69 rows=34 width=52)
Group Key: region
Group Key: ()
Sort Key: country
Group Key: country
-> Sort (cost=22.58..23.04 rows=184 width=24)
Sort Key: region
-> Seq Scan on t_oil
(cost=0.00..15.66 rows=184 width=24)
Filter: (country = ANY
('{USA,Canada,Iran,Oman}'::text[]))
(9 rows)
In PostgreSQL, hash aggregates are only supported for normal GROUP BY clauses involving no grouping sets. In PostgreSQL 10.0, the planner already has more options than in PostgreSQL 9.6. Expect grouping sets to be faster in the new version.
推薦閱讀
- Dreamweaver CS3 Ajax網(wǎng)頁(yè)設(shè)計(jì)入門與實(shí)例詳解
- 樂高機(jī)器人EV3設(shè)計(jì)指南:創(chuàng)造者的搭建邏輯
- ServiceNow Cookbook
- 快學(xué)Flash動(dòng)畫百例
- 樂高創(chuàng)意機(jī)器人教程(中級(jí) 下冊(cè) 10~16歲) (青少年iCAN+創(chuàng)新創(chuàng)意實(shí)踐指導(dǎo)叢書)
- 大數(shù)據(jù)安全與隱私保護(hù)
- AutoCAD 2012中文版繪圖設(shè)計(jì)高手速成
- 完全掌握AutoCAD 2008中文版:機(jī)械篇
- Linux服務(wù)與安全管理
- 人工智能趣味入門:光環(huán)板程序設(shè)計(jì)
- 工業(yè)機(jī)器人應(yīng)用案例集錦
- 電氣控制與PLC技術(shù)應(yīng)用
- Word 2007,Excel 2007辦公應(yīng)用融會(huì)貫通
- 單片機(jī)技能與實(shí)訓(xùn)
- Learning Linux Shell Scripting