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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
