我在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
语句中,我已经可以访问希望保留的每一行,因此可以将它们存储在一个临时表中,然后在循环结束时返回临时表。
这个函数是否可以优化--特别是为了提高性能?
发布于 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)
上。相关信息:
旁白:避免使用keywords作为标识符。这是在自找麻烦。另外,实际包含timestamptz
的列timestamp
具有误导性。
https://stackoverflow.com/questions/56172750
复制相似问题