sql - PostgreSQL 多列索引连接与比较 ("<"和 ">") 运算符

Question

我正在尝试利用 PostgreSQL 中的多列 btree 索引来执行两个表之间令人讨厌的连接。

               Table "revision_main"
     Column     |          Type          | Modifiers 
----------------+------------------------+-----------
 revision_id    | integer                | 
 page_id        | integer                | 

Indexes:
    "revision_main_pkey" UNIQUE, btree (revision_id)
    "revision_main_cluster_idx" btree (page_id, "timestamp") CLUSTER

此表包含对 wiki 中页面的修订(约 3 亿行)。我的表中还有更多列，但在本示例中我已将它们丢弃，因为它们无关紧要。

               Table "revert"
       Column       |  Type   | Modifiers 
--------------------+---------+-----------
 page_id            | integer | 
 revision_id        | integer | 
 reverted_to        | integer | 
Indexes:
    "revert_page_between_idx" btree (page_id, reverted_to, revision_id) CLUSTER

此表包含还原修订(约 2200 万行)。如果修订已被还原，则 revision_id 将在 revision_main 表中有一行，其 revision_id 将介于 reverted_to 和 revision_id 之间，并共享相同的 page_id。 (如果您好奇，请参阅 http://en.wikipedia.org/wiki/Wikipedia:Revert。)

连接这两个表以获得恢复的修订似乎很简单。这是我想出的:

explain SELECT
    r.revision_id,
    rvt.revision_id
FROM revision_main r
INNER JOIN revert rvt 
    ON r.page_id = rvt.page_id 
    AND r.revision_id > rvt.reverted_to
    AND r.revision_id < rvt.revision_id;
                                       QUERY PLAN                                               
----------------------------------------------------------------------------------------------------
 Merge Join  (cost=4202878.87..15927491478.57 rows=88418194298 width=8)
   Merge Cond: (r.page_id = rvt.page_id)
   Join Filter: ((r.revision_id > rvt.reverted_to) AND (r.revision_id < rvt.revision_id))
   ->  Index Scan using revision_main_page_id_idx on revision_main r  (cost=0.00..9740790.61 rows=223163392 width=8)
   ->  Materialize  (cost=4201592.06..4536465.21 rows=26789852 width=12)
         ->  Sort  (cost=4201592.06..4268566.69 rows=26789852 width=12)
               Sort Key: rvt.page_id
               ->  Seq Scan on revert rvt  (cost=0.00..438534.52 rows=26789852 width=12)

即使恢复时的聚集索引应该是 Btree 索引(因此支持比较运算符，如“<”和“>”)，查询优化器不会使用索引进行连接，并且“解释”会预测总成本超过 150 亿(明年可能完成)。

是否无法将比较运算符用于多列(btree)索引？我只是做错了吗？

Answer 1

看起来优化器比您更了解它的工作。

如果您选择的不仅仅是表的一小部分(这部分取决于硬件，比如说 5%)，那么选择和排序整个表比使用索引更快。如果您只是选择几行，那么它应该使用索引。因此它为您的数据提供了正确的查询计划。

至于总成本，这些数字都是废话，只有在单个查询中相互比较时才有用。 (两个非常相似的查询产生的总成本可能在非常不同的规模上。)执行时间和查询成本几乎无关。