Thread: BUG #2196: Useless RECHECK on RTREE index

BUG #2196: Useless RECHECK on RTREE index

From
""
Date:
The following bug has been logged online:

Bug reference:      2196
Logged by:
Email address:      agattik@gmail.com
PostgreSQL version: 8.1.2
Operating system:   alphaev68-dec-osf5.1a
Description:        Useless RECHECK on RTREE index
Details:

create table tpoints (x int, y int);
CREATE INDEX i_tpoints ON tpoints USING RTREE (box(point(x,y),
point(x,y)));
explain select * from tpoints where box '((0,0),(1,1))' && box(point(x,y),
point(x,y));


QUERY PLAN

----------------------------------------------------------------------------
----------------------------------------------------------------------------
---
 Bitmap Heap Scan on tpoints  (cost=1.05..10.46 rows=10 width=8)
   Recheck Cond: ('(1,1),(0,0)'::box && box(point((x)::double precision,
(y)::double precision), point((x)::double precision, (y)::double
precision)))
   ->  Bitmap Index Scan on i_tpoints  (cost=0.00..1.05 rows=10 width=0)
         Index Cond: ('(1,1),(0,0)'::box && box(point((x)::double precision,
(y)::double precision), point((x)::double precision, (y)::double
precision)))
(4 rows)

The RECHECK is necessary in case of polygons, but useless in case of box
overlap.

pg_amop.amopreqcheck is correctly set to false, but the planner seems to
ignore that.

select pg_amop.* from pg_amop join pg_opclass c ON (amopclaid=c.oid) join
pg_operator op ON (amopopr=op.oid) where opcname='box_ops' and
oprname='&&';
 amopclaid | amopsubtype | amopstrategy | amopreqcheck | amopopr
-----------+-------------+--------------+--------------+---------
       425 |           0 |            3 | f            |     500
      2593 |           0 |            3 | f            |     500
(2 rows)

Re: BUG #2196: Useless RECHECK on RTREE index

From
Tom Lane
Date:
"" <agattik@gmail.com> writes:
>  Bitmap Heap Scan on tpoints  (cost=1.05..10.46 rows=10 width=8)
>    Recheck Cond: ('(1,1),(0,0)'::box && box(point((x)::double precision,
> (y)::double precision), point((x)::double precision, (y)::double
> precision)))
>    ->  Bitmap Index Scan on i_tpoints  (cost=0.00..1.05 rows=10 width=0)
>          Index Cond: ('(1,1),(0,0)'::box && box(point((x)::double precision,
> (y)::double precision), point((x)::double precision, (y)::double
> precision)))
> (4 rows)

> The RECHECK is necessary in case of polygons, but useless in case of box
> overlap.

You don't understand what a bitmap scan's recheck condition is for.
The above plan is correct.

            regards, tom lane