Re: Index on points

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