Re: ST_Within with subquery as parameter makes query slow
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.
From: postgis-users [mailto:[hidden email]] On Behalf Of Stefan Keller
Sent: Tuesday, April 18, 2017 7:01 PM
To: PostGIS Users Discussion <[hidden email]>
Subject: [postgis-users] ST_Within with subquery as parameter makes query slow
Recently I wrote this simple query:
SELECT count (*)
osm_point AS osm
(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:
osm_point AS osm,
(SELECT way FROM osm_polygon WHERE osm_id=-51701) AS ch
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?