Search Postgresql Archives

Re: Index on points

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

 



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@xxxxxxxxxxx>:
> 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
>
>

-- 
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