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@j-davis.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
>
>