<div dir="ltr"><div class="gmail_quote"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr">Dear all,<div><br></div><div>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</div><div><a href="http://dna.fernuni-hagen.de/secondo/BerlinMOD/BerlinMOD.html" target="_blank">http://dna.fernuni-hagen.de/secondo/BerlinMOD/BerlinMOD.html</a><br></div><div>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</div><div><div dir="ltr"><span class="m_-1757692822792862849gmail-il"><br></span></div><div dir="ltr"><span class="m_-1757692822792862849gmail-il">CPU</span>: 2x Intel Xeon E5-2640 v4</div><div>RAM: 128GB</div><div>HDD: 500GB NVMe SSD</div><div>OS: Debian 9 "stretch" (kernel 4.9.0-8-amd64)</div></div><div>PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit</div><div><div> 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</div></div><div><br></div><div>The four scale factors (SF) used were as follows</div><div><br></div><div><div>SF 0.005: 1797 trips over 4 days/partitions</div><div>SF 0.05: 15045 trips over 9 days/partitions</div><div>SF 0.2: 62510 trips over 16 days/partitions</div><div>SF 1.0: 292940 trips over 31 days/partitions</div></div><div><br></div><div>We give next the results for Query 6 of the benchmark </div><div><br></div><div><div>Query 6: What are the pairs of trucks that have ever been as close as 10 m or less to each other?</div></div><div><br></div><div>which in MobilityDB is written as follows</div><div><br></div><div><div>SELECT DISTINCT C1.Licence AS Licence1, C2.Licence AS Licence2</div><div>FROM Trips T1, Cars C1, Trips T2, Cars C2</div><div>WHERE T1.CarId = C1.CarId AND T2.CarId = C2.CarId</div><div>AND T1.CarId < T2.CarId AND C1.Type = 'truck' AND C2.Type = 'truck' </div><div>AND expandSpatial(T1.Trip, 10) && expandSpatial(T2.Trip, 10) </div><div>AND tdwithin(T1.Trip, T2.Trip, 10.0) &= true</div><div>ORDER BY C1.Licence, C2.Licence</div></div><div><br></div><div><div>---------------------------------------------------</div><div>Trips</div><div><br></div><div>SF 0.005</div><div>---------</div><div> Planning Time: 1.475 ms</div><div> Execution Time: 553.243 ms</div><div> </div><div> Unique (cost=1163.94..1165.62 rows=1 width=164) (actual time=553.124..553.124 rows=1 loops=1)</div><div> -> Sort (cost=1163.94..1164.50 rows=224 width=164) (actual time=553.123..553.123 rows=1 loops=1)</div><div> Sort Key: c1.licence, c2.licence</div><div> Sort Method: quicksort Memory: 25kB</div><div> -> Nested Loop (cost=8.36..1155.19 rows=224 width=164) (actual time=474.755..553.114 rows=1 loops=1)</div><div> 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)))</div><div> Rows Removed by Join Filter: 6888</div><div>[...]</div><div><br></div><div>SF 0.05</div><div>---------</div><div> Planning Time: 1.589 ms<br></div><div> Execution Time: 7303.452 ms</div><div> </div><div> Sort (cost=16790.28..16790.29 rows=4 width=164) (actual time=7292.983..7292.985 rows=22 loops=1)</div><div> Sort Key: c1.licence, c2.licence</div><div> Sort Method: quicksort Memory: 26kB</div><div> -> HashAggregate (cost=16790.20..16790.24 rows=4 width=164) (actual time=7292.914..7292.918 rows=22 loops=1)</div><div> Group Key: c1.licence, c2.licence</div><div> -> Gather (cost=6095.47..16736.40 rows=10760 width=164) (actual time=496.630..7296.353 rows=78 loops=1)</div><div> Workers Planned: 4</div><div> Workers Launched: 4</div><div>[...]</div><div><br></div><div>SF 0.2</div><div>---------</div><div> Planning Time: 2.378 ms</div><div> Execution Time: 95450.940 ms</div><div><br></div><div> Sort (cost=222692.07..222692.11 rows=16 width=164) (actual time=95434.894..95434.898 rows=108 loops=1)</div><div> Sort Key: c1.licence, c2.licence</div><div> Sort Method: quicksort Memory: 33kB</div><div> -> HashAggregate (cost=222691.59..222691.75 rows=16 width=164) (actual time=95434.711..95434.740 rows=108 loops=1)</div><div> Group Key: c1.licence, c2.licence</div><div> -> Gather (cost=24786.84..221690.44 rows=200230 width=164) (actual time=373.478..95450.026 rows=524 loops=1)</div><div> Workers Planned: 4</div><div> Workers Launched: 4</div><div>[...]</div><div><br></div><div>SF 1.0</div><div>---------</div><div> Planning Time: 3.813 ms</div><div> Execution Time: 1171259.212 ms</div><div> </div><div> Sort (cost=103497057.49..103497072.70 rows=6084 width=16) (actual time=1170553.544..1170553.572 rows=644 loops=1)</div><div> Sort Key: c1.licence, c2.licence</div><div> Sort Method: quicksort Memory: 75kB</div><div> -> HashAggregate (cost=103496614.25..103496675.09 rows=6084 width=16) (actual time=1170551.974..1170552.187 rows=644</div><div> loops=1)</div><div> Group Key: c1.licence, c2.licence</div><div> -> Gather (cost=264285.07..101835734.56 rows=332175937 width=16) (actual time=6021.547..1171251.922 rows=4080 loops=1)</div><div> Workers Planned: 5</div><div> Workers Launched: 5</div><div>[...]</div><div><br></div><div>---------------------------------------------------</div><div><br></div><div>AllTrips</div><div><br></div><div>SF 0.005</div><div>---------</div><div> Planning Time: 2.359 ms</div><div> Execution Time: 553.953 ms</div><div><br></div><div> Unique (cost=85.40..85.42 rows=1 width=164) (actual time=553.834..553.834 rows=1 loops=1)</div><div> -> Sort (cost=85.40..85.41 rows=2 width=164) (actual time=553.833..553.833 rows=1 loops=1)</div><div> Sort Key: c1.licence, c2.licence</div><div> Sort Method: quicksort Memory: 25kB</div><div> -> Nested Loop (cost=8.69..85.39 rows=2 width=164) (actual time=460.713..553.804 rows=1 loops=1)</div><div> 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)))</div><div> Rows Removed by Join Filter: 6888</div><div>[...]</div><div><br></div><div>SF 0.05</div><div>---------</div><div> Planning Time: 0.440 ms</div><div> Execution Time: 35537.814 ms</div><div><br></div><div> Unique (cost=1222.46..1222.83 rows=4 width=164) (actual time=35405.795..35405.813 rows=22 loops=1)</div><div> -> Sort (cost=1222.46..1222.58 rows=50 width=164) (actual time=35405.794..35405.798 rows=78 loops=1)</div><div> Sort Key: c1.licence, c2.licence</div><div> Sort Method: quicksort Memory: 31kB</div><div> -> Nested Loop (cost=9.73..1221.05 rows=50 width=164) (actual time=3265.734..35405.675 rows=78 loops=1)</div><div> 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)))</div><div> Rows Removed by Join Filter: 534283</div><div>[...]</div><div> <br></div><div>SF 0.2</div><div>---------</div><div> Planning Time: 0.555 ms</div><div> Execution Time: 478382.227 ms</div><div><br></div><div> Unique (cost=10970.86..10977.38 rows=16 width=164) (actual time=478382.024..478382.122 rows=108 loops=1)</div><div> -> Sort (cost=10970.86..10973.03 rows=870 width=164) (actual time=478382.023..478382.047 rows=524 loops=1)</div><div> Sort Key: c1.licence, c2.licence</div><div> Sort Method: quicksort Memory: 65kB</div><div> -> Nested Loop (cost=29.68..10928.38 rows=870 width=164) (actual time=1020.236..478380.983 rows=524 loops=1)</div><div> 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)))</div><div> Rows Removed by Join Filter: 8672501</div><div>[...]</div><div><br></div><div>SF 1.0</div><div>---------</div><div> Planning Time: 0.570 ms</div><div> Execution Time: 1381164.728 ms</div><div><br></div><div><br></div><div> Sort (cost=773727.61..773742.82 rows=6084 width=16) (actual time=1380600.506..1380600.534 rows=644 loops=1)</div><div> Sort Key: c1.licence, c2.licence</div><div> Sort Method: quicksort Memory: 75kB</div><div> -> HashAggregate (cost=773284.37..773345.21 rows=6084 width=16) (actual time=1380599.184..1380599.320 rows=644 loops</div><div>=1)</div><div> Group Key: c1.licence, c2.licence</div><div> -> Gather (cost=83926.72..766032.88 rows=1450297 width=16) (actual time=28842.680..1381159.047 rows=4080 loops</div><div>=1)</div><div> Workers Planned: 4</div><div> Workers Launched: 4</div><div>[...]</div><div> </div><div>---------------------------------------------------</div></div><div><br></div><div>Regards</div><div><br></div><div>Esteban</div><div><br></div></div></div></div></div></div></div></div></div><div class="gmail_quote"><div dir="ltr">On Tue, Dec 18, 2018 at 12:27 PM Darafei "Komяpa" Praliaskouski <<a href="mailto:me@komzpa.net" target="_blank">me@komzpa.net</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hi Bruce,<div><br></div><div>Can you share the code that is currently slow for you?</div></div><br><div class="gmail_quote"><div dir="ltr">вт, 18 дек. 2018 г. в 14:25, Bruce Rindahl <<a href="mailto:bruce.rindahl@gmail.com" target="_blank">bruce.rindahl@gmail.com</a>>:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="auto">Everything is fine in 2.5 but I was just wondering if we could get a performance increase in large tables. <div dir="auto">Thanks</div></div><div dir="auto"><div dir="auto">Bruce</div></div>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a></blockquote></div>-- <br><div dir="ltr" class="m_-1757692822792862849gmail-m_7560858710664359846gmail-m_3835392194299720498gmail-m_-7086963858053956420gmail_signature"><div dir="ltr">Darafei Praliaskouski<br>Support me: <a href="http://patreon.com/komzpa" target="_blank">http://patreon.com/komzpa</a></div></div>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a></blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr" class="m_-1757692822792862849gmail-m_7560858710664359846gmail-m_3835392194299720498gmail_signature">------------------------------------------------------------<br>Prof. Esteban Zimanyi<br>Department of Computer & Decision Engineering (CoDE) CP 165/15 <br>Universite Libre de Bruxelles <br>Avenue F. D. Roosevelt 50 <br>B-1050 Brussels, Belgium <br>fax: + 32.2.650.47.13<br>tel: + 32.2.650.31.85<br>e-mail: <a href="mailto:ezimanyi@ulb.ac.be" target="_blank">ezimanyi@ulb.ac.be</a><br>Internet: <a href="http://code.ulb.ac.be/" target="_blank">http://code.ulb.ac.be/</a><br>------------------------------------------------------------</div>
</div>
</div></div>