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

From: Ralf Rantzau <rrantzau(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Question on "box @> point" using GiST index on boxes
Date: 2012-10-03 23:13:49
Message-ID: CAHQ+Zo2QVmzzQayCsbCX0KwSko6BY3MGuK5m1SDZVuBNuSP9Tw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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)

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2012-10-03 23:27:02 Re: Support for REINDEX CONCURRENTLY
Previous Message Tom Lane 2012-10-03 22:57:09 Re: do we EXEC_BACKEND on Mac OS X?