[postgis-tickets] [PostGIS] #3739: ST_Within Not using index

[PostGIS]

#3739: ST_Within Not using index
--------------------------+---------------------------
  Reporter:  postgispaul  |      Owner:  pramsey
      Type:  defect       |     Status:  new
  Priority:  medium       |  Milestone:  PostGIS 2.3.3
 Component:  postgis      |    Version:  2.3.x
Resolution:               |   Keywords:
--------------------------+---------------------------
Description changed by robe:

Old description:

> Hi,
> I've got this query and I can't figure what I'm doing wrong.
> It doesn't use the geo-index.
>
> Versions:
> "POSTGIS="2.3.2 r15302" GEOS="3.6.1-CAPI-1.10.1 r4317" PROJ="Rel. 4.9.1,
> 04 March 2015" GDAL="GDAL 2.1.3, released 2017/20/01 GDAL_DATA not found"
> LIBXML="2.7.8" LIBJSON="0.12" (core procs from "2.3.0 r15146" need
> upgrade) RASTER (raster procs from "2.3.0 r (...)"
> "PostgreSQL 9.6.1, compiled by Visual C++ build 1800, 64-bit"
>
> This happens on a full planet_osm_polygon table.
> Definition:
> CREATE TABLE public.planet_osm_polygon
> (
>   osm_id bigint,
>   ... snip a number of irrelevant columns ...
>   way geometry(Geometry,3857)
> )
> WITH (
>   OIDS=FALSE
> );
>

> CREATE INDEX planet_osm_polygon_index
>   ON public.planet_osm_polygon
>   USING gist
>   (way);
>
> CREATE INDEX planet_osm_polygon_pkey
>   ON public.planet_osm_polygon
>   USING btree
>   (osm_id);
>
> I've done an analyze planet_osm_polygon but still the query planner
> surprises me.
>
> select s.way from planet_osm_polygon s
> join planet_osm_polygon ap on (ST_Within(s.way,ap.way))
> where ap.osm_id=123456
>
> results in a pkey lookup followed by a bitmap index scan
> Nested Loop  (cost=6339.51..360823.14 rows=88540 width=229)
>   Output: s.way
>   ->  Index Scan using planet_osm_polygon_pkey on
> public.planet_osm_polygon ap  (cost=0.57..2.79 rows=1 width=229)
>         Output: ap.osm_id, ap.highway, ap.surface, ap.railway,
> ap.waterway, ap.tunnel, ap.bridge, ap.oneway, ap.access, ap.name,
> ap.z_order, ap.way_area, ap.area, ap.natural, ap.water, ap.admin_level,
> ap.boundary, ap.landuse, ap.amenity, ap.parking, ap.a (...)
>         Index Cond: (ap.osm_id = 123456)
>   ->  Bitmap Heap Scan on public.planet_osm_polygon s
> (cost=6338.94..359934.96 rows=88540 width=229)
>         Output: s.osm_id, s.highway, s.surface, s.railway, s.waterway,
> s.tunnel, s.bridge, s.oneway, s.access, s.name, s.z_order, s.way_area,
> s.area, s.natural, s.water, s.admin_level, s.boundary, s.landuse,
> s.amenity, s.parking, s.aeroway, s.military, s (...)
>         Recheck Cond: (ap.way ~ s.way)
>         Filter: _st_contains(ap.way, s.way)
>         ->  Bitmap Index Scan on planet_osm_polygon_index
> (cost=0.00..6316.81 rows=265621 width=0)
>               Index Cond: (ap.way ~ s.way)
>
> adding ap.way ~ s.way and to the join condition:
> select s.way from planet_osm_polygon s
> join planet_osm_polygon ap on (ap.way ~ s.way and
> ST_Within(s.way,ap.way))
> where ap.osm_id=123456
>
> results in a pkey lookup and a normal index-scan
> Nested Loop  (cost=1.12..373.05 rows=89 width=229)
>   Output: s.way
>   ->  Index Scan using planet_osm_polygon_pkey on
> public.planet_osm_polygon ap  (cost=0.57..2.79 rows=1 width=229)
>         Output: ap.osm_id, ap.highway, ap.surface, ap.railway,
> ap.waterway, ap.tunnel, ap.bridge, ap.oneway, ap.access, ap.name,
> ap.z_order, ap.way_area, ap.area, ap.natural, ap.water, ap.admin_level,
> ap.boundary, ap.landuse, ap.amenity, ap.parking, ap.a (...)
>         Index Cond: (ap.osm_id = 123456)
>   ->  Index Scan using planet_osm_polygon_index on
> public.planet_osm_polygon s  (cost=0.55..369.37 rows=89 width=229)
>         Output: s.osm_id, s.highway, s.surface, s.railway, s.waterway,
> s.tunnel, s.bridge, s.oneway, s.access, s.name, s.z_order, s.way_area,
> s.area, s.natural, s.water, s.admin_level, s.boundary, s.landuse,
> s.amenity, s.parking, s.aeroway, s.military, s (...)
>         Index Cond: ((ap.way ~ s.way) AND (ap.way ~ s.way))
>         Filter: _st_contains(ap.way, s.way)
>
> One thing stands out here: the duplicate index condition ap.way ~ s.way
>
> My question is: why isn't it planning to use the normal index-scan
> without adding ap.way ~ s.way.
> After all, it is part of the st_within definition:
> CREATE OR REPLACE FUNCTION public.st_within(
>     geom1 geometry,
>     geom2 geometry)
>   RETURNS boolean AS
> 'SELECT $2 OPERATOR(public.~) $1 AND public._ST_Contains($2,$1)'
>   LANGUAGE sql IMMUTABLE
>   COST 100;

New description:

 Hi,
 I've got this query and I can't figure what I'm doing wrong.
 It doesn't use the geo-index.

 Versions:

 {{{
 "POSTGIS="2.3.2 r15302" GEOS="3.6.1-CAPI-1.10.1 r4317" PROJ="Rel. 4.9.1,
 04 March 2015" GDAL="GDAL 2.1.3, released 2017/20/01 GDAL_DATA not found"
 LIBXML="2.7.8" LIBJSON="0.12" (core procs from "2.3.0 r15146" need
 upgrade) RASTER (raster procs from "2.3.0 r (...)"
 "PostgreSQL 9.6.1, compiled by Visual C++ build 1800, 64-bit"
 }}}


 This happens on a full planet_osm_polygon table.
 Definition:

 {{{
 CREATE TABLE public.planet_osm_polygon
 (
   osm_id bigint,
   ... snip a number of irrelevant columns ...
   way geometry(Geometry,3857)
 )
 WITH (
   OIDS=FALSE
 );


 CREATE INDEX planet_osm_polygon_index
   ON public.planet_osm_polygon
   USING gist
   (way);

 CREATE INDEX planet_osm_polygon_pkey
   ON public.planet_osm_polygon
   USING btree
   (osm_id);
 }}}

 I've done an analyze planet_osm_polygon but still the query planner
 surprises me.


 {{{
 select s.way from planet_osm_polygon s
 join planet_osm_polygon ap on (ST_Within(s.way,ap.way))
 where ap.osm_id=123456

 }}}

 results in a pkey lookup followed by a bitmap index scan

 {{{
 Nested Loop  (cost=6339.51..360823.14 rows=88540 width=229)
   Output: s.way
   ->  Index Scan using planet_osm_polygon_pkey on
 public.planet_osm_polygon ap  (cost=0.57..2.79 rows=1 width=229)
         Output: ap.osm_id, ap.highway, ap.surface, ap.railway,
 ap.waterway, ap.tunnel, ap.bridge, ap.oneway, ap.access, ap.name,
 ap.z_order, ap.way_area, ap.area, ap.natural, ap.water, ap.admin_level,
 ap.boundary, ap.landuse, ap.amenity, ap.parking, ap.a (...)
         Index Cond: (ap.osm_id = 123456)
   ->  Bitmap Heap Scan on public.planet_osm_polygon s
 (cost=6338.94..359934.96 rows=88540 width=229)
         Output: s.osm_id, s.highway, s.surface, s.railway, s.waterway,
 s.tunnel, s.bridge, s.oneway, s.access, s.name, s.z_order, s.way_area,
 s.area, s.natural, s.water, s.admin_level, s.boundary, s.landuse,
 s.amenity, s.parking, s.aeroway, s.military, s (...)
         Recheck Cond: (ap.way ~ s.way)
         Filter: _st_contains(ap.way, s.way)
         ->  Bitmap Index Scan on planet_osm_polygon_index
 (cost=0.00..6316.81 rows=265621 width=0)
               Index Cond: (ap.way ~ s.way)
 }}}


 adding ap.way ~ s.way and to the join condition:


 {{{
 select s.way from planet_osm_polygon s
 join planet_osm_polygon ap on (ap.way ~ s.way and ST_Within(s.way,ap.way))
 where ap.osm_id=123456
 }}}


 results in a pkey lookup and a normal index-scan


 {{{
 Nested Loop  (cost=1.12..373.05 rows=89 width=229)
   Output: s.way
   ->  Index Scan using planet_osm_polygon_pkey on
 public.planet_osm_polygon ap  (cost=0.57..2.79 rows=1 width=229)
         Output: ap.osm_id, ap.highway, ap.surface, ap.railway,
 ap.waterway, ap.tunnel, ap.bridge, ap.oneway, ap.access, ap.name,
 ap.z_order, ap.way_area, ap.area, ap.natural, ap.water, ap.admin_level,
 ap.boundary, ap.landuse, ap.amenity, ap.parking, ap.a (...)
         Index Cond: (ap.osm_id = 123456)
   ->  Index Scan using planet_osm_polygon_index on
 public.planet_osm_polygon s  (cost=0.55..369.37 rows=89 width=229)
         Output: s.osm_id, s.highway, s.surface, s.railway, s.waterway,
 s.tunnel, s.bridge, s.oneway, s.access, s.name, s.z_order, s.way_area,
 s.area, s.natural, s.water, s.admin_level, s.boundary, s.landuse,
 s.amenity, s.parking, s.aeroway, s.military, s (...)
         Index Cond: ((ap.way ~ s.way) AND (ap.way ~ s.way))
         Filter: _st_contains(ap.way, s.way)

 }}}

 One thing stands out here: the duplicate index condition ap.way ~ s.way

 My question is: why isn't it planning to use the normal index-scan without
 adding ap.way ~ s.way.
 After all, it is part of the st_within definition:


 {{{
 CREATE OR REPLACE FUNCTION public.st_within(
     geom1 geometry,
     geom2 geometry)
   RETURNS boolean AS
 'SELECT $2 OPERATOR(public.~) $1 AND public._ST_Contains($2,$1)'
   LANGUAGE sql IMMUTABLE
   COST 100;

 }}}

--

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3739#comment:1>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.