[postgis-devel] Fwd: Feature request in postgis 3.0
Esteban Zimanyi
ezimanyi at ulb.ac.be
Wed Dec 19 05:27:16 PST 2018
Dear all,
I fully support the need for partitioned tables. As some of you may know,
we are working on trajectories, i.e., spatio-temporal data, e.g., coming
from GPS readings. For example, using the BerlinMOD benchmark for moving
object databases
http://dna.fernuni-hagen.de/secondo/BerlinMOD/BerlinMOD.html
we compared the performance of the benchmark queries with partitioned vs
unpartitioned tables. The car trips are stored in either a single table
AllTrips or a partitioned table Trips where the partition is by day. The
experiments were conducted on a machine with the following characteristics
CPU: 2x Intel Xeon E5-2640 v4
RAM: 128GB
HDD: 500GB NVMe SSD
OS: Debian 9 "stretch" (kernel 4.9.0-8-amd64)
PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian
6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
POSTGIS="2.5.1 r17027" [EXTENSION] PGSQL="110" GEOS="3.5.1-CAPI-1.9.1
r4246" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.1.2, released
2016/10/24" LIBXML="2.9.4" LIBJSON="0.12.1" RASTER
The four scale factors (SF) used were as follows
SF 0.005: 1797 trips over 4 days/partitions
SF 0.05: 15045 trips over 9 days/partitions
SF 0.2: 62510 trips over 16 days/partitions
SF 1.0: 292940 trips over 31 days/partitions
We give next the results for Query 6 of the benchmark
Query 6: What are the pairs of trucks that have ever been as close as 10 m
or less to each other?
which in MobilityDB is written as follows
SELECT DISTINCT C1.Licence AS Licence1, C2.Licence AS Licence2
FROM Trips T1, Cars C1, Trips T2, Cars C2
WHERE T1.CarId = C1.CarId AND T2.CarId = C2.CarId
AND T1.CarId < T2.CarId AND C1.Type = 'truck' AND C2.Type = 'truck'
AND expandSpatial(T1.Trip, 10) && expandSpatial(T2.Trip, 10)
AND tdwithin(T1.Trip, T2.Trip, 10.0) &= true
ORDER BY C1.Licence, C2.Licence
---------------------------------------------------
Trips
SF 0.005
---------
Planning Time: 1.475 ms
Execution Time: 553.243 ms
Unique (cost=1163.94..1165.62 rows=1 width=164) (actual
time=553.124..553.124 rows=1 loops=1)
-> Sort (cost=1163.94..1164.50 rows=224 width=164) (actual
time=553.123..553.123 rows=1 loops=1)
Sort Key: c1.licence, c2.licence
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=8.36..1155.19 rows=224 width=164) (actual
time=474.755..553.114 rows=1 loops=1)
Join Filter: ((t1.carid < t2.carid) AND (tdwithin(t1.trip,
t2.trip, '10'::double precision) &= true) AND (expandspatial(t1.trip,
'10'::double precision) && expandspatial(t2.trip, '10'::double precision)))
Rows Removed by Join Filter: 6888
[...]
SF 0.05
---------
Planning Time: 1.589 ms
Execution Time: 7303.452 ms
Sort (cost=16790.28..16790.29 rows=4 width=164) (actual
time=7292.983..7292.985 rows=22 loops=1)
Sort Key: c1.licence, c2.licence
Sort Method: quicksort Memory: 26kB
-> HashAggregate (cost=16790.20..16790.24 rows=4 width=164) (actual
time=7292.914..7292.918 rows=22 loops=1)
Group Key: c1.licence, c2.licence
-> Gather (cost=6095.47..16736.40 rows=10760 width=164) (actual
time=496.630..7296.353 rows=78 loops=1)
Workers Planned: 4
Workers Launched: 4
[...]
SF 0.2
---------
Planning Time: 2.378 ms
Execution Time: 95450.940 ms
Sort (cost=222692.07..222692.11 rows=16 width=164) (actual
time=95434.894..95434.898 rows=108 loops=1)
Sort Key: c1.licence, c2.licence
Sort Method: quicksort Memory: 33kB
-> HashAggregate (cost=222691.59..222691.75 rows=16 width=164) (actual
time=95434.711..95434.740 rows=108 loops=1)
Group Key: c1.licence, c2.licence
-> Gather (cost=24786.84..221690.44 rows=200230 width=164)
(actual time=373.478..95450.026 rows=524 loops=1)
Workers Planned: 4
Workers Launched: 4
[...]
SF 1.0
---------
Planning Time: 3.813 ms
Execution Time: 1171259.212 ms
Sort (cost=103497057.49..103497072.70 rows=6084 width=16) (actual
time=1170553.544..1170553.572 rows=644 loops=1)
Sort Key: c1.licence, c2.licence
Sort Method: quicksort Memory: 75kB
-> HashAggregate (cost=103496614.25..103496675.09 rows=6084 width=16)
(actual time=1170551.974..1170552.187 rows=644
loops=1)
Group Key: c1.licence, c2.licence
-> Gather (cost=264285.07..101835734.56 rows=332175937 width=16)
(actual time=6021.547..1171251.922 rows=4080 loops=1)
Workers Planned: 5
Workers Launched: 5
[...]
---------------------------------------------------
AllTrips
SF 0.005
---------
Planning Time: 2.359 ms
Execution Time: 553.953 ms
Unique (cost=85.40..85.42 rows=1 width=164) (actual time=553.834..553.834
rows=1 loops=1)
-> Sort (cost=85.40..85.41 rows=2 width=164) (actual
time=553.833..553.833 rows=1 loops=1)
Sort Key: c1.licence, c2.licence
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=8.69..85.39 rows=2 width=164) (actual
time=460.713..553.804 rows=1 loops=1)
Join Filter: ((t1.carid < t2.carid) AND (tdwithin(t1.trip,
t2.trip, '10'::double precision) &= true) AND (expandspatial(t1.trip,
'10'::double precision) && expandspatial(t2.trip, '10'::double precision)))
Rows Removed by Join Filter: 6888
[...]
SF 0.05
---------
Planning Time: 0.440 ms
Execution Time: 35537.814 ms
Unique (cost=1222.46..1222.83 rows=4 width=164) (actual
time=35405.795..35405.813 rows=22 loops=1)
-> Sort (cost=1222.46..1222.58 rows=50 width=164) (actual
time=35405.794..35405.798 rows=78 loops=1)
Sort Key: c1.licence, c2.licence
Sort Method: quicksort Memory: 31kB
-> Nested Loop (cost=9.73..1221.05 rows=50 width=164) (actual
time=3265.734..35405.675 rows=78 loops=1)
Join Filter: ((t1.carid < t2.carid) AND (tdwithin(t1.trip,
t2.trip, '10'::double precision) &= true) AND (expandspatial(t1.trip,
'10'::double precision) && expandspatial(t2.trip, '10'::double precision)))
Rows Removed by Join Filter: 534283
[...]
SF 0.2
---------
Planning Time: 0.555 ms
Execution Time: 478382.227 ms
Unique (cost=10970.86..10977.38 rows=16 width=164) (actual
time=478382.024..478382.122 rows=108 loops=1)
-> Sort (cost=10970.86..10973.03 rows=870 width=164) (actual
time=478382.023..478382.047 rows=524 loops=1)
Sort Key: c1.licence, c2.licence
Sort Method: quicksort Memory: 65kB
-> Nested Loop (cost=29.68..10928.38 rows=870 width=164) (actual
time=1020.236..478380.983 rows=524 loops=1)
Join Filter: ((t1.carid < t2.carid) AND (tdwithin(t1.trip,
t2.trip, '10'::double precision) &= true) AND (expandspatial(t1.trip,
'10'::double precision) && expandspatial(t2.trip, '10'::double precision)))
Rows Removed by Join Filter: 8672501
[...]
SF 1.0
---------
Planning Time: 0.570 ms
Execution Time: 1381164.728 ms
Sort (cost=773727.61..773742.82 rows=6084 width=16) (actual
time=1380600.506..1380600.534 rows=644 loops=1)
Sort Key: c1.licence, c2.licence
Sort Method: quicksort Memory: 75kB
-> HashAggregate (cost=773284.37..773345.21 rows=6084 width=16)
(actual time=1380599.184..1380599.320 rows=644 loops
=1)
Group Key: c1.licence, c2.licence
-> Gather (cost=83926.72..766032.88 rows=1450297 width=16)
(actual time=28842.680..1381159.047 rows=4080 loops
=1)
Workers Planned: 4
Workers Launched: 4
[...]
---------------------------------------------------
Regards
Esteban
On Tue, Dec 18, 2018 at 12:27 PM Darafei "Komяpa" Praliaskouski <
me at komzpa.net> wrote:
> Hi Bruce,
>
> Can you share the code that is currently slow for you?
>
> вт, 18 дек. 2018 г. в 14:25, Bruce Rindahl <bruce.rindahl at gmail.com>:
>
>> Everything is fine in 2.5 but I was just wondering if we could get a
>> performance increase in large tables.
>> Thanks
>> Bruce
>> _______________________________________________
>> postgis-devel mailing list
>> postgis-devel at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
>
> --
> Darafei Praliaskouski
> Support me: http://patreon.com/komzpa
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel
--
------------------------------------------------------------
Prof. Esteban Zimanyi
Department of Computer & Decision Engineering (CoDE) CP 165/15
Universite Libre de Bruxelles
Avenue F. D. Roosevelt 50
B-1050 Brussels, Belgium
fax: + 32.2.650.47.13
tel: + 32.2.650.31.85
e-mail: ezimanyi at ulb.ac.be
Internet: http://code.ulb.ac.be/
------------------------------------------------------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20181219/e77cd1be/attachment-0001.html>
More information about the postgis-devel
mailing list