Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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

share|improve this question
    
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 May 15 '14 at 9:31
1  
Please post the output of explain analyze (formatted here, or upload to depesz.com) –  a_horse_with_no_name May 15 '14 at 9:32
3  
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 May 15 '14 at 9:33
1  
Please read stackoverflow.com/tags/postgresql-performance/info and update the question based on the advice given there. –  Craig Ringer May 15 '14 at 11:34
    
What does 'all' refer to from the subquery? It strikes me that as written, the subquery isn't really necessary. –  John Barça May 15 '14 at 14:00

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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. –  John Barça May 15 '14 at 13:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.