[Mobilitydb-users] Speed up tintersects function

[Esteban Zimanyi]

To test your query I created a table using MobilityDB synthetic data
generator as follows

CREATE TABLE trips AS
SELECT k, random_tgeompointseq(1, 1000, 1, 1000, '2001-01-01',
'2001-12-31', 10, 10, 1200, 1300) as trip
FROM generate_series(1, 17000) k;
-- SELECT 17000

select SUM(numInstants(trip)) from trips;
 21 259 057

This table has similar size as the one you described. In the first
experiments there are no indices.

A query similar as yours is as follows

SELECT k as id, getTime(atvalue(tintersects(trip, geometry 'Polygon((48
48,48 52,52 52,52 48,48 48))' ), true)) periods
FROM trips
WHERE intersects(trip, geometry 'Polygon((48 48,48 52,52 52,52 48,48 48))'
);

Time: 5767.409 ms (00:05.767)

I tried an alternative version of your query (but this requires the latest
version of MobilityDB in the develop branch)

SELECT k, getTime(atGeometry(trip, geometry 'Polygon((48 48,48 52,52 52, 52
48,48 48))'))
FROM trips
WHERE trip && geometry 'Polygon((48 48,48 52,52 52,52 48,48 48))';

Time: 5432.174 ms (00:05.432)

With indices the queries run faster

CREATE INDEX trips_trip_spgist_idx ON trips USING spgist(trip);

SELECT k as id, getTime(atvalue(tintersects(trip, geometry 'Polygon((48
48,48 52,52 52,52 48,48 48))' ), true)) periods
FROM trips
WHERE intersects(trip, geometry 'Polygon((48 48,48 52,52 52,52 48,48 48))'
);

Time: 3444.571 ms (00:03.445)

SELECT k, getTime(atGeometry(trip, geometry 'Polygon((48 48,48 52,52 52, 52
48,48 48))'))
FROM trips
WHERE trip && geometry 'Polygon((48 48,48 52,52 52,52 48,48 48))';

Time: 3142.270 ms (00:03.142)

We are analyzing how to speed up these queries. I have run valgrind on them
and 50 % of the execution time is spent in the calls to PostGIS in the
tintersects query whereas it is 30% in the atGeometry query.

We will continue reflecting on this and let you know whether we were able
to achieve some speed up.

Esteban
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mobilitydb-users/attachments/20210419/87e54926/attachment.html>