在较小的 Postgres 表上，不同的计划和较慢的查询

在两个仅行数不同的表（约 7.8M vs 约 1.4M）上运行相同的查询，会得到两个不同的计划，这听起来很合理。但是在较小的表上执行速度要慢 4 到 5 倍，我想知道原因。

表格定义如下：

   Column   |           Type           | Collation | Nullable | Default 
------------+--------------------------+-----------+----------+---------
 image_id   | bigint                   |           | not null | 
 h3_cell    | h3index                  |           | not null | 
 created_at | timestamp with time zone |           | not null | 
 location   | geometry(PointZ,4326)    |           | not null | 
Indexes:
    "images_a_pkey" PRIMARY KEY, btree (image_id)
    "images_a_created_at_idx" btree (created_at)
    "images_a_h3_cell_idx" btree (h3_cell)

查询如下

h3_cells AS (
    SELECT UNNEST(h3_linestring_to_cells(:line_string, 13, 1)) AS cell
)
SELECT COUNT(*)
FROM images
JOIN h3_cells hc ON images.h3_cell = hc.cell

该h3_linestring_to_cells()函数返回一个数组，h3index其大小在某些情况下可能高达数万个值。在下面的示例中，它返回的值约为 50,000 个。

在具有 7.8M 行的表中，计划和执行条目如下（为简洁起见，删除了数组值）：

Aggregate  (cost=347404.47..347404.48 rows=1 width=8) (actual time=74.311..74.312 rows=1 loops=1)
  Buffers: shared hit=154681 read=328
  I/O Timings: shared read=1.362
  ->  Nested Loop  (cost=0.43..346724.23 rows=272093 width=0) (actual time=0.051..74.246 rows=833 loops=1)
        Buffers: shared hit=154681 read=328
        I/O Timings: shared read=1.362
        ->  ProjectSet  (cost=0.00..256.90 rows=51377 width=8) (actual time=0.002..4.113 rows=51377 loops=1)
              ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
        ->  Index Only Scan using images_a_h3_cell_idx on images_a  (cost=0.43..6.68 rows=5 width=8) (actual time=0.001..0.001 rows=0 loops=51377)
              Index Cond: (h3_cell = (unnest('{...}'::h3index[])))
              Heap Fetches: 354
              Buffers: shared hit=154681 read=328
              I/O Timings: shared read=1.362
Planning Time: 139.421 ms
Execution Time: 74.345 ms

而在较小的 1.4M 行表上，计划和执行如下：

Aggregate  (cost=105040.78..105040.79 rows=1 width=8) (actual time=327.586..327.587 rows=1 loops=1)
  Buffers: shared hit=148358 read=6315 written=41
  I/O Timings: shared read=26.521 write=0.327
  ->  Merge Join  (cost=4791.05..104802.14 rows=95455 width=0) (actual time=321.174..327.575 rows=118 loops=1)
        Merge Cond: (ptilmi.h3_cell = (unnest('{...}'::h3index[])))
        Buffers: shared hit=148358 read=6315 written=41
        I/O Timings: shared read=26.521 write=0.327
        ->  Index Only Scan using images_b_h3_cell_idx on images_b ptilmi  (cost=0.43..95041.10 rows=1415438 width=8) (actual time=0.026..245.897 rows=964987 loops=1)
              Heap Fetches: 469832
              Buffers: shared hit=148358 read=6315 written=41
              I/O Timings: shared read=26.521 write=0.327
        ->  Sort  (cost=4790.62..4919.07 rows=51377 width=8) (actual time=11.181..13.551 rows=51390 loops=1)
              Sort Key: (unnest('{...}'::h3index[]))
              Sort Method: quicksort  Memory: 1537kB
              ->  ProjectSet  (cost=0.00..256.90 rows=51377 width=8) (actual time=0.002..3.716 rows=51377 loops=1)
                    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
Planning Time: 146.617 ms
Execution Time: 327.626 ms

对于较小的源数组（例如大小为 25,000），较小表上的计划更改为第一个（嵌套循环），并且其执行时间变得更符合预期（比较大的表更快）。

我不明白是什么促使计划改变为效率更低的计划。

请注意，我使用的是 CTE+JOIN 而不是 eg WHERE h3_cell = ANY(h3_linestring_to_cells(:line_string, 13, 1))，因为生成的数组通常很大，而且我发现在这种情况下前者通常更高效。有趣的是，对于包含 50,000 个条目的数组，这种= ANY()方法在较小的表上速度更快，而对于包含 25,000 个条目的数组，这种方法速度较慢。