[Dutch] PostGIS optimalisaties

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

[Dutch] PostGIS optimalisaties

Paul Meems (Top-X)
Goedemiddag,

Ik ben al de hele dag bezig om mijn PostGIS query te optimaliseren maar het lukt me niet.
Hopelijk zijn er hier op deze lijst enige PostGIS goeroes aanwezig ;)

Ik heb een eenvoudige tabel met BRP-data (gewaspercelen). Iets meer dan 750k rijen.
Ik heb 7 kolommen waarvan eentje van het type 'geometry(Polygon)' is.
Ik heb een GIST index daarop.

Deze data wordt gebruikt in een webapplicatie met OpenLayers. De boundingbox wordt naar de server gestuurd en de percelen die daarin zitten worden terug gestuurd.
De query:
select *
from BRP2015  
where ST_Intersects(Polygon,'0103000000 ...')
Ik heb hier de bbox niet in z'n geheel geplakt.

De explain:
Bitmap Heap Scan on brp2015 (cost=30.53..3214.62 rows=275 width=524) (actual time=0.608..6.551 rows=882 loops=1)
  Recheck Cond: (polygon && '0103000000 ...'
  Filter: _st_intersects(polygon, '0103000000 ...'
  Rows Removed by Filter: 36
  ->  Bitmap Index Scan on brp2015_polygon_gist_idx  (cost=0.00..30.46 rows=824 width=0) (actual time=0.416..0.416 rows=918 loops=1)
        Index Cond: (polygon && '0103000000 ...'

Als ik het allemaal goed begrepen heb dan wordt de index netjes gebruikt.

Bovenstaande voorbeeldquery geeft 882 rijen terug en duurt rond de 2 seconden.
Dan is er nog de tijd dat het duurt voordat de nieuwe data gerenderd is en dan is het zomaar 4-5 seconden voordat de data in de browser wordt getoond.

Ik gebruik PostgreSQL 9.3.3 op Ubuntu en PostGis v2.1 (USE_GEOS=1 USE_PROJ=1 USE_STATS=1)

Het doel is om de query binnen 1s resultaat te laten terug geven.

Iemand nog een richting waar ik het kan zoeken?

Alvast bedankt.


Met vriendelijke groet,

Paul




_______________________________________________
Dutch mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/dutch
Reply | Threaded
Open this post in threaded view
|

Re: [Dutch] PostGIS optimalisaties

TomvanTilburg

Hoi Paul,

Weet je zeker dat je query zo lang duurt?
Het kan ook dat je zit te wachten op dataoverdracht (naar pgadmin bijv). Wat doet een count (*) bijvoorbeeld?

Het gedeelte renderen kan postgres natuurlijk niks aan doen. Hoe breng je de data in openlayers?

Grt,
Tom


On Mon, 25 Jan 2016 18:45 Paul Meems <[hidden email]> wrote:
Goedemiddag,

Ik ben al de hele dag bezig om mijn PostGIS query te optimaliseren maar het lukt me niet.
Hopelijk zijn er hier op deze lijst enige PostGIS goeroes aanwezig ;)

Ik heb een eenvoudige tabel met BRP-data (gewaspercelen). Iets meer dan 750k rijen.
Ik heb 7 kolommen waarvan eentje van het type 'geometry(Polygon)' is.
Ik heb een GIST index daarop.

Deze data wordt gebruikt in een webapplicatie met OpenLayers. De boundingbox wordt naar de server gestuurd en de percelen die daarin zitten worden terug gestuurd.
De query:
select *
from BRP2015  
where ST_Intersects(Polygon,'0103000000 ...')
Ik heb hier de bbox niet in z'n geheel geplakt.

De explain:
Bitmap Heap Scan on brp2015 (cost=30.53..3214.62 rows=275 width=524) (actual time=0.608..6.551 rows=882 loops=1)
  Recheck Cond: (polygon && '0103000000 ...'
  Filter: _st_intersects(polygon, '0103000000 ...'
  Rows Removed by Filter: 36
  ->  Bitmap Index Scan on brp2015_polygon_gist_idx  (cost=0.00..30.46 rows=824 width=0) (actual time=0.416..0.416 rows=918 loops=1)
        Index Cond: (polygon && '0103000000 ...'

Als ik het allemaal goed begrepen heb dan wordt de index netjes gebruikt.

Bovenstaande voorbeeldquery geeft 882 rijen terug en duurt rond de 2 seconden.
Dan is er nog de tijd dat het duurt voordat de nieuwe data gerenderd is en dan is het zomaar 4-5 seconden voordat de data in de browser wordt getoond.

Ik gebruik PostgreSQL 9.3.3 op Ubuntu en PostGis v2.1 (USE_GEOS=1 USE_PROJ=1 USE_STATS=1)

Het doel is om de query binnen 1s resultaat te laten terug geven.

Iemand nog een richting waar ik het kan zoeken?

Alvast bedankt.


Met vriendelijke groet,

Paul



_______________________________________________
Dutch mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/dutch

_______________________________________________
Dutch mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/dutch
Reply | Threaded
Open this post in threaded view
|

Re: [Dutch] PostGIS optimalisaties

Paul Meems (Top-X)
Bedankt Tom voor je reactie.

Ik time nu met een unit test, dus voordat de data naar OpenLayers wordt gestuurd. Dus die stap heb ik vooralsnog uitgesloten.
Een count(1) gaat idd veel sneller, dus het lijkt door de data-overdracht te komen.
Marco ([hidden email]) suggereerde om een CLUSTER op de tabel met de spatial index te gebruiken. Dat heb ik geprobeerd, maar de query duurt daardoor ca. 10% langer.
Vervolgens ben ik nog in het memory gebruik van PostgreSQL gedoken en nu blijkt dat die settings nog op de default waarden stonden en dus erg laag. De db-server draait bij Amazon.
Ik heb de work_mem verhoogd van 1MB naar 8MB.
De combinatie van meer work_mem en CLUSTER lijkt nu goed te gaan.

Omdat ik niet alleen een tabel heb met de BRP data van 2015, maar ook een tabel met BRP data van 2014 kan ik mooi de performance testen met 1 wel CLUSTER en 1 niet.
Ik test met Chrome en kijk naar de 'Time' in de Network tab van de Dev-Tools (F12)
Met CLUSTER en 8MB: 1.2s-1.6s
Zonder CLUSTER en 8MB: 2.5s-3s
Met 1MB: 3s-4s

Dit zijn significante verbeteringen. Ik zal uitzoeken of ik het geheugen nog iets kan ophogen.
De gebruikers zijn weer tevreden.

Bedankt voor het meedenken en de suggesties.

Met vriendelijke groet,

Paul




Op 25 januari 2016 19:06 schreef Tom van Tilburg <[hidden email]>:

Hoi Paul,

Weet je zeker dat je query zo lang duurt?
Het kan ook dat je zit te wachten op dataoverdracht (naar pgadmin bijv). Wat doet een count (*) bijvoorbeeld?

Het gedeelte renderen kan postgres natuurlijk niks aan doen. Hoe breng je de data in openlayers?

Grt,
Tom


On Mon, 25 Jan 2016 18:45 Paul Meems <[hidden email]> wrote:
Goedemiddag,

Ik ben al de hele dag bezig om mijn PostGIS query te optimaliseren maar het lukt me niet.
Hopelijk zijn er hier op deze lijst enige PostGIS goeroes aanwezig ;)

Ik heb een eenvoudige tabel met BRP-data (gewaspercelen). Iets meer dan 750k rijen.
Ik heb 7 kolommen waarvan eentje van het type 'geometry(Polygon)' is.
Ik heb een GIST index daarop.

Deze data wordt gebruikt in een webapplicatie met OpenLayers. De boundingbox wordt naar de server gestuurd en de percelen die daarin zitten worden terug gestuurd.
De query:
select *
from BRP2015  
where ST_Intersects(Polygon,'0103000000 ...')
Ik heb hier de bbox niet in z'n geheel geplakt.

De explain:
Bitmap Heap Scan on brp2015 (cost=30.53..3214.62 rows=275 width=524) (actual time=0.608..6.551 rows=882 loops=1)
  Recheck Cond: (polygon && '0103000000 ...'
  Filter: _st_intersects(polygon, '0103000000 ...'
  Rows Removed by Filter: 36
  ->  Bitmap Index Scan on brp2015_polygon_gist_idx  (cost=0.00..30.46 rows=824 width=0) (actual time=0.416..0.416 rows=918 loops=1)
        Index Cond: (polygon && '0103000000 ...'

Als ik het allemaal goed begrepen heb dan wordt de index netjes gebruikt.

Bovenstaande voorbeeldquery geeft 882 rijen terug en duurt rond de 2 seconden.
Dan is er nog de tijd dat het duurt voordat de nieuwe data gerenderd is en dan is het zomaar 4-5 seconden voordat de data in de browser wordt getoond.

Ik gebruik PostgreSQL 9.3.3 op Ubuntu en PostGis v2.1 (USE_GEOS=1 USE_PROJ=1 USE_STATS=1)

Het doel is om de query binnen 1s resultaat te laten terug geven.

Iemand nog een richting waar ik het kan zoeken?

Alvast bedankt.


Met vriendelijke groet,

Paul



_______________________________________________
Dutch mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/dutch


_______________________________________________
Dutch mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/dutch