时间戳

首页 > 解决方案 > 如何在 Postgres 中正确索引和查询时间序列数据？

postgresql - 如何在 Postgres 中正确索引和查询时间序列数据？

问题描述

我最近正在尝试使用 Postgres 而不是使用 Bigquery。

我的表transactions_all结构

access_id (text)
serial_no (text)
transaction_id (text)
date_local (timestamp)

和index (BTREE, condition (((date_local)::date), serial_no))

当我将一个月的 500.000 行加载到该表中时，像这样查询最近 2 天的性能还可以

SELECT * 
FROM transactions_all 
WHERE DATE(date_local) BETWEEN CURRENT_DATE - INTERVAL '1 day'  AND CURRENT_DATE 
 AND access_id = 'accessid1' 
 and serial_no = 'IO99267637'

但是如果我选择像这样的过去 21 天

SELECT * 
FROM transactions_all 
WHERE DATE(date_local) BETWEEN CURRENT_DATE - INTERVAL '20 day'  AND CURRENT_DATE 
  AND access_id = 'accessid1' 
  and serial_no = 'IO99267637'

然后获取数据需要几秒钟而不是几毫秒。

这是正常行为还是我使用了错误的索引？

标签： postgresql

解决方案

您的索引列的顺序错误。=在前面，您需要与条件中的运算符一起使用的那些表达式WHERE，因为不能再使用与不同运算符一起使用的列之后的索引列。

为了理解这一点，想象一个电话簿，其中的名字是按（姓氏，名字）排序的。然后考虑很容易找到姓“Miller”和名字小于“J”的所有条目：您只需阅读“Miller”条目，直到您点击“J”。现在考虑任务是查找所有“Joe”的全名小于“M”：您必须扫描直到“M”的所有条目，并且对名字也进行排序对您没有多大帮助.

所以使用像这样的索引

CREATE INDEX ON transactions_all (serial_no, access_id, date(date_local));

推荐阅读