[postgis-users] speed up spatial queries (distance() or intersects() or ???)

[David Blasby]

Daniel Faivre wrote:
> SELECT oid AS qoid FROM lg_annee WHERE  lga_annee='2003' and 
> lga_nat_cdn=2 AND lga_geom && box3d((SELECT
> buf_geom FROM buffer WHERE type='zrb' )) AND distance(lga_geom, (SELECT 
> buf_geom FROM buffer WHERE type='zrb' )) = 0;

Try enabling stats.

1. make sure you compiled with USE_STATS=1 (in the makefile)

2) Verify that there's an entry in geometry_columns for your geometry column

3) execute "SELECT UPDATE_GEOMETRY_STATS();"

3) Verify that there's a big HISTOGRAM2D(...) in the geometry_columns 
table for your data.

4) stats should now be enabled.  You'll find that the geometry index is 
being used in a "wiser" way than before. Also, your EXPLAIN ANALYSE 
estimated-number-of-rows should be much more accurate.


Whats happening is postgresql is doing the spatial filtering on the 
table too "early".

If this doesnt work, you might have to re-write your query so you are 
using an actual GEOMETRY instead of "SELECT  buf_geom FROM buffer WHERE 
type='zrb'".

dave