[postgis-tickets] [PostGIS] #3739: ST_Within Not using index
PostGIS
trac at osgeo.org
Mon Apr 24 02:18:25 PDT 2017
#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:
--------------------------+---------------------------
Comment (by robe):
Maybe it's just late, but relooking at the plans I see nothing wrong here.
Your original does use an index but chooses a bitmap index scan strategy
instead of a pure index scan.
I don't have the same dataset, but I get similar plans. The geo index is
used just like yours in your bad case. True it's in a bitmap index,but
that's fine and common. I'm thinking there is nothing worth fixing here
as far as plans go, but if you are seeing much worse performance with the
bitmap index scan, that might be a separate issue.
postgispaul,
can you rerun your examples with actual costs ( explain analyze instead of
explain).
Only thing I see here is the estimated and actual are a bit off, which is
no big deal in most cases.
This is testing on a FreeBSD with an example that yields an output of 299
rows,
goes thru similar plan as yours
first does btree index by id
followed by bitmap index scan first using the ~ to bring in 783 rows of
which 484 are then removed by a recheck and _ST_Contains. Same as yours,
uses an index. I got thrown off because I forgot I have to read the plan
from bottom up from each node.
{{{
PostgreSQL 9.3.14 on amd64-portbld-freebsd10.1, compiled by FreeBSD clang
version 3.4.1 (tags/RELEASE_34/dot1-final 208032) 20140512, 64-bit
POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08
September 2015" GDAL="GDAL 2.1.0, released 2016/04/25" LIBXML="2.9.4"
LIBJSON="0.12" RASTER
}}}
{{{
EXPLAIN ANALYZE VERBOSE select s.way FROM osm.us_west_planet_osm_polygon
s
join osm.us_west_planet_osm_polygon ap on ( st_within(s.way, ap.way) )
where ap.osm_id=179980467 ;
}}}
Yields:
{{{
QUERY PLAN
----------------------- Nested Loop (cost=362.38..22734.22 rows=2001
width=252) (actual time=0.560..13.253 rows=299 loops=1)
Output: s.way
-> Index Scan using us_west_planet_osm_polygon_pkey on
osm.us_west_planet_osm_polygon ap (cost=0.43..8.45 rows=1 width=252)
(actual time=0.013..0.015 rows=1 loops=1)
Output: ap.osm_id, ap.access, ap."addr:housename",
ap."addr:housenumber", ap."addr:interpolation", ap.admin_level,
ap.aerialway, ap.aeroway, ap.amenity, ap.area, ap.barrier, ap.bicycle,
ap.brand, ap.bridge
nomination, ap.disused, ap.embankment, ap.foot, ap."generator:source",
ap.harbour, ap.highway, ap.historic, ap.horse, ap.intermittent,
ap.junction, ap.landuse, ap.layer, ap.leisure, ap.lock, ap.man_made,
ap.militar
.population, ap.power, ap.power_source, ap.public_transport, ap.railway,
ap.ref, ap.religion, ap.route, ap.service, ap.shop, ap.sport, ap.surface,
ap.toll, ap.tourism, ap."tower:type", ap.tracktype, ap.tunnel, ap.w
Index Cond: (ap.osm_id = 179980467)
-> Bitmap Heap Scan on osm.us_west_planet_osm_polygon s
(cost=361.95..22705.76 rows=2001 width=252) (actual time=0.542..13.168
rows=299 loops=1)
Output: s.osm_id, s.access, s."addr:housename",
s."addr:housenumber", s."addr:interpolation", s.admin_level, s.aerialway,
s.aeroway, s.amenity, s.area, s.barrier, s.bicycle, s.brand, s.bridge,
s.boundary,
, s.embankment, s.foot, s."generator:source", s.harbour, s.highway,
s.historic, s.horse, s.intermittent, s.junction, s.landuse, s.layer,
s.leisure, s.lock, s.man_made, s.military, s.motorcar, s.name,
s."natural", s
c_transport, s.railway, s.ref, s.religion, s.route, s.service, s.shop,
s.sport, s.surface, s.toll, s.tourism, s."tower:type", s.tracktype,
s.tunnel, s.water, s.waterway, s.wetland, s.width, s.wood, s.z_order,
s.way
Recheck Cond: (ap.way ~ s.way)
Filter: _st_contains(ap.way, s.way)
Rows Removed by Filter: 484
-> Bitmap Index Scan on us_west_planet_osm_polygon_index
(cost=0.00..361.44 rows=6004 width=0) (actual time=0.314..0.314 rows=783
loops=1)
Index Cond: (ap.way ~ s.way)
Total runtime: 13.402 ms
(13 rows)
}}}
Now repeating using the ~
{{{
EXPLAIN ANALYZE VERBOSE select s.way FROM osm.us_west_planet_osm_polygon
s
join osm.us_west_planet_osm_polygon ap on ( ap.way ~ s.way AND
st_within(s.way, ap.way) )
where ap.osm_id=179980467 ;
}}}
Yields:
{{{
QUERY PLAN
----------------------------------------------------------------------------------------------------Nested
Loop (cost=4.91..38.44 rows=2 width=252) (actual time=0.659..13.404
rows=299 loops=1)
Output: s.way
-> Index Scan using us_west_planet_osm_polygon_pkey on
osm.us_west_planet_osm_polygon ap (cost=0.43..8.45 rows=1 width=252)
(actual time=0.017..0.018 rows=1 loops=1)
Output: ap.osm_id, ap.access, ap."addr:housename",
ap."addr:housenumber", ap."addr:interpolation", ap.admin_level,
ap.aerialway, ap.aeroway, ap.amenity, ap.area, ap.barrier, ap.bicycle,
ap.brand, ap.bridge, ap.boundary, ap.building, ap.construction, ap.covered
nomination, ap.disused, ap.embankment, ap.foot, ap."generator:source",
ap.harbour, ap.highway, ap.historic, ap.horse, ap.intermittent,
ap.junction, ap.landuse, ap.layer, ap.leisure, ap.lock, ap.man_made,
ap.military, ap.motorcar, ap.name, ap."natural", ap.office, ap.on
.population, ap.power, ap.power_source, ap.public_transport, ap.railway,
ap.ref, ap.religion, ap.route, ap.service, ap.shop, ap.sport, ap.surface,
ap.toll, ap.tourism, ap."tower:type", ap.tracktype, ap.tunnel, ap.water,
ap.waterway, ap.wetland, ap.width, ap.wood, ap.z_
Index Cond: (ap.osm_id = 179980467)
-> Bitmap Heap Scan on osm.us_west_planet_osm_polygon s
(cost=4.48..29.97 rows=2 width=252) (actual time=0.637..13.320 rows=299
loops=1)
Output: s.osm_id, s.access, s."addr:housename",
s."addr:housenumber", s."addr:interpolation", s.admin_level, s.aerialway,
s.aeroway, s.amenity, s.area, s.barrier, s.bicycle, s.brand, s.bridge,
s.boundary, s.building, s.construction, s.covered, s.culvert, s.cut
, s.embankment, s.foot, s."generator:source", s.harbour, s.highway,
s.historic, s.horse, s.intermittent, s.junction, s.landuse, s.layer,
s.leisure, s.lock, s.man_made, s.military, s.motorcar, s.name,
s."natural", s.office, s.oneway, s.operator, s.place, s.population, s
c_transport, s.railway, s.ref, s.religion, s.route, s.service, s.shop,
s.sport, s.surface, s.toll, s.tourism, s."tower:type", s.tracktype,
s.tunnel, s.water, s.waterway, s.wetland, s.width, s.wood, s.z_order,
s.way_area, s.way
Recheck Cond: ((ap.way ~ s.way) AND (ap.way ~ s.way))
Filter: _st_contains(ap.way, s.way)
Rows Removed by Filter: 484
-> Bitmap Index Scan on us_west_planet_osm_polygon_index
(cost=0.00..4.47 rows=6 width=0) (actual time=0.363..0.363 rows=783
loops=1)
Index Cond: ((ap.way ~ s.way) AND (ap.way ~ s.way))
Total runtime: 13.570 ms
(13 rows)
}}}
Not much of a difference, but different from yours in that mine still uses
a bitmap index scan instead of a plain index scan.
next defined this:
{{{
CREATE OR REPLACE FUNCTION public.st_withinol(
geom1 geometry,
geom2 geometry)
RETURNS boolean AS
'SELECT $2 OPERATOR(public.&&) $1 AND public._ST_Contains($2,$1)'
LANGUAGE sql IMMUTABLE
COST 100;
EXPLAIN ANALYZE VERBOSE select s.way FROM osm.us_west_planet_osm_polygon
s
join osm.us_west_planet_osm_polygon ap on ( st_withinol(s.way, ap.way) )
where ap.osm_id=179980467 ;
}}}
Still no difference, get a bitmap index scan again except slightly worse
because the && index scan does not remove as many false positives leaving
the _ST_Contains to do more work.
{{{
Nested Loop (cost=37.40..2510.63 rows=49298 width=252) (actual
time=0.719..16.919 rows=299 loops=1)
Output: s.way
-> Index Scan using us_west_planet_osm_polygon_pkey on
osm.us_west_planet_osm_polygon ap (cost=0.43..8.45 rows=1 width=252)
(actual time=0.012..0.013 rows=1 loops=1)
Output: ap.osm_id, ap.access, ap."addr:housename",
ap."addr:housenumber", ap."addr:interpolation", ap.admin_level,
ap.aerialway, ap.aeroway, ap.amenity, ap.area, ap.barrier, ap.bicycle,
ap.brand, ap.bridge, ap.boundary, ap.building, ap.construction,
ap.covered, ap.culvert, ap.cutting, ap.denomination, ap.disused,
ap.embankment, ap.foot, ap."generator:source", ap.harbour, ap.highway,
ap.historic, ap.horse, ap.intermittent, ap.junction, ap.landuse, ap.layer,
ap.leisure, ap.lock, ap.man_made, ap.military, ap.motorcar, ap.name,
ap."natural", ap.office, ap.oneway, ap.operator, ap.place, ap.population,
ap.power, ap.power_source, ap.public_transport, ap.railway, ap.ref,
ap.religion, ap.route, ap.service, ap.shop, ap.sport, ap.surface, ap.toll,
ap.tourism, ap."tower:type", ap.tracktype, ap.tunnel, ap.water,
ap.waterway, ap.wetland, ap.width, ap.wood, ap.z_order, ap.way_area,
ap.way
Index Cond: (ap.osm_id = 179980467)
-> Bitmap Heap Scan on osm.us_west_planet_osm_polygon s
(cost=36.96..2500.18 rows=200 width=252) (actual time=0.703..16.831
rows=299 loops=1)
Output: s.osm_id, s.access, s."addr:housename",
s."addr:housenumber", s."addr:interpolation", s.admin_level, s.aerialway,
s.aeroway, s.amenity, s.area, s.barrier, s.bicycle, s.brand, s.bridge,
s.boundary, s.building, s.construction, s.covered, s.culvert, s.cutting,
s.denomination, s.disused, s.embankment, s.foot, s."generator:source",
s.harbour, s.highway, s.historic, s.horse, s.intermittent, s.junction,
s.landuse, s.layer, s.leisure, s.lock, s.man_made, s.military, s.motorcar,
s.name, s."natural", s.office, s.oneway, s.operator, s.place,
s.population, s.power, s.power_source, s.public_transport, s.railway,
s.ref, s.religion, s.route, s.service, s.shop, s.sport, s.surface, s.toll,
s.tourism, s."tower:type", s.tracktype, s.tunnel, s.water, s.waterway,
s.wetland, s.width, s.wood, s.z_order, s.way_area, s.way
Recheck Cond: (ap.way && s.way)
Filter: _st_contains(ap.way, s.way)
Rows Removed by Filter: 513
-> Bitmap Index Scan on us_west_planet_osm_polygon_index
(cost=0.00..36.91 rows=600 width=0) (actual time=0.309..0.309 rows=812
loops=1)
Index Cond: (ap.way && s.way)
Total runtime: 17.111 ms
}}}
Now strk for your comment about combining both:
{{{
CREATE OR REPLACE FUNCTION public.st_withinolc(
geom1 geometry,
geom2 geometry)
RETURNS boolean AS
'SELECT $2 OPERATOR(public.&&) $1 AND $2 OPERATOR(public.~) $1 AND
public._ST_Contains($2,$1)'
LANGUAGE sql IMMUTABLE
COST 100;
}}}
{{{
EXPLAIN ANALYZE VERBOSE select s.way FROM osm.us_west_planet_osm_polygon
s
join osm.us_west_planet_osm_polygon ap on ( st_withinolc(s.way, ap.way) )
where ap.osm_id=179980467 ;
}}}
{{{
Nested Loop (cost=0.85..17.15 rows=49 width=252) (actual
time=0.327..13.473 rows=299 loops=1)
Output: s.way
-> Index Scan using us_west_planet_osm_polygon_pkey on
osm.us_west_planet_osm_polygon ap (cost=0.43..8.45 rows=1 width=252)
(actual time=0.013..0.013 rows=1 loops=1)
Output: ap.osm_id, ap.access, ap."addr:housename",
ap."addr:housenumber", ap."addr:interpolation", ap.admin_level,
ap.aerialway, ap.aeroway, ap.amenity, ap.area, ap.barrier, ap.bicycle,
ap.brand, ap.bridge, ap.boundary, ap.building, ap.construction,
ap.covered, ap.culvert, ap.cutting, ap.denomination, ap.disused,
ap.embankment, ap.foot, ap."generator:source", ap.harbour, ap.highway,
ap.historic, ap.horse, ap.intermittent, ap.junction, ap.landuse, ap.layer,
ap.leisure, ap.lock, ap.man_made, ap.military, ap.motorcar, ap.name,
ap."natural", ap.office, ap.oneway, ap.operator, ap.place, ap.population,
ap.power, ap.power_source, ap.public_transport, ap.railway, ap.ref,
ap.religion, ap.route, ap.service, ap.shop, ap.sport, ap.surface, ap.toll,
ap.tourism, ap."tower:type", ap.tracktype, ap.tunnel, ap.water,
ap.waterway, ap.wetland, ap.width, ap.wood, ap.z_order, ap.way_area,
ap.way
Index Cond: (ap.osm_id = 179980467)
-> Index Scan using us_west_planet_osm_polygon_index on
osm.us_west_planet_osm_polygon s (cost=0.41..8.68 rows=1 width=252)
(actual time=0.291..13.376 rows=299 loops=1)
Output: s.osm_id, s.access, s."addr:housename",
s."addr:housenumber", s."addr:interpolation", s.admin_level, s.aerialway,
s.aeroway, s.amenity, s.area, s.barrier, s.bicycle, s.brand, s.bridge,
s.boundary, s.building, s.construction, s.covered, s.culvert, s.cutting,
s.denomination, s.disused, s.embankment, s.foot, s."generator:source",
s.harbour, s.highway, s.historic, s.horse, s.intermittent, s.junction,
s.landuse, s.layer, s.leisure, s.lock, s.man_made, s.military, s.motorcar,
s.name, s."natural", s.office, s.oneway, s.operator, s.place,
s.population, s.power, s.power_source, s.public_transport, s.railway,
s.ref, s.religion, s.route, s.service, s.shop, s.sport, s.surface, s.toll,
s.tourism, s."tower:type", s.tracktype, s.tunnel, s.water, s.waterway,
s.wetland, s.width, s.wood, s.z_order, s.way_area, s.way
Index Cond: ((ap.way && s.way) AND (ap.way ~ s.way))
Filter: _st_contains(ap.way, s.way)
Rows Removed by Filter: 484
Total runtime: 13.660 ms
}}}
switches to a regular old index scan instead of bitmap index, but timing I
get is worse by a little bit presumably
because I have that extra && check which is redundant.
I tried swapping the order of ~~ and && and got 13.521ms presumably
because ~~ is just more efficient so gives && less to check.
--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3739#comment:14>
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.
More information about the postgis-tickets
mailing list