Re: Index on points - Mailing list pgsql-general

From Jeff Davis
Subject Re: Index on points
Date
Msg-id 1285427725.24585.7.camel@jdavis-laptop
Whole thread Raw
In response to Re: Index on points  (A B <gentosaker@gmail.com>)
Responses Re: Index on points
List pgsql-general
On Sat, 2010-09-25 at 09:18 +0200, A B wrote:
> fleet (id bigserial primary key, location point);
>
...
> 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
>

...

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

Looks like there's no entry in the box_ops opclass for point <@ box, but
there is an entry for box <@ box.

So, try:

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

There's no reason that there couldn't be a point <@ box operator in the
opclass, but nobody really uses these geometric types that come with
core postgres (at least, not that I can tell). PostGIS is a dedicated
project that has removed most of the justification for trying to improve
the built-in geometric types. However, keep in mind that PostGIS is
under a different license (GPL, I think).

Regards,
    Jeff Davis


pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: psql copy command - 1 char limitation on delimiter
Next
From: "Andrus"
Date:
Subject: 9.0RC1 error variable not found in subplan target lists