[postgis-users] Why does this take almost 3 hours?

[Markus Schaber]

Hi, Ethan,

Ethan Alpert wrote:

> select catalogid,cii_region from new_snaps a, nga_reg b where
> distance(a.the_geom,b.the_geom) <= 0 and catalogid = ?

What about the following query:


select catalogid,cii_region from new_snaps a, nga_reg b where
(a.the_geom && b.the_geom) and distance(a.the_geom,b.the_geom) <= 0 and
catalogid = ?

This should use a quick bbox scan even before the distance calculation,
and an utilize one or even both geometric indices on the the_geom columns.

HTH,
Markus