Postgresql query optimization Index not used

Question

I'm having some trouble in optimizing some posgresql queries. For example, this one seams to be the slowest:

select  st_astext(geom), 'all' as type
from (
   select ST_Simplify(ST_Intersection(ST_MakePolygon(ST_GeomFromText(? ,4326)), st_transform(way, 4326)), ?) as geom
   from planet_osm_polygon 
   where (sT_Intersects(ST_MakePolygon(ST_GeomFromText(?,4326)), st_transform(way,4326))=true)
   and   ('natural' IN ('water', 'pond') OR waterway IN ('basin', 'canal', 'mill_pond', 'pond', 'riverbank', 'stream'))
) AS subquery";

First thing i did was editing the conf file and changing buffer sizes, and i got few percent less in total time spent.

Next thing I did was creating new tables from existing tables and breaking multipolygons into polygons. That speeded up most of the querys from 70-90%. However, the slowest 3 queries didnt speed up more than a few percent.

By examining the searches using EXPLAIN and ANALYZE, i realized that indexes are not used. What is used is seqscan even if i disable it. As far as i know this means that i should make a new index.

In the table planet_osm_polygon i have two indexes:

CREATE INDEX planet_osm_polygon_index_poly
  ON planet_osm_polygon_poly
  USING gist (way);

CREATE INDEX planet_osm_polygon_poly_pkey
  ON planet_osm_polygon_poly
  USING btree (osm_id);

Any ideas how to speed up this query and why aren't the indexes being used?

This is very new to me, and if something i wrote doesn't make sense, just ignore it :)

I'm not very familiar with PostgreSQL, but I suppose all those ST_* are custom functions, right? I don't expect any RDBMS to be able to optimize access on the result of a custom function. Since you do have an AND in your where clause, you could try to switch the two conditions around and see if performance gets better. — Frazz
– Frazz, Commented May 15, 2014 at 9:31
Please post the output of explain analyze (formatted here, or upload to depesz.com) — user330315
– user330315, Commented May 15, 2014 at 9:32
Another issue... what is 'natural' IN ('water','pond') suppose to mean? Is natural a column or a constant? In the former case, why is it quoted? In the latter case, obviously this condition is always false. — Frazz
– Frazz, Commented May 15, 2014 at 9:33
Please read stackoverflow.com/tags/postgresql-performance/info and update the question based on the advice given there. — Craig Ringer
– Craig Ringer, Commented May 15, 2014 at 11:34
What does 'all' refer to from the subquery? It strikes me that as written, the subquery isn't really necessary. — John Powell
– John Powell, Commented May 15, 2014 at 14:00

Mike T · Accepted Answer · 2014-05-15 12:23:16Z

1

Indexes could potentially be used with ST_Intersects in the WHERE part, except they are not because a function is used on the data, namely st_transform(way,4326). Your options are to avoid the function (perform an interesction query within the native projection, which will yield different answers), or to add an index using the function (although I'm not 100% certain that this would work with ST_Intersects).

Lastly, two points. SELECT 'natural' IN ('water', 'pond'); is always false. And SELECT true=true is true, so any boolean operator like ST_Intersects(g1, g2)=true is logically valid, but is aesthetically redundant. Just use ST_Intersects(g1, g2) without the "equals true" part.

answered May 15, 2014 at 12:23

Mike T

44.3k18 gold badges166 silver badges213 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Powell Over a year ago

You could also try transforming the geometry before running the query and setting the geometry srid of the table explicitly, look at the UpdateGeometrySRID function: postgis.org/docs/UpdateGeometrySRID.html. As Mike has said, the ST_Transform will make it harder for the optimizer to find the spatial index.

Collectives™ on Stack Overflow

Postgresql query optimization Index not used

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related