postgresql - 强制 Postgresql 使用 Merge Append

Question

假设我有以下表格和索引：

 create table inbound_messages(id int, user_id int, received_at timestamp);
 create table outbound_messages(id int, user_id int, sent_at timestamp);
 create index on inbound_messages(user_id, received_at);
 create index on outbound_messages(user_id, sent_at);

现在我想为用户提取最后 20 条消息，无论是在特定时间范围内的入站消息还是出站消息。我可以执行以下操作，从解释来看，PG 似乎以“并行”方式返回两个索引，因此它最大限度地减少了需要扫描的行数。

explain select * from (select id, user_id, received_at as time from inbound_messages union all select id, user_id, sent_at as time from outbound_messages) x where user_id = 5 and time between '2018-01-01' and '2020-01-01' order by user_id,time desc limit 20;

 Limit  (cost=0.32..16.37 rows=2 width=16)
   ->  Merge Append  (cost=0.32..16.37 rows=2 width=16)
         Sort Key: inbound_messages.received_at DESC
         ->  Index Scan Backward using inbound_messages_user_id_received_at_idx on inbound_messages  (cost=0.15..8.17 rows=1 width=16)
               Index Cond: ((user_id = 5) AND (received_at >= '2018-01-01 00:00:00'::timestamp without time zone) AND (received_at <= '2020-01-01 00:00:00'::timestamp without time zone))
         ->  Index Scan Backward using outbound_messages_user_id_sent_at_idx on outbound_messages  (cost=0.15..8.17 rows=1 width=16)
               Index Cond: ((user_id = 5) AND (sent_at >= '2018-01-01 00:00:00'::timestamp without time zone) AND (sent_at <= '2020-01-01 00:00:00'::timestamp without time zone))

例如，它可以做一些疯狂的事情，比如在内存中找到所有匹配的行，然后对这些行进行排序。假设有数百万个匹配行，那么这可能需要很长时间。但是因为它以相同的顺序遍历索引，所以我们希望结果是一个快速的操作。看起来“合并追加”操作是懒惰地完成的，它实际上并没有实现所有匹配的行。

现在我们可以看到 postgres 支持对两个不同的表进行此操作，但是是否可以强制 Postgres 对单个表使用此优化。

可以说我想要最后 20 个inbound messages用于user_id = 5or user_id = 6。

explain select * from inbound_messages where user_id in (6,7) order by received_at desc limit 20; 

然后我们得到一个执行位图堆扫描的查询计划，然后执行内存排序。因此，如果有数百万条消息匹配，那么它将查看数百万行，即使理论上它可以使用相同的合并技巧只查看几行。

 Limit  (cost=15.04..15.09 rows=18 width=16)
   ->  Sort  (cost=15.04..15.09 rows=18 width=16)
         Sort Key: received_at DESC
         ->  Bitmap Heap Scan on inbound_messages  (cost=4.44..14.67 rows=18 width=16)
               Recheck Cond: (user_id = ANY ('{6,7}'::integer[]))
               ->  Bitmap Index Scan on inbound_messages_user_id_received_at_idx  (cost=0.00..4.44 rows=18 width=0)
                     Index Cond: (user_id = ANY ('{6,7}'::integer[]))

我们可以考虑(received_at)在表上添加一个索引，然后它会执行相同的反向扫描。但是，如果我们有大量用户，那么我们就会错过潜在的大幅加速，因为我们正在扫描大量与查询不匹配的索引条目。

标签： postgresql