Yes it's a known issue. Not just limited ST_Within and I think it has bad affects beyond killing use of index. As a general rule, don't use a sub query in a function call if you can avoid it.
I think Tom Lane explained the issue well a while ago. Don't have the explanation on hand at moment. -----Original Message----- From: postgis-users [mailto:[email protected]] On Behalf Of Stefan Keller Sent: Tuesday, April 18, 2017 7:01 PM To: PostGIS Users Discussion <[email protected]> Subject: [postgis-users] ST_Within with subquery as parameter makes query slow Hi, Recently I wrote this simple query: SELECT count (*) FROM osm_point AS osm WHERE ST_Within ( osm.way, (SELECT way FROM osm_polygon WHERE osm_id=-51701) ) AND tags @> 'railway=>station'; This was slow (>30 sec.) I then moved the subquery out of ST_Within to the FROM-clause like this: SELECT count(*) FROM osm_point AS osm, (SELECT way FROM osm_polygon WHERE osm_id=-51701) AS ch WHERE ST_Within(osm.way, ch.way) AND tags @> 'railway=>station' This was faster (5 sec.). As it seems, the presence of the subquery is blocking the inlining of ST_Within. Strange that the call overhead is so high. Is this effect only bound to ST_Within? Does anyone know more about this? :Stefan _______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
