主页 / dba / 问题 / 59074
Accepted
Saurabh Nanda
Asked: 2014-02-18 17:32:02 +0800 CST

tsrange 上的 2 个 B-tree 索引或 1 个 GiST 索引——哪个表现更好？

  • 772

我有一个使用列存储预订数据的表starts_at&ends_at每当我查询表以查找重叠预订时，我都可以选择使用以下查询之一：

SELECT * FROM reservations
WHERE starts_at < '2014-01-03 00:00:00'
AND   ends_at   >='2014-01-01 00:00:00';

或者

SELECT * FROM reservations
WHERE tsrange(starts_at, ends_at) && ('2014-01-01 00:00:00', '2014-01-03 00:00:00')

我在starts_at和ends_at列上有常规的 B-Tree 索引，因此第一个查询总是使用它们。但是，除非我在 tsrange 上定义功能性 GiST 索引，否则第二个查询会进行完整扫描。

create index tsrange_idx on reservations using gist(tsrange(starts_at, ends_at));

我的问题是，随着表的增长，哪个索引会更快？从查询执行计划来看，答案可能很明显，但我并不精通阅读EXPLAIN ANALYZE输出。

postgresql index

1 个回答

  1. Best Answer

    带有 B 树索引的时间戳

    我建议第三种选择：只要您的表包含两timestamp列（似乎已定义），我将使用具有相反排序顺序NOT NULL的单个多列 B-tree 索引（如果没有其他注意事项适用）：

    CREATE INDEX reservations_range_idx ON reservations (starts_at, ends_at DESC);

    这些相关答案中的更多内容：

    至于查询，请查看SQL 标准运算符OVERLAPS：

    SELECT * FROM reservations
WHERE (starts_at, ends_at) OVERLAPS ('2014-01-01 00:00:00', '2014-01-03 00:00:00');

    更多关于 SO 的相关问题：

    应该比两个 B 树索引快。磁盘空间更少，维护成本更低。写操作的负担很小。

    具有 GiST 索引的范围类型

    对于大表，范围类型的 GiST 索引很可能更快，因为它可以更好地扩展。不过，磁盘上的存储空间要大得多，索引维护要贵一些。

    如果你走那条路，将时间戳存储为范围（tsrange或tstzrange）会更有效。没有功能方面的普通 GiST 索引要快一些。

    CREATE TABLE reservation (
  reservation_id serial PRIMARY KEY
, span tsrange
, ...
);
 
CREATE INDEX reservation_span_gist_idx on reservations USING GiST (span);

    使用您已经在问题中显示的&&“重叠”运算符：

    SELECT *
FROM   reservation 
WHERE  span && ('2014-01-01 00:00:00', '2014-01-03 00:00:00');

    您可能对通过设计排除重叠的排除约束感兴趣，它会自动实现像上面那样的 GiST 索引。手册中有一个代码示例。这个关于 SO 的相关答案有更多细节：

    • 18

相关问题