[postgis-users] Insert performance issues

[David Blasby]

Jonathan W. Lowe wrote:
> What I'm wondering is if Mapserver will force use of an overlap (&&)
> that uses the index on parcel.the_geom, but prevents use of another
> index on parcel.apn.  Or, does the SQL within the parenthesis run
> independently of the surrounding "the_geom from (...) as listed_homes
> using unique oid using srid=26943" ?
> 
> I ask in order to simulate the SQL for zoomed in Mapserver views for
> EXPLAIN tests on the PostgreSQL command line.

If you find its using the geometry index when you dont think it should 
be, you can turn on the stats package.

I recently sent out a few messages on how to do this, but its basically:

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.

dave