|
Home > Archive > PostgreSQL Bugs > January 2006 > BUG #2196: Useless RECHECK on RTREE index
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
BUG #2196: Useless RECHECK on RTREE index
|
|
|
|
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)::doubl
e 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)::doubl
e 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)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Tom Lane 2006-01-23, 1:24 pm |
| "" <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)::doubl
e 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)::doubl
e 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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
|
|
|
|
|