2

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).

This query takes about 15 secs:

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","admin_level") t )As properties FROM osm_admin 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 (admin_level < 5) ) fc;

The execution plan: http://explain.depesz.com/s/ZJU2

This query takes approx. 45 secs:

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","area","z_order") t ) As properties FROM osm_landusages As lg, (
SELECT ST_Transform(ST_MakeEnvelope(-2.8125,38.82259097617711,25.312499999999986,57.326521225217064, 4326),3857) 
  as geom) as bbox 
WHERE ST_Intersects(lg.geometry, bbox.geom)  
AND (area > 200000000) ) fc;

http://explain.depesz.com/s/26L

Table info:

                            Table "public.osm_admin"
       Column    |          Type           |                       Modifiers
    -------------+-------------------------+--------------------------------------------------------
     id          | integer                 | not null default nextval('osm_admin_id_seq'::regclass)
     osm_id      | bigint                  |
     name        | character varying       |
     type        | character varying       |
     admin_level | integer                 |
     geometry    | geometry(Geometry,3857) |
    Indexes:
        "osm_admin_pkey" PRIMARY KEY, btree (id)
        "osm_admin_geom" gist (geometry)
        "osm_admin_osm_id_idx" btree (osm_id)

 Rows: 333319


                           Table "public.osm_landusages"
      Column  |          Type           |                          Modifiers
    ----------+-------------------------+-------------------------------------------------------------
     id       | integer                 | not null default nextval('osm_landusages_id_seq'::regclass)
     osm_id   | bigint                  |
     name     | character varying       |
     type     | character varying       |
     area     | real                    |
     z_order  | integer                 |
     geometry | geometry(Geometry,3857) |
    Indexes:
        "osm_landusages_pkey" PRIMARY KEY, btree (id)
        "osm_landusages_geom" gist (geometry)
        "osm_landusages_osm_id_idx" btree (osm_id)

Rows: 19934972

How can I optimize this query? Are there ways I can query more efficiently? Should I create more indexes? Does it make sense to create another index on the WHERE CLAUSE I'm using (admin_level < 5), since I'll be repeating the same query all the time?

The data is the OSM PostGIS database.

  • CREATE INDEX ON public.osm_landusages (area); - or, if the cutoff is always 200000000, CREATE INDEX ON public.osm_landusages (area) WHERE area > 200000000; – dezso Jan 30 '15 at 13:33
  • 1
    I mean, after adding the index - and, unrelatedly: we should clean up the comments above :) – dezso Jan 30 '15 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.