作者
digoal
日期
2020-06-25
标签
PostgreSQL , select , 顺序 , 优化
背景
https://www.cybertec-postgresql.com/en/postgresql-speeding-up-analytics-and-windowing-functions/
目前PG在某些语句中, 优化器无法最优化的执行, 例如多个窗口函数(有排序的情况), 在最后需要对结果排序时, 窗口的顺序对优化器是否采用多次排序有影响.
test=# CREATE TABLE data (id int);
CREATE TABLE
test=# INSERT INTO data SELECT * FROM generate_series(1, 5);
INSERT 0 5
```
test=# SELECT * FROM data;
id
1
2
3
4
5
(5 rows)
```
test=# SELECT *, array_agg(id) OVER (ORDER BY id) FROM data;
id | array_agg
----+-------------
1 | {1}
2 | {1,2}
3 | {1,2,3}
4 | {1,2,3,4}
5 | {1,2,3,4,5}
(5 rows)
test=# SELECT *,
array_agg(id) OVER (ORDER BY id),
array_agg(id) OVER (ORDER BY id DESC)
FROM data;
id | array_agg | array_agg
----+-------------+-------------
5 | {1,2,3,4,5} | {5}
4 | {1,2,3,4} | {5,4}
3 | {1,2,3} | {5,4,3}
2 | {1,2} | {5,4,3,2}
1 | {1} | {5,4,3,2,1}
(5 rows)
```
test=# explain
SELECT *,
array_agg(id) OVER (ORDER BY id),
array_agg(id) OVER (ORDER BY id DESC)
FROM data
ORDER BY id;
QUERY PLAN
Sort (cost=557.60..563.97 rows=2550 width=68)
Sort Key: id
<- WindowAgg (cost=368.69..413.32 rows=2550 width=68)
<- Sort (cost=368.69..375.07 rows=2550 width=36)
Sort Key: id DESC
<- WindowAgg (cost=179.78..224.41 rows=2550 width=36)
<- Sort (cost=179.78..186.16 rows=2550 width=4)
Sort Key: id
<- Seq Scan on data (cost=0.00..35.50 rows=2550 width=4)
(9 rows)
```
调整窗口查询的顺序后, 最后一个字段的顺序和最后数据排序一致, 那么可以避免最后对数据排序.
```
test=# explain
SELECT *,
array_agg(id) OVER (ORDER BY id DESC),
array_agg(id) OVER (ORDER BY id)
FROM data
ORDER BY id;
QUERY PLAN
WindowAgg (cost=368.69..413.32 rows=2550 width=68)
<- Sort (cost=368.69..375.07 rows=2550 width=36)
Sort Key: id
<- WindowAgg (cost=179.78..224.41 rows=2550 width=36)
<- Sort (cost=179.78..186.16 rows=2550 width=4)
Sort Key: id DESC
<- Seq Scan on data (cost=0.00..35.50 rows=2550 width=4)
(7 rows)
```
At this point PostgreSQL is not able (yet?) to make those adjustments for you so some manual improvements will definitely help. Try to adjust your windowing functions in a way that columns needing identical sorting are actually next to each other.
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
