Re: Question on "box @> point" using GiST index on boxes

Hello,

I would like to test the containment of a point against many boxes.

I did not find a way to express "box @> point" in straightforward way such
that the GiST index on the boxes is exploited.
The only way to use a point directly is to turn the box into a polygon.

Is it a missing feature?

The way I currently represent a point p is as: box(p, p). In this case,
the GiST index use kicks in.

Regards,
Ralf

--

drop table if exists boxes cascade;
create table boxes (
b box
);
-- Some random data
insert into boxes
select box(point((random()*100)::int, (random()*100)::int),
point((random()*100)::int, (random()*100)::int))
from (select * from generate_series(1,1000)) as t;
create index i on boxes using gist (b);
vacuum analyze boxes;

explain select * from boxes where b @> '((0,0),(0,0))'::box;
explain select * from boxes where b::polygon @> '(0,0)'::point;

RESULT:

QUERY PLAN
----------------------------------------------------------------
Index Scan using i on boxes (cost=0.00..8.27 rows=1 width=32)
Index Cond: (b @> '(0,0),(0,0)'::box)
(2 rows)

QUERY PLAN
---------------------------------------------------------
Seq Scan on boxes (cost=0.00..23.00 rows=500 width=32)
Filter: ((b)::polygon @> '(0,0)'::point)
(2 rows)

Ralf Rantzau <rrantzau(at)gmail(dot)com> writes:
> I would like to test the containment of a point against many boxes.
> I did not find a way to express "box @> point" in straightforward way such
> that the GiST index on the boxes is exploited.

Yeah, that operator is not in any GiST opclass, as you can easily verify
with a look into pg_amop. It seems like it probably wouldn't be
terribly hard to add it (and related box vs point operators) to GiST
box_ops, but nobody's bothered to do the legwork.

> The way I currently represent a point p is as: box(p, p). In this case,
> the GiST index use kicks in.

Right, that's the standard workaround. Note that I'm not sure that
direct use of the box vs point operators would be any faster, but it'd
surely be less surprising.

regards, tom lane