查看表格时间戳字段中的唯一日期的最快方法？

Question

我有一张数十亿行的表格。 “录制”字段上有每日分区，它是“没有时区的时间戳”。我想知道哪些日子在桌子上。我知道我可以做这样的事情：

SELECT recorded::date 
FROM table 
GROUP BY 1; 

在理想情况下应该可行，但在解释上是相当高的，并指出这将需要一段时间的工作......如果这是我能做到的最好，我可以接受这一点（并且我们可以随时关注这些数据），但是我想知道是否可以采用更有效的方式来实现这一点，因为我每天都进行分区？

Answer 1

您可以创建一个索引是这样的：

create index your_index_name 
on table (date_trunc('day', recorded)) 

在我的测试，PostgreSQL的9.something使用顺序扫描后简单地索引“中记载的”列添加索引，按顺序进行扫描前，和使用date_trunc（）将其索引后进行索引扫描。选择一天的行需要66ms没有索引，68ms使用普通索引，13ms使用date_trunc（）索引。

随着数十亿行，预计创建该索引需要几分钟。 （咳嗽）

Answer 2

有一个非常类似的线程在这里：

Slow select distinct query on postgres

如果你知道最小/最大日期，你会比做一个序列扫描过更好的查询对日期的列表整桌子。假设你看起来像这应该是快上记录的索引，东西：

with days as (
select date_trunc('day', min(recorded))::date + k * interval '1 day' as day 
from records, 
    generate_series(0, 
        (select date_trunc('day', max(recorded))::date 
          - date_trunc('day', min(recorded)::date 
        from records 
    )) as k 
) 
select day 
from days 
where exists (
     select 1 
     from records 
     where day <= recorded and recorded < day + interval '1 day' 
    ); 

可能会有一些调整做上面的查询，但总的想法是存在的：它会更快在索引字段上执行几千个子查询/索引扫描，而不是扫描几十亿行并聚合它们以便识别不同日子。