[postgis-users] 2 Simple Questions

[Markus Schaber]

Hi, Dylan,

Dylan Lorimer wrote:

> As for indices, I created a GIST on my geometry and BTREE's on my most
> used attributes in WHERE clauses. Does that sound about right?

For the B-Side of the JOIN, the BTREEs will help.

But the GIST index is not used yet, as "distance()<foo" is not indexable
currently.

You can try to extend the ON clause with something like: ... ON
(a.thegeom && expand(b.the_geom, 18046.7)) AND
(distance_sphere(a.the_geom, b.the_geom) < 18046.7) WHERE ...

The expand part will draw a rectangular box around b.geom (the one found
by the attributes) that's the same size than your wanted distance. This
bbox is then queried via the && operator which can be indexed. We still
double-check with the distance function, however, as the bbox &&
operation has some false positives. (I can give you examples if this is
not clear.)

If you test that query, can you send us the output of:

EXPLAIN ANALYZE SELECT ...

This will show what the query planner estimates about the query, and
additionally statistics about what really happened. This way, we can see
whether the plan is likely to be optimal.

HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org