postgresql - 我如何创建一个有效的索引来快速检索最后一天的数据？

Question

我有一个包含 118m 行数据的表，我现在还不能对其进行分区。我希望能够快速检索过去 24 小时的数据。它的格式是:

created_at | page_id
timestampz     text

这样的事情是最好的方法吗？ (虽然得到关于 IMMUTABLE 的错误)

CREATE INDEX my_table_last_day
ON my_table (created_at)
WHERE date(created_at) = date(current_timestamp) - INTERVAL '1 day'

如果我每天获得约 20 万个新行，它会有效地更新吗？

Answer 1

只需要在 created_at::date 上有一个索引。 where 限定条件不是必需的，并且会随着 current_timestamp 的变化而做出奇怪的事情。 default Postgres B-tree index可以处理相等和范围查询。

一定要使用额外的括号。

test=> create index my_table_created_at_date on my_table((created_at::date));
CREATE INDEX

test=> analyze my_table;                                                    
ANALYZE

test=> explain select * from my_table WHERE date(created_at) = date(current_timestamp) - INTERVAL '1 day';
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Index Scan using my_table_created_at_date on my_table  (cost=0.29..8.43 rows=2 width=8)
   Index Cond: (date(created_at) = (date(CURRENT_TIMESTAMP) - '1 day'::interval))

还要确保仅在 created_at 上有一个索引，以覆盖其他非日期查询。

test=> create index my_table_created_at on my_table(created_at);
CREATE INDEX

test=> analyze my_table ;
ANALYZE

test=> explain select * from my_table WHERE created_at between (current_timestamp - INTERVAL '1 day') and current_timestamp;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Index Only Scan using my_table_created_at on my_table  (cost=0.29..4.39 rows=5 width=8)
   Index Cond: ((created_at >= (CURRENT_TIMESTAMP - '1 day'::interval)) AND (created_at <= CURRENT_TIMESTAMP))