我在Postgres 9.4.5中有一张时间戳:
CREATE TABLE vessel_position (
posid serial NOT NULL,
mmsi integer NOT NULL,
"timestamp" timestamp with time zone,
the_geom geometry(PointZ,4326),
CONSTRAINT "PK_posid_mmsi" PRIMARY KEY (posid, mmsi)
);
其他索引:
CREATE INDEX vessel_position_timestamp_idx ON vessel_position ("timestamp");
我想提取每行至少在上一行之后的时间戳为x分钟。我使用了几种使用LAG()
的SELECT
语句,这些语句有效,但没有给我确切的结果。以下功能为我提供了我的需求,但我觉得它可能会更快:
CREATE OR REPLACE FUNCTION _getVesslTrackWithInterval(mmsi integer, startTime character varying (25) ,endTime character varying (25), interval_min integer)
RETURNS SETOF vessel_position AS
$func$
DECLARE
count integer DEFAULT 0;
posids varchar DEFAULT '';
tbl CURSOR FOR
SELECT
posID
,EXTRACT(EPOCH FROM (timestamp - lag(timestamp) OVER (ORDER BY posid asc)))::int as diff
FROM vessel_position vp WHERE vp.mmsi = $1 AND vp.timestamp BETWEEN $2::timestamp AND $3::timestamp;
BEGIN
FOR row IN tbl
LOOP
count := coalesce(row.diff,0) + count;
IF count >= $4*60 OR count = 0 THEN
posids:= posids || row.posid || ',';
count:= 0;
END IF;
END LOOP;
RETURN QUERY EXECUTE 'SELECT * from vessel_position where posid in (' || TRIM(TRAILING ',' FROM posids) || ')';
END
$func$ LANGUAGE plpgsql;
我不禁会认为将所有posids
作为字符串,然后在最后再次选择它们正在放慢速度。在IF
语句中,我已经可以访问要保留的每一行,因此可以将它们存储在临时表中,然后在循环末端返回临时表。
可以优化此功能 - 特别提高性能?
查询
您的功能具有各种昂贵,不必要的开销。单个查询应该是多次快速,进行相同的操作:
CREATE OR REPLACE FUNCTION _get_vessel_track_with_interval
(mmsi int, starttime timestamptz, endtime timestamptz, min_interval interval)
RETURNS SETOF vessel_position AS
$func$
BEGIN
SELECT (vp).* -- parentheses required for decomposing row type
FROM (
SELECT vp -- whole row (!)
, timestamp - lag(timestamp) OVER (ORDER BY posid) AS diff
FROM vessel_position vp
WHERE vp.mmsi = $1
AND vp.timestamp >= $2 -- typically you'd include the lower bound
AND vp.timestamp < $3; -- ... and exlude the upper
ORDER BY posid
) sub
WHERE diff >= $4;
END
$func$ LANGUAGE plpgsql STABLE;
也可以是SQL功能或无包装器的Bare SELECT
(也许是准备好的语句?示例。(
请注意如何以timestamp
传递starttime
和endtime
。(以text
和Cast的方式传递毫无意义。(最小间隔min_interval
是实际的interval
。通过您选择的任何间隔。
索引
如果mmsi
上的谓词以任何方式选择性,那么您当前 ake ((posid, mmsi)
上的PK和(timestamp)
上的IDX(的两个索引不是很有用。如果将PK的列顺序转换为(mmsi, posid)
,则它对手头的查询变得更有用。请参阅:
- 复合索引也适用于第一个字段的查询吗?
最佳索引通常在vessel_position(mmsi, timestamp)
上。相关:
- 多列指数和性能
- 以(col = value或col为null( 的postgresql性能
- 查询不会达到索引 - 这些是索引的适当列吗?
旁边:避免关键字作为标识符。那是在要求麻烦。另外,实际保留timestamptz
的timestamp
列是误导的。