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)
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? |