考虑具有三列的项目的示例表：

• id（UUID，主键）
• time（时间戳）
• store_id（UUID，外键）

在(store_id, time) INCLUDING (id)上有一个覆盖B树索引。

查找top-N项

我想写一个查询，可以有效地获取指定商店集合中的20个最新商品。每个store_id可能有数万或数十万行。这是一个相对简单的查询，可以产生正确的结果：

SELECT id, time
FROM items
WHERE store_id = ANY(ARRAY[
  'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
  'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb',
  ...
]::uuid[])
ORDER BY time DESC
LIMIT 20;

然而，根据EXPLAIN (ANALYZE, BUFFERS)的查询计划，尽管有LIMIT 20子句，Postgres还是读取了超过一万个缓冲区页面。即使使用SSD，这也很慢。似乎发生的是Postgres在排序之前读取所有索引条目。

查询计划

这个查询计划是在这个问题第一次发布后生成的，我已经执行了一些其他的数据库优化，如重新索引和清理。现在查询运行得相当快，但仍然访问超过7900个缓冲区页面。

Limit  (cost=553.97..554.02 rows=20 width=56) (actual time=214.618..214.624 rows=21 loops=1)
   Output: id, time
   Buffers: shared hit=7606 read=369 dirtied=1
   I/O Timings: read=205.188
   ->  Sort  (cost=553.97..574.15 rows=8073 width=56) (actual time=214.617..214.620 rows=20 loops=1)
         Output: id, time
         Sort Key: items.time DESC
         Sort Method: top-N heapsort  Memory: 28kB
         Buffers: shared hit=7606 read=369 dirtied=1
         I/O Timings: read=205.188
         ->  Index Only Scan using items_store_id_time_covering_id_idx on public.items  (cost=0.43..336.31 rows=8073 width=56) (actual time=0.730..211.783 rows=11920 loops=1)
               Output: id, time
               Index Cond: (items.store_id = ANY ('{aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa,bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb, ...}'::uuid[]))
               Heap Fetches: 282
               Buffers: shared hit=7606 read=369 dirtied=1
               I/O Timings: read=205.188
 Settings: effective_cache_size = '3052336kB', random_page_cost = '1.1'
 Query Identifier: -1648601102884428975
 Planning Time: 0.160 ms
 Execution Time: 214.647 ms

横向连接-速度提高50- 100倍

一个非常有用的解决方法是使用横向连接来迭代地加载每个store_id中最新的20个条目，然后将最新的20个条目全部加载。这只加载了几百个缓冲区页面，对于我的测试工作负载来说，效率提高了50- 100倍！

SELECT id, time
FROM unnest(ARRAY[
  'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
  'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb',
  ...
  ]::uuid[]) AS store_ids(store_id)
JOIN LATERAL (
  SELECT id, time
  FROM items
  WHERE store_id = store_ids.store_id
  ORDER BY time DESC
  LIMIT 20
) ON TRUE
ORDER BY time DESC
LIMIT 20;

查询计划

这个查询计划要高效得多，可以访问119个缓冲页。

Limit  (cost=26.21..26.26 rows=20 width=24) (actual time=0.284..0.287 rows=20 loops=1)
   Output: items.id, items.time
   Buffers: shared hit=119
   ->  Sort  (cost=26.21..26.76 rows=220 width=24) (actual time=0.283..0.285 rows=20 loops=1)
         Output: items.id, items.time
         Sort Key: items.time DESC
         Sort Method: top-N heapsort  Memory: 27kB
         Buffers: shared hit=119
         ->  Nested Loop  (cost=0.43..20.35 rows=220 width=24) (actual time=0.055..0.247 rows=200 loops=1)
               Output: items.id, items.time
               Buffers: shared hit=119
               ->  Function Scan on pg_catalog.unnest store_ids  (cost=0.00..0.11 rows=11 width=16) (actual time=0.005..0.006 rows=11 loops=1)
                     Output: store_ids.store_id
                     Function Call: unnest('{aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa,bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb,...}'::uuid[])
               ->  Limit  (cost=0.43..1.44 rows=20 width=24) (actual time=0.011..0.019 rows=18 loops=11)
                     Output: items.id, items.time
                     Buffers: shared hit=119
                     ->  Index Only Scan Backward using items_store_id_time_covering_id_idx on public.items  (cost=0.43..5.48 rows=100 width=24) (actual time=0.010..0.017 rows=18 loops=11)
                           Output: items.id, items.time
                           Index Cond: (items.store_id = store_ids.store_id)
                           Heap Fetches: 20
                           Buffers: shared hit=119
 Settings: effective_cache_size = '3052336kB', random_page_cost = '1.1'
 Query Identifier: -8987254562252190725
 Planning:
   Buffers: shared hit=28
 Planning Time: 0.254 ms
 Execution Time: 0.321 ms

然而，一个更聪明的查询计划是从每个store_id中按顺序加载20个项目，只保留最新的time列的20个项目。我也更喜欢声明式SQL而不是命令式SQL（特别是横向连接的for-each性质），以给予查询计划器更多的控制权，理想情况下可以获得更好的性能。

其他尝试

我还尝试了两种替代方法，使用ROW_NUMBER()和simulated loose indexscan，这两种方法在这种情况下都表现不佳，因为Postgres仍然读取超过一万个缓冲区页面。
有没有一种（简单）的方法让Postgres生成一个加载和排序最少行数的查询计划？查询计划器和执行器是否能够实现上面描述的“更智能的查询计划”？