在PostgreSQL中，从时间戳中提取日期的最有效方法是什么？

Question

如果您遇到表单相关的问题

select * from foo
where created_on::date = '2014/1/1'

或者

select * from foo
where date_trunc('day', created_on) = '2014/1/1'

或者

select * from foo
where date(created_on) = '2014/1/1'

在什么情况下，不同的查询会表现得更好/更差？这三种选项中哪一种最高效？

- avyfain

---

你需要查看执行计划才能确定，但一般来说，像你第二个和第三个示例中那样在表列中使用函数会阻止任何索引使用，这将使你降到表扫描。 - Brandon

第四个可能性（待测试）是“select * from foo where created_on between '2014/1/1 00:00:00' and '2014/1/2 00:00:00'”。 - Brandon

@Brandon：第一个表达式也会防止使用索引。 - user330315

1和3是相同的；date()是用于实现::date转换的函数。请参见：SELECT castfunc :: regprocedure FROM pg_cast WHERE castsource ='timestamp' :: regtype AND casttarget ='date' :: regtype。 - Nick Barnes

@Brandon的解决方案是唯一一个可以使用timestamp索引的，但请记住，BETWEEN包括其两个端点。您可能希望改用显式的>= / <比较。 - Nick Barnes

Answer 1

总结评论，您的第一和第三种解决方案是相同的。根据@Nick Barnes的说法，将其转换为日期只是使用了date函数。

这些选项以及第二个选项都需要对表中的每一行运行一个函数，因此即使您有索引，也无法使用它。

假设在created_on上有索引，那么这是您最好的选择:

select * from foo
where created_on >= '2014/1/1 00:00:00'
  and created_on < '2014/1/2 00:00:00';

- Brandon

---

如果我根据月份分区数据，我相信它不会扫描所有的分区，对吗？ - uhs