Changes between Initial Version and Version 1 of Ticket #3739
- Timestamp:
- Apr 16, 2017, 9:23:08 PM (7 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Ticket #3739 – Description
initial v1 4 4 5 5 Versions: 6 7 {{{ 6 8 "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 (...)" 7 9 "PostgreSQL 9.6.1, compiled by Visual C++ build 1800, 64-bit" 10 }}} 11 8 12 9 13 This happens on a full planet_osm_polygon table. 10 14 Definition: 15 16 {{{ 11 17 CREATE TABLE public.planet_osm_polygon 12 18 ( … … 29 35 USING btree 30 36 (osm_id); 37 }}} 31 38 32 39 I've done an analyze planet_osm_polygon but still the query planner surprises me. 33 40 41 42 {{{ 34 43 select s.way from planet_osm_polygon s 35 44 join planet_osm_polygon ap on (ST_Within(s.way,ap.way)) 36 45 where ap.osm_id=123456 37 46 47 }}} 48 38 49 results in a pkey lookup followed by a bitmap index scan 50 51 {{{ 39 52 Nested Loop (cost=6339.51..360823.14 rows=88540 width=229) 40 53 Output: s.way … … 48 61 -> Bitmap Index Scan on planet_osm_polygon_index (cost=0.00..6316.81 rows=265621 width=0) 49 62 Index Cond: (ap.way ~ s.way) 63 }}} 64 50 65 51 66 adding ap.way ~ s.way and to the join condition: 67 68 69 {{{ 52 70 select s.way from planet_osm_polygon s 53 71 join planet_osm_polygon ap on (ap.way ~ s.way and ST_Within(s.way,ap.way)) 54 72 where ap.osm_id=123456 73 }}} 74 55 75 56 76 results in a pkey lookup and a normal index-scan 77 78 79 {{{ 57 80 Nested Loop (cost=1.12..373.05 rows=89 width=229) 58 81 Output: s.way … … 65 88 Filter: _st_contains(ap.way, s.way) 66 89 90 }}} 91 67 92 One thing stands out here: the duplicate index condition ap.way ~ s.way 68 93 69 94 My question is: why isn't it planning to use the normal index-scan without adding ap.way ~ s.way. 70 95 After all, it is part of the st_within definition: 96 97 98 {{{ 71 99 CREATE OR REPLACE FUNCTION public.st_within( 72 100 geom1 geometry, … … 76 104 LANGUAGE sql IMMUTABLE 77 105 COST 100; 106 107 }}}
