索引文本日期是否有任何缺点，例如 2018-06-03 以提高 postgresql 中的日期范围查询性能?

Question

请考虑以下数据库模式

my_primary_id (text) primary index
my_date (timestamp with timezone)

有没有办法索引my_date以便我可以快速查询日期范围？

我的第一个想法是将my_date作为二级索引，但是经过一番思考，如果我每天有 100k 到 200k 个项目，my_date索引的基数将与我拥有的行数相似。

由于大索引表 ->较慢的查询，我想也许我应该存储一个额外的列

`yyyy-mm-dd`

并索引它？

如果我能保证我所做的日期范围查询返回的不超过表大小的 5%(使用 seq 扫描阻止它(有什么缺点吗？

我的查询模式如下

select * from my_table
where my_date >= my_start_date and my_date < my_end_date

Answer 1

您可以通过将时间戳的日期部分强制转换为"日期：

create index on the_table (my_date::date);

若要使查询使用该索引，需要在查询中使用相同的表达式：

select *
from my_table
where my_date::date >= date '2018-01-01'
and my_date::date < date '2018-02-01';

我认为时间戳列上的索引也应该可用，如果您将列与时间戳值进行比较：

select *
from my_table
where my_date >= timestamp '2018-01-01 00:00:00'
and my_date < timestamp '2018-02-01 00:00:00';

Answer 2

您可以按日期对表进行分区。如果您按日期顺序排列有数百万条记录并且只需要处理子集，这将极大地加快查询速度。

https://www.postgresql.org/docs/current/static/ddl-partitioning.html