postgresql - Linux 上 PostgreSQL 中的配置参数 work_mem

Question

我必须通过调整基本的 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

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

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

Answer 1

我在 explain.depesz.com, have a look 上发布了您的查询计划.

查询规划器的估计在某些地方是非常错误的。你最近运行过 ANALYZE 吗？

在 Statistics Used by the Planner 上阅读手册中的章节和 Planner Cost Constants .请特别注意有关 random_page_cost 和 default_statistics_target 的章节。
您可以尝试:

ALTER TABLE diplomas ALTER COLUMN number SET STATISTICS 1000;
ANALYZE diplomas;

或者对于具有 1000 万行的表甚至更高。这取决于数据分布和实际查询。实验。默认为 100，最大为 10000。

对于那种大小的数据库，只有 1 或 5 MB 的 work_mem一般是不够的。阅读 Postgres Wiki page on Tuning Postgres @aleroot 链接到。

根据 EXPLAIN 输出，您的查询需要 430104kB 磁盘内存，您必须将 work_mem 设置为 500MB 或更多以允许内存中排序。数据的内存表示比磁盘表示需要更多的空间。您可能对什么感兴趣 Tom Lane posted on that matter recently .

像您尝试的那样，将 work_mem 增加一点点，不会有太大帮助，甚至会减慢速度。将其全局设置为高甚至会造成伤害，尤其是在并发访问的情况下。多个 session 可能会互相饿死资源。如果资源有限，为一个目的分配更多内存会占用另一个目的的内存。最佳设置取决于完整的情况。

为了避免副作用，只在你的 session 中将它设置得足够高，并且暂时用于查询:

SET work_mem = '500MB';

之后将其重置为默认值:

RESET work_mem;

或者使用SET LOCAL将其设置为只为当前事务开始。