Re: Index on points

But how do I do it without Postgis?

Right now I have a table
fleet (id bigserial primary key, location point);

and I have filled it with random data and then tried selecting

explain analyze select count(1) from fleet where location <@
box(point(300,300),point(600,600));

to gather runningtime data, and then I have created and index (or I
think I have atleast)

CREATE INDEX fleet_location ON fleet USING GIST ( box(location,location) );

but I still get almost exaclty the same run time of the query

explain analyze select count(1) from fleet where location <@
box(point(300,300),point(600,600));

Aggregate (cost=100597.89..100597.90 rows=1 width=0) (actual
time=706.604..706.605 rows=1 loops=1)
-> Seq Scan on fleet (cost=0.00..94347.90 rows=2499996 width=0)
(actual time=0.252..701.624 rows=4533 loops=1)
Filter: (location <@ '(600,600),(300,300)'::box)
Total runtime: 706.675 ms

I have then tried to avoid the seq. scan by

set enable_seqscan=off;
set seq_page_cost=4000; (which would make it more expensive to scan,
wouldn't it?)

and the result is the same

Aggregate (cost=10127460749.89..10127460749.90 rows=1 width=0)
(actual time=799.077..799.078 rows=1 loops=1)
-> Seq Scan on fleet (cost=10000000000.00..10127454499.90
rows=2499996 width=0) (actual time=0.221..792.374 rows=4533 loops=1)
Filter: (location <@ '(600,600),(300,300)'::box)
Total runtime: 799.117 ms

So how do I create an index that gets used?
(I've run the queries a thousand times to make sure the total runtime
is consistent, and it is)

2010/9/23 Jeff Davis <pgsql(at)j-davis(dot)com>:
> On Thu, 2010-09-23 at 12:45 +0200, A B wrote:
>> Hello.
>>
>> If I have a table like this
>>
>> create table fleet ( ship_id   integer,  location point);
>
> I recommend taking a look into PostGIS: http://postgis.org
>
> Regards,
>        Jeff Davis
>
>