In Pg12 I noticed the output from EXPLAIN ANALYZE no longer displays the recheck condition when using GIST indexes, such as:
Recheck Cond: (b.way ~ way)
Was this intentional? I double checked the release notes and didn't find any changes that indicate this was expected.
An example is a query such as:
EXPLAIN (ANALYZE) SELECT r.highway, r.ref, r.way FROM osm.boundary_polygon b INNER JOIN osm.road_line r ON ST_Contains(b.way, r.way) WHERE b.name = 'Jefferson County' ;
Output from PG11 shows the recheck cond:
Nested Loop (cost=29.28..2610.82 rows=220 width=237) (actual time=20.466..1900.878 rows=62585 loops=1) -> Seq Scan on boundary_polygon b (cost=0.00..132.16 rows=1 width=5627) (actual time=0.206..0.323 rows=1 loops=1) Filter: (name = 'Jefferson County'::text) Rows Removed by Filter: 492 -> Bitmap Heap Scan on road_line r (cost=29.28..2476.45 rows=220 width=237) (actual time=20.251..1879.286 rows=62585 loops=1) Recheck Cond: (b.way ~ way) Filter: _st_contains(b.way, way) Rows Removed by Filter: 8803 Heap Blocks: exact=2273 -> Bitmap Index Scan on gix_osm_road_line (cost=0.00..29.23 rows=659 width=0) (actual time=14.517..14.517 rows=71388 loops=1) Index Cond: (b.way ~ way) Planning Time: 0.213 ms Execution Time: 1910.810 ms
Output from Pg12 does not show recheck:
Nested Loop (cost=29.24..18887.33 rows=39206 width=234) (actual time=12.757..1064.333 rows=62585 loops=1) -> Seq Scan on boundary_polygon b (cost=0.00..109.16 rows=1 width=5627) (actual time=0.169..0.238 rows=1 loops=1) Filter: (name = 'Jefferson County'::text) Rows Removed by Filter: 492 -> Bitmap Heap Scan on road_line r (cost=29.24..18777.51 rows=66 width=234) (actual time=12.578..1055.015 rows=62585 loops=1) Filter: st_contains(b.way, way) Rows Removed by Filter: 8803 Heap Blocks: exact=2505 -> Bitmap Index Scan on gix_osm_road_line (cost=0.00..29.23 rows=659 width=0) (actual time=11.285..11.285 rows=71388 loops=1) Index Cond: (way @ b.way) Planning Time: 0.613 ms Execution Time: 1068.370 ms