加速数据在两个日期之间的PostgreSQL查询

Question

我有一个很大的表(> 50m行)，其中包含一些带有ID和时间戳的数据：

id, timestamp, data1, ..., dataN

在(id, timestamp)上...with多列索引。

我需要查询表以选择具有特定ID的所有行，其中时间戳在两个日期之间，我目前正在使用以下命令执行此操作：

SELECT * FROM mytable WHERE id = x AND timestamp BETWEEN y AND z

这目前在一台高端机器上需要超过2分钟(2个3 3Ghz双核Xeons，带HT，16 up，2个RAID 0中的1 to驱动器)，我真的想加快速度。

我找到了this tip，它建议使用空间索引，但它给出的示例是针对IP地址的。然而，速度的提升(436秒到3秒)令人印象深刻。

如何将其与时间戳一起使用？

Answer 1

只有当您有两个列A和B并使用如下查询时，该技巧才适用：

where 'a' between A and B

这不是：

where A between 'a' and 'b'

在date(column)上使用索引，而不是在column上使用索引，可能会稍微加快速度。

Answer 2

你能为我们查询EXPLAIN吗？然后我们就知道数据库是如何执行您的查询的。那么配置是怎样的呢？shared_buffers和work_mem的设置是什么？你(或你的系统)最后一次吸尘和分析是什么时候？最后一件事，你使用的是什么操作系统和pgSQL版本？

您可以创建完美的索引，但如果没有适当的设置，数据库将无法非常有效地使用它们。

Answer 3

确保索引为TableID+TableTimestamp，然后执行如下查询：

SELECT
    ....
    FROM YourTable
    WHERE TableID=..YourID.. 
        AND TableTimestamp>=..startrange.. 
        AND TableTimestamp<=..endrange..

如果将函数应用于WHERE中的表的TableTimestamp列，将无法完全使用索引。

如果您已经在执行所有这些操作，那么您的硬件可能无法完成任务。

如果您使用的是8.2或更高版本，则应尝试：

WHERE (TableID, TableTimestamp) >= (..YourID.., ..startrange.. ) 
    and (TableID, TableTimestamp) <= (..YourID.., ..endrange..)