EXPLAIN ANALYZE not displaying recheck condition

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

EXPLAIN ANALYZE not displaying recheck condition

ryanlambert
Hello,

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

Thanks,

Ryan Lambert
RustProof Labs
Ryan Lambert
RustProof  Labs
Reply | Threaded
Open this post in threaded view
|

Re: EXPLAIN ANALYZE not displaying recheck condition

Jeff Janes
On Thu, Nov 7, 2019 at 5:04 PM Ryan Lambert <[hidden email]> wrote:
Hello,

In Pg12 I noticed the output from EXPLAIN ANALYZE no longer displays the recheck condition when using GIST indexes, such as:


I can't reproduce this using postgis 2.5.2 in Pg12.  What version of postgis are you using, and how did you install it?

I use this sequence of commands, and I still get the recheck listed:

create schema osm;
create extension postgis;
create table osm.boundary_polygon (way geometry, name text);
create table osm.road_line (way geometry, highway text, ref text);
create index on osm.road_line using gist (way );
set enable_indexscan TO off;
set enable_seqscan TO off;
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' ;

Cheers,

Jeff
Reply | Threaded
Open this post in threaded view
|

Re: EXPLAIN ANALYZE not displaying recheck condition

Jeff Janes
On Fri, Nov 8, 2019 at 10:47 AM Jeff Janes <[hidden email]> wrote:
On Thu, Nov 7, 2019 at 5:04 PM Ryan Lambert <[hidden email]> wrote:
Hello,

In Pg12 I noticed the output from EXPLAIN ANALYZE no longer displays the recheck condition when using GIST indexes, such as:


I can't reproduce this using postgis 2.5.2 in Pg12.  What version of postgis are you using, and how did you install it?

This looks like an intended outcome of this commit (in postgis, not in PostgreSQL):

commit 2f3e2783e7e1fa57a5a994e0c82d9784e94895b9
Author: Paul Ramsey <[hidden email]>
Date:   Wed Mar 6 17:27:24 2019 +0000

    Use "support function" API in PgSQL 12+ to provide index support for ST_Intersects and company, in place of old SQL inlining method.
    Closes #4341
   
My interpretation is that the filter is at least as stringent as the recheck, so there is nothing to recheck.

Cheers,

Jeff
Reply | Threaded
Open this post in threaded view
|

Re: EXPLAIN ANALYZE not displaying recheck condition

ryanlambert
Thank you Jeff! 
So this is an expected change when using both Pg12 and PostGIS 3 while using an older version of either will still include the recheck.  I confirmed that PostGIS 3 with Pg11 still shows the recheck as expected.

Thanks,
Ryan 


On Fri, Nov 8, 2019 at 11:54 AM Jeff Janes <[hidden email]> wrote:
On Fri, Nov 8, 2019 at 10:47 AM Jeff Janes <[hidden email]> wrote:
On Thu, Nov 7, 2019 at 5:04 PM Ryan Lambert <[hidden email]> wrote:
Hello,

In Pg12 I noticed the output from EXPLAIN ANALYZE no longer displays the recheck condition when using GIST indexes, such as:


I can't reproduce this using postgis 2.5.2 in Pg12.  What version of postgis are you using, and how did you install it?

This looks like an intended outcome of this commit (in postgis, not in PostgreSQL):

commit 2f3e2783e7e1fa57a5a994e0c82d9784e94895b9
Author: Paul Ramsey <[hidden email]>
Date:   Wed Mar 6 17:27:24 2019 +0000

    Use "support function" API in PgSQL 12+ to provide index support for ST_Intersects and company, in place of old SQL inlining method.
    Closes #4341
   
My interpretation is that the filter is at least as stringent as the recheck, so there is nothing to recheck.

Cheers,

Jeff
Ryan Lambert
RustProof  Labs