优化函数，提取时间戳间隔最小的记录
我在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语句中，我已经可以访问希望保留的每一行，因此可以将它们存储在一个临时表中，然后在循环结束时返回临时表。
这个函数是否可以优化--特别是为了提高性能？
回答 1
Stack Overflow用户
发布于 2019-05-17 06:53:07
查询
你的函数有各种昂贵的，不必要的开销。一次查询应该比快很多倍，做同样的事情：
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函数或没有任何包装器的裸SELECT (可能是预准备语句？Example.)。
注意starttime和endtime是如何作为timestamp传递的。(作为text和cast传递没有任何意义。)并且最小间隔min_interval是实际的interval。传递您选择的任何间隔。
索引
如果mmsi上的谓词在任何方面都是选择性的，那么您当前拥有的两个索引( (posid, mmsi)上的PK和(timestamp)上的idx )都不是很有用。如果您将PK的列顺序颠倒为(mmsi, posid)，那么它对于手头的查询将变得更加有用。请参见：
这方面的最佳索引通常是在vessel_position(mmsi, timestamp)上。相关信息：
- Multicolumn index and performance
- PostgreSQL performance with (col = value or col is NULL)
- Query does not hit the index - are these the proper columns to index?
旁白:避免使用keywords作为标识符。这是在自找麻烦。另外，实际包含timestamptz的列timestamp具有误导性。
https://stackoverflow.com/questions/56172750
复制相似问题
腾讯云开发者
