performance - 在 postgres 中索引日期字段的推荐方法？

Question

我有几个大约有 1700 万行的表，它们都有一个我希望能够经常用于搜索的日期列。我正在考虑要么只在该列上添加一个索引，看看事情进展如何，要么将项目作为一次性操作按日期排序，然后将所有内容插入到一个新表中，以便主键随着日期的增加而增加。

由于这些都非常耗时，我认为首先在这里询问输入可能是值得的。

我的最终目标是将 sql 查询加载到 pandas 中以进行一些分析(如果相关的话)。

Answer 1

当您要在表中搜索给定日期时，日期列上的索引很有意义，例如:

select * from test
where the_date = '2016-01-01';
-- or

select * from test
where the_date between '2016-01-01' and '2016-01-31';
-- etc

在这些查询中，无论主键和日期列的排序顺序是否相同。因此将数据重写到新表将是无用的。只需创建一个索引。

但是，如果您打算仅在 ORDER BY 中使用索引:

select * from test
order by the_date;

那么主键整数索引可能比日期列上的索引快得多(2-4 倍)。