linux 中的mem参数,Linux上PostgreSQL中的配置参数work_mem

我必须通过调整基本的PostgreSQL服务器配置参数来优化查询.在文档中我遇到了work_mem参数.然后我检查了如何更改此参数将影响我的查询的性能(使用排序).我用各种work_mem设置测量查询执行时间,非常失望.

我执行查询的表包含10,000,000行,并且有430 MB的数据进行排序. (排序方法：外部合并磁盘：430112kB).

使用work_mem = 1MB,EXPLAIN输出为：

Total runtime: 29950.571 ms (sort takes about 19300 ms).

Sort (cost=4032588.78..4082588.66 rows=19999954 width=8)

(actual time=22577.149..26424.951 rows=20000000 loops=1)

Sort Key: "*SELECT* 1".n

Sort Method: external merge Disk: 430104kB

with work_mem = 5MB：

Total runtime: 36282.729 ms (sort: 25400 ms).

Sort (cost=3485713.78..3535713.66 rows=19999954 width=8)

(actual time=25062.383..33246.561 rows=20000000 loops=1)

Sort Key: "*SELECT* 1".n

Sort Method: external merge Disk: 430104kB

with work_mem = 64MB：

Total runtime: 42566.538 ms (sort: 31000 ms).

Sort (cost=3212276.28..3262276.16 rows=19999954 width=8)

(actual time=28599.611..39454.279 rows=20000000 loops=1)

Sort Key: "*SELECT* 1".n

Sort Method: external merge Disk: 430104kB

任何人都可以解释性能如何恶化？还是建议任何其他方法通过更改服务器参数来更快地执行查询？

我的查询(我知道这不是最佳的,但我必须对这种查询进行基准测试)：

SELECT n

FROM (

SELECT n + 1 AS n FROM table_name

EXCEPT

SELECT n FROM table_name) AS q1

ORDER BY n DESC;

完整执行计划：

Sort (cost=5805421.81..5830421.75 rows=9999977 width=8) (actual time=30405.682..30405.682 rows=1 loops=1)

Sort Key: q1.n

Sort Method: quicksort Memory: 25kB

-> Subquery Scan q1 (cost=4032588.78..4232588.32 rows=9999977 width=8) (actual time=30405.636..30405.637 rows=1 loops=1)

-> SetOp Except (cost=4032588.78..4132588.55 rows=9999977 width=8) (actual time=30405.634..30405.634 rows=1 loops=1)

-> Sort (cost=4032588.78..4082588.66 rows=19999954 width=8) (actual time=23046.478..27733.020 rows=20000000 loops=1)

Sort Key: "*SELECT* 1".n

Sort Method: external merge Disk: 430104kB

-> Append (cost=0.00..513495.02 rows=19999954 width=8) (actual time=0.040..8191.185 rows=20000000 loops=1)

-> Subquery Scan "*SELECT* 1" (cost=0.00..269247.48 rows=9999977 width=8) (actual time=0.039..3651.506 rows=10000000 loops=1)

-> Seq Scan on table_name (cost=0.00..169247.71 rows=9999977 width=8) (actual time=0.038..2258.323 rows=10000000 loops=1)

-> Subquery Scan "*SELECT* 2" (cost=0.00..244247.54 rows=9999977 width=8) (actual time=0.008..2697.546 rows=10000000 loops=1)

-> Seq Scan on table_name (cost=0.00..144247.77 rows=9999977 width=8) (actual time=0.006..1079.561 rows=10000000 loops=1)

Total runtime: 30496.100 ms