PostgreSQL时间戳-索引化

Question

PostgreSQL时间戳-索引化

3

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

表定义：

(
  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 都有索引，shape 和 shape_utm 上有 gist 索引。

应该每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

该查询最终变得无法使用。

时间戳字段具有普通索引。是否有特殊类型的索引或其他内容，可以使此查询可用？

目前的查询：

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;

通过请求一些特定的时间间隔，我也可以获得可接受的查询时间。

- MortenSickel

只有三种可能性，4秒、5秒或6秒吗？还是可能存在小数秒？ - Mark Byers

1

@Mark Byers：只有三种可能，时间戳被截断到秒。- 是的，我刚刚发现使用链式或者in()运行速度更快，尽管SQL变得相当丑陋... - MortenSickel

@Pablo：可能会有记录在中间（尽管就像我描述的那样，没有），但我还需要查看在较长时间间隔内发生了什么。但无论如何，我如何找到未知秒数后的下一条记录？使用什么方法？ - MortenSickel

@MortenSickel：“我该如何在未知秒数后找到下一条记录？” 呃... 你读了Quassnoi的回答吗？ - Mark Byers

您应严肃考虑重新使用 TIMESTAMP WITHOUT TIME ZONE 和 CHAR(n) 类型。这两种类型仅用于符合标准，且都具有非常令人惊讶的语义，往往会咬到使用它们的人。如果要记录时间点，请考虑使用TIMESTAMP WITH TIME ZONE（也称为timestamptz）。在PostgreSQL中，VARCHAR(n)几乎普遍更安全和更快，没有关于尾随空格的令人惊讶的语义;许多人只使用text，在PostgreSQL中，这与没有长度约束的VARCHAR(n)相同。 - kgrittn

显示剩余2条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Quassnoi · Accepted Answer

如果您只想获取上一个记录，可以执行以下操作:

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

如果你需要获取当前时间前4到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

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