官术网_书友最值得收藏!

  • 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.

主站蜘蛛池模板: 青阳县| 西宁市| 海林市| 砚山县| 桃源县| 田林县| 灌阳县| 潞城市| 荃湾区| 乐都县| 齐河县| 杭锦旗| 万荣县| 米泉市| 济源市| 泉州市| 日喀则市| 道孚县| 肃宁县| 广宁县| 油尖旺区| 临朐县| 文山县| 云林县| 莒南县| 册亨县| 特克斯县| 玛沁县| 科技| 辉南县| 奉新县| 安陆市| 菏泽市| 县级市| 左贡县| 仁布县| 乌苏市| 玛曲县| 海晏县| 商水县| 茌平县|