php - PostgreSQL 遍历数百万条记录？

Question

我有一个表“location_signals”，其中包含大约 2.5 亿条记录，并且在 ID 字段上有一个索引。该表具有 ID、时间戳、纬度和经度(以及其他几个未使用的列)。每个 ID 可能有数千个具有不同时间戳的纬度/经度条目。我有一个传递 ID 的例程，它获取该 ID 的所有记录，并返回一个具有最高 Lat/Lon 浓度的圆。

当我们有一万条记录时，上面的方法工作得很好，但是当我们导入更多数据时，它就无法再处理了。逻辑如下:

• 使用“select ID from location_signals group by ID”(仅限 1500 个)选择唯一 ID，这里的一个问题是数据时间戳不是理想格式，因此正在这样做:

     SELECT TO_TIMESTAMP(timestamp, 'MM/DD/YY HH24:MI') AS ts, * 
      FROM  location_signals 
      WHERE (
      extract(hour from TO_TIMESTAMP(timestamp, 'MM/DD/YY HH24:MI')) > '18' 
      OR  extract(hour from TO_TIMESTAMP(timestamp, 'MM/DD/YY HH24:MI')) < '06'  
      )
      AND \"DID\" = '$did'
  
      LIMIT 1500
  

• 将 ID 传递给例程

我认为这个问题是因为循环了数百万个时间戳很重要，或者可能是其他原因。任何输入将不胜感激。也许也分批做事？如果是，怎么办？

Answer 1

您真的应该将 timestamp 列修复为真正的时间戳。将日期/时间值存储为字符串是一个非常糟糕的主意。将列命名为就好像它们存储为正确的数据类型一样——好吧，在我看来，这近乎不当行为。

因为你想在一个时间范围内搜索，你可以在一个表达式上创建一个索引:

create index idx_location_signals_id_time on location_signals(did, to_timestamp(timestamp, 'MM/DD/YY HH24:MI')::time));

然后您可以将您的where 逻辑表达为:

where did = ? and
      (to_timestamp(timestamp, 'MM/DD/YY HH24:MI')::time >= '19:00:00'::time or
       to_timestamp(timestamp, 'MM/DD/YY HH24:MI')::time < '06:00:00'::time
      )

这应该使用完整表达式的索引。