Re: Index on points

On 23/09/10 11:45, A B wrote:
> Hello.
>
> If I have a table like this
>
> create table fleet ( ship_id integer, location point);
>
> and fill it with a lot of ships and their locations and then want to
> create an index on this to speed up operations on finding ships within
> a certain region (let's say its a rectangular region), how do I do
> this?
>
> I tried:
>
> CREATE INDEX my_index ON fleet USING gist ( box(location,location)); ?

That's the idea, but you'll need to be careful about how you're
searching against it. Remember, the index is on a box based on the
location, not the point location itself.

CREATE TABLE fleet (ship int, locn point);

INSERT INTO fleet SELECT (x*1000 + y), point(x,y)
FROM generate_series(0,999) x, generate_series(0,999) y;

CREATE INDEX fleet_locn_idx ON fleet USING gist( box(locn,locn) );
ANALYSE fleet;

EXPLAIN ANALYSE SELECT count(*) FROM fleet
WHERE box(locn,locn) <@ box '(10,10),(20,20)';
QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2654.84..2654.85 rows=1 width=0) (actual
time=4.611..4.612 rows=1 loops=1)
-> Bitmap Heap Scan on fleet (cost=44.34..2652.33 rows=1000
width=0) (actual time=4.344..4.491 rows=121 loops=1)
Recheck Cond: (box(locn, locn) <@ '(20,20),(10,10)'::box)
-> Bitmap Index Scan on fleet_locn_idx (cost=0.00..44.09
rows=1000 width=0) (actual time=4.311..4.311 rows=121 loops=1)
Index Cond: (box(locn, locn) <@ '(20,20),(10,10)'::box)
Total runtime: 4.694 ms
(6 rows)

DROP INDEX fleet_locn_idx;

EXPLAIN ANALYSE SELECT count(*) FROM fleet WHERE box(locn,locn) <@ box
'(10,10),(20,20)';
QUERY PLAN

----------------------------------------------------------------------------------------------------------------
Aggregate (cost=20885.50..20885.51 rows=1 width=0) (actual
time=551.756..551.757 rows=1 loops=1)
-> Seq Scan on fleet (cost=0.00..20883.00 rows=1000 width=0)
(actual time=5.142..551.624 rows=121 loops=1)
Filter: (box(locn, locn) <@ '(20,20),(10,10)'::box)
Total runtime: 551.831 ms
(4 rows)

--
Richard Huxton
Archonet Ltd