PostgreSQL时间戳-索引

Question

我正在运行一个查询，其中我查找一条记录，并在一段时间后再查找另一条记录。

表定义：

(
  id integer primary key,
  gpsstatus character(2),
  datetime timestamp without time zone,
  lat numeric(9,6),
  lon numeric(9,6),
  alt numeric(9,4),
  time integer,
  datafileid integer,
  shape geometry,
  speed double precision,
  dist double precision,
  shape_utm geometry,
  lokalitet character(128),
  cowid integer
)

有datetime，lokalitet，cowid，gpsstatus，gist-index on shape和shape_utm的索引。

这些点应该每隔5秒采样一次，所以我试着这样做

select <something more>,p1.timestamp 
from table p1, table p2 
where p1.timestamp + interval '5 secound' = p2.timestamp

这运行得相当快，但后来我发现，由于采样中的抖动，我丢失了相当多的点，因此这些点可能相隔4到6秒。

然后我试着：

where    (p2.timestamp, interval'0 second')
overlaps (p1.timestamp + interval '4 second', interval '2 second')

这花了很长时间。我还尝试了更简单的解决方案：

WHERE p1.timestamp + interval '4 second' <= p2.timestamp
AND   p1.timestamp + interval '6 second' >= p2.timestamp

这也最终导致了无用的缓慢。

timestamp字段具有普通索引。有没有一种特殊类型的索引可以使这个查询变得有用？

此时的查询：

SELECT
    p1.cowid,
    p1.datetime,
    st_distance(p1.shape_utm, lead(p1.shape_utm)
      OVER (ORDER BY p1.datetime)) AS meters_obs,
    st_distance(p1.shape_utm, lead(p1.shape_utm, 720)
      OVER (ORDER BY p1.datetime)) AS meters_hour,
    observation.observation
  FROM (gpspoint p1 LEFT JOIN observation
                           ON (observation.gpspointid = p1.id)),
       status
  WHERE p1.gpsstatus = status.id
    AND status.use = true;

我也可以通过询问一些特定的时间间隔来获得一个可接受的查询时间。

Answer 1

如果你只想要上一条记录，你可以这样做：

SELECT  p, LAG(p) OVER (ORDER BY timestamp) AS pp
FROM    table p
ORDER BY
        timestamp

如果您需要在当前秒数之前使用记录4 to 6秒，请使用以下命令：

SELECT  p1.*, p2.*
FROM    table p1
LEFT JOIN
        table p2
ON      p2.timestamp BETWEEN p1.timestamp - '4 seconds'::INTERVAL
                         AND p1.timestamp - '6 seconds'::INTERVAL
ORDER BY
        p1.timestamp

这可能会返回多个以前的记录，如果它们都在该范围内。