Search Postgresql Archives

Re: Index on points

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux