ST_Within with subquery as parameter makes query slow

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

ST_Within with subquery as parameter makes query slow

Stefan Keller-4
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_Within with subquery as parameter makes query slow

Regina Obe-2
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:[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

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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...