1

I'm using postgresql 9.6 as my database. It takes too much time than the query itself. The query take about 160ms which is not as good as the wanted performance. My query is like :

SELECT DISTINCT code from table where ST_intersects((ST_Transform(ST_PointFromText('POINT(lon lat)', 4326),3857)),geom) 

I used this sql query to create spatial indexes

CREATE INDEX table_geom_gist ON table USING GIST (geom);

but it still consume the same time :

VACUUM ANALYZE table

and also tried to update stats :

SELECT UPDATE_GEOMETRY_STATS(table, geom);

but it fires an error that says :

the column table doesn't exist ! 

Any advice on how to speed up my request and solve this issue ?

  • "VACUUM ANALYZE table" will update statistics on table. Did you check EXPLAIN SELECT ...? Probably your spatial index is not used. – Zoltan Apr 10 '17 at 19:10
  • 1
    160 milliseconds is pretty fast for a query that has to do text conversion and reprojection. It's entirely possible that you've optimized what you could. There's no way to judge unless you provide a more complete description of the table contents (how many total rows and how many meet the spatial criteria) and the output of the EXPLAIN plan for the query. – Vince Apr 10 '17 at 23:13
  • well i thank you a lot for being such proactif persons ! for the explain it uses the index and this is the result of the EXPLAIN SELECT : Unique (cost=8.41..8.42 rows=1 width=4) -> Sort (cost=8.41..8.42 rows=1 width=4) Sort Key: code -> Index Scan using table_geom_gist on table (cost=0.13..8.40 rows=1 width=4) Index Cond: ('0101000020110F00000B1CDFFB838EF540AEA8D43E8BFC5641'::geometry && geom) Filter: _st_intersects('0101000020110F00000B1CDFFB838EF540AEA8D43E8BFC5641'::geometry, geom) – Abdelaziz Apr 11 '17 at 8:37
  • my table has 39 rows , and the query should only return one row because there is no overlaping in my data , – Abdelaziz Apr 11 '17 at 8:41
  • If there is only 39 rows? try removing the index. It might give more harm than gain when there is so few geometries. – Nicklas Avén Apr 17 '17 at 16:03

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.