postgresql - 在两台不同的机器上进行 GROUP BY DISTINCT 查询的不同查询计划

Question

我正在尝试在不同计算机上运行的两个数据库上运行相同的查询。我正在尝试找到导致他们使用不同查询计划的设置。

设置

CREATE TABLE my_table (
    condition_id BIGINT,
    group_by_id BIGINT,
    order_date DATE,
    data DOUBLE PRECISION
);

INSERT INTO my_table (condition_id, group_by_id, order_date, data)
SELECT 1000 * random(),
       10000 * random(),
       current_date - cast(cast(100 * random() AS BIGINT) || ' day' AS INTERVAL),
       random()
FROM generate_series(1, 10000000);

CREATE INDEX my_table_idx ON my_table (condition_id, group_by_id, order_date DESC);

VACUUM ANALYSE my_table;

查询

EXPLAIN (ANALYSE TRUE, BUFFERS TRUE)
SELECT DISTINCT ON (group_by_id) *
FROM my_table
    WHERE condition_id = 1
ORDER BY group_by_id, order_date DESC;

结果

机器 1 未缓存

Unique  (cost=0.56..10220.12 rows=6209 width=28) (actual time=0.034..8.820 rows=6393 loops=1)
  Buffers: shared hit=10106 read=54
  I/O Timings: read=0.367
  ->  Index Scan using my_table_idx on my_table  (cost=0.56..10195.97 rows=9662 width=28) (actual time=0.033..6.935 rows=10106 loops=1)
        Index Cond: (condition_id = 1)
        Buffers: shared hit=10106 read=54
        I/O Timings: read=0.367
Planning time: 0.177 ms
Execution time: 9.197 ms

机器 1 已缓存

Unique  (cost=0.56..10220.12 rows=6209 width=28) (actual time=0.013..9.059 rows=6393 loops=1)
  Buffers: shared hit=10160
  ->  Index Scan using my_table_idx on my_table  (cost=0.56..10195.97 rows=9662 width=28) (actual time=0.013..7.245 rows=10106 loops=1)
        Index Cond: (condition_id = 1)
        Buffers: shared hit=10160
Planning time: 0.069 ms
Execution time: 9.430 ms

机器 2 未缓存

Unique  (cost=10261.82..10271.81 rows=6319 width=28) (actual time=3604.888..3607.945 rows=6243 loops=1)
  Buffers: shared read=9236
  I/O Timings: read=3186.548
  ->  Sort  (cost=10261.82..10266.81 rows=9987 width=28) (actual time=3604.886..3606.016 rows=9808 loops=1)
        Sort Key: group_by_id, order_date DESC
        Sort Method: quicksort  Memory: 1151kB
        Buffers: shared read=9236
        I/O Timings: read=3186.548
        ->  Bitmap Heap Scan on my_table  (cost=70.59..10129.13 rows=9987 width=28) (actual time=29.201..3528.437 rows=9808 loops=1)
              Recheck Cond: (condition_id = 1)
              Heap Blocks: exact=9184
              Buffers: shared read=9236
              I/O Timings: read=3186.548
              ->  Bitmap Index Scan on my_table_idx  (cost=0.00..70.09 rows=9987 width=0) (actual time=27.637..27.637 rows=9808 loops=1)
                    Index Cond: (condition_id = 1)
                    Buffers: shared read=52
                    I/O Timings: read=2.168
Planning time: 0.771 ms
Execution time: 3608.721 ms

机器 2 已缓存

Unique  (cost=10261.82..10271.81 rows=6319 width=28) (actual time=32.859..40.082 rows=6243 loops=1)
  Buffers: shared hit=9236
  ->  Sort  (cost=10261.82..10266.81 rows=9987 width=28) (actual time=32.856..36.278 rows=9808 loops=1)
        Sort Key: group_by_id, order_date DESC
        Sort Method: quicksort  Memory: 1151kB
        Buffers: shared hit=9236
        ->  Bitmap Heap Scan on my_table  (cost=70.59..10129.13 rows=9987 width=28) (actual time=2.884..26.122 rows=9808 loops=1)
              Recheck Cond: (condition_id = 1)
              Heap Blocks: exact=9184
              Buffers: shared hit=9236
              ->  Bitmap Index Scan on my_table_idx  (cost=0.00..70.09 rows=9987 width=0) (actual time=1.551..1.551 rows=9808 loops=1)
                    Index Cond: (condition_id = 1)
                    Buffers: shared hit=52
Planning time: 0.098 ms
Execution time: 40.907 m

它们运行不同的硬件和略有不同的 postgres 版本。机器 1 运行 10.10，而机器 2 运行 10.6。我的下一步可能是尝试升级两台机器上的版本。

我尝试更改 work_mem、random_page_cost、 effective_cache_size 和 cpu_tuple_cost。但这似乎并没有影响它(如果我不将其设置为一些愚蠢的东西，这会导致他们的行为变得更糟)。运行上述测试时两台机器上的设置为:

work_mem 20971kB
random_page_cost 1.1
effective_cache_size 24GB
cpu_tuple_cost 0.01

我相信我希望机器 2 运行与机器 1 相同的查询计划。因为现在 2 很大程度上受到数据在磁盘上的排序方式以及数据是否在内存中的影响。

事实证明，查询计划并没有那么不同。只是机器 1 将其永远保存在缓存中。为了在未缓存时真正使读取合理，我可能会不时尝试对其进行聚类。也许可以看看为什么机器 2 如此频繁地取消缓存该表。

CLUSTER my_table USING my_table_idx;

Answer 1

这两个计划的预估成本几乎相同，因此可能存在随机性。请记住，您使用 random() 来生成数据。每台机器的计划在多次测试运行中是否保持稳定？

观察到的差异很可能是缓存效应。慢速计划执行大量 I/O，而快速计划则缓存所有内容。

要从执行计划中获得更多信息，请将 track_io_timing 设置为 on。

这里影响计划选择的配置参数是 effective_cache_size (高值有利于索引扫描)和 random_page_cost (低值有利于索引扫描)。