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

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

主站蜘蛛池模板: 罗甸县| 定州市| 黄大仙区| 大理市| 罗平县| 青铜峡市| 黄山市| 浦城县| 庆云县| 临漳县| 库车县| 新兴县| 阜宁县| 南乐县| 扎鲁特旗| 宁南县| 宁波市| 龙胜| 渝北区| 抚远县| 青海省| 胶州市| 牟定县| 新干县| 九江市| 普安县| 察哈| 夏津县| 清丰县| 沐川县| 泸西县| 达孜县| 宜良县| 普定县| 都兰县| 玛纳斯县| 兴城市| 登封市| 阿图什市| 仪征市| 临海市|