Poor query performance on postgis 3.5 builds #679
Description
Describe the bug
Hi there! Just testing newer mobilitydb 1.3 features using public docker images and noticed query performance on 3.5-master is very poor. For instance a simple query testing if an arbitrary shape eIntersects a GIST indexed tgeompoint column on 3.4-master/latest takes <500ms, but on 3.5-master takes >>500'000ms on the same hardware. Have confirmed this behaviour on both postgres 16 and 17 images.
Assuming this is either related to the newer postgis 3.5 version or instability of master, but thought I'd bring it to moblitydb developers' attention. Looking forward to stable 1.3 release. 😄
To Reproduce
Steps to reproduce the behavior:
docker run -p 25432:5432 mobilitydb/mobilitydb:17-3.5-master- Statements to insert 10000 rows of dummy data (sequences of 300 random points)
CREATE EXTENSION IF NOT EXISTS mobilitydb;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE test_paths (
id SERIAL PRIMARY KEY,
path tgeompoint
);
CREATE INDEX idx_test_paths_spatial ON test_paths USING GIST (path);DO $$
DECLARE
i INT;
j INT;
geom_str TEXT;
t TIMESTAMPTZ;
lon DOUBLE PRECISION;
lat DOUBLE PRECISION;
BEGIN
FOR i IN 1..10000 LOOP
geom_str := '';
t := clock_timestamp();
FOR j IN 0..299 LOOP
lon := random() * 360 - 180; -- Longitude range
lat := random() * 180 - 90; -- Latitude range
geom_str := geom_str || format(
'Point(%s %s)@%s',
lon,
lat,
(t + (j * interval '1 second'))::TEXT
);
IF j < 299 THEN
geom_str := geom_str || ', ';
END IF;
END LOOP;
EXECUTE format(
'INSERT INTO test_paths(path) VALUES ((''[%s]''))',
geom_str
);
END LOOP;
END $$;- Query intersect
WITH shape AS (
SELECT ST_MakeEnvelope(-10, 10, -20, 20) AS geom
) SELECT id
FROM test_paths, shape
WHERE eIntersects(path, shape.geom)- Compare performance of same query to
17-master-3.4orlatestimage
Expected behavior
Would expect similar performance between postgis versions.
Specifications (please complete the following information):
SELECT version();
-- PostgreSQL 17.5 (Debian 17.5-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
SELECT postgis_full_version();
-- POSTGIS=""3.5.2 dea6d0a"" [EXTENSION] PGSQL=""170"" GEOS=""3.9.0-CAPI-1.16.2"" PROJ=""7.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org/ USER_WRITABLE_DIRECTORY=/var/lib/postgresql/.local/share/proj DATABASE_PATH=/usr/share/proj/proj.db"" (compiled against PROJ 7.2.1) LIBXML=""2.9.10"" LIBJSON=""0.15"" LIBPROTOBUF=""1.3.3"" WAGYU=""0.5.0 (Internal)"" TOPOLOGY
SELECT mobilitydb_full_version();
-- MobilityDB 1.3.0, PostgreSQL 17.5 (Debian 17.5-1.pgdg110+1), PostGIS 3.5.2, GEOS 3.9.0-CAPI-1.16.2, PROJ 7.2.1, JSON-C 0.15, GSL 2.6