Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group