jeudi 29 janvier 2015

How to optimize inefficient query in PostGIS


I'm doing some queries on a PostGIS, and some of them take a really long time ( > 60 secs). My queries are like this, except querying different tables (like osm_placesbelow).



SELECT row_to_json(fc) FROM (
SELECT
ST_AsGeoJSON(ST_Transform(ST_Intersection(lg.geometry, bbox.geom), 4326))::json
As geometry, (
SELECT row_to_json(t) FROM ( SELECT "name","type","population","z_order") t )
As properties FROM osm_places As lg, (
SELECT ST_Transform(ST_MakeEnvelope(-2.8125,54.162433968067795,25.312499999999986,
67.60922060496382, 4326),3857) as geom)
as bbox WHERE ST_Intersects(lg.geometry, bbox.geom)
AND (type = 'country' OR type = 'city') ) fc;


This is the EXPLAIN of the above query:



Nested Loop (cost=18.51..1159.41 rows=2 width=107)
Output: row_to_json(ROW((_st_asgeojson(1, st_transform(st_intersection(lg.geometry, ('0103000020110F0000010000000500000080107C45F81B13C1CF57E2E334785B4180107
-> Result (cost=0.00..0.01 rows=1 width=0)
Output: '0103000020110F0000010000000500000080107C45F81B13C1CF57E2E334785B4180107C45F81B13C117F1A707D8B46341A3922B4E777F454117F1A707D8B46341A3922B4E777F
-> Bitmap Heap Scan on public.osm_places lg (cost=18.51..1158.81 rows=1 width=75)
Output: lg.id, lg.osm_id, lg.name, lg.type, lg.z_order, lg.population, lg.geometry
Recheck Cond: (lg.geometry && ('0103000020110F0000010000000500000080107C45F81B13C1CF57E2E334785B4180107C45F81B13C117F1A707D8B46341A3922B4E777F454117F1A
Filter: ((((lg.type)::text = 'country'::text) OR ((lg.type)::text = 'city'::text)) AND _st_intersects(lg.geometry, ('0103000020110F00000100000005000000
-> Bitmap Index Scan on osm_places_geom (cost=0.00..18.51 rows=279 width=0)
Index Cond: (lg.geometry && ('0103000020110F0000010000000500000080107C45F81B13C1CF57E2E334785B4180107C45F81B13C117F1A707D8B46341A3922B4E777F45411
SubPlan 1
-> Subquery Scan on t (cost=0.00..0.02 rows=1 width=24)
Output: row_to_json(t.*)
-> Result (cost=0.00..0.01 rows=1 width=0)
Output: lg.name, lg.type, lg.population, lg.z_order
(15 rows)


How can I optimize this query? Are there ways I can query more efficiently? Should I create more indexes? The data is the OSM PostGIS database.





Aucun commentaire:

Enregistrer un commentaire