[postgis-users] Insert performance issues

[Paul Ramsey]

Mapserver always does its queries within the context of a bounding 
rectangle, and this can in many cases cause suboptimal index usage by 
PostGIS.  The way around this is to ensure you use the STATS extension 
in PostGIS, so that the query planner can choose the correct index 
every time.

The fact that your query is nested will not affect how the planner uses 
indexes, it will look at the complete context of the query and figure 
out the optimal plan given the knowledge it has.  If you have not 
enabled STATS, it will always assume very high selectivity for spatial 
indexes.  In the case of large bounding boxes, this selectivity 
assumption will be wrong, and the result can be really stupid query 
plans.  So the planner is very smart, but only within the context of 
what it knows, and without STATS enabled, what it knows is sometimes 
demonstrably incorrect.

P.

On Thursday, October 23, 2003, at 10:12 AM, chodgson at refractions.net 
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" ?
>
      Paul Ramsey
      Refractions Research
      Email: pramsey at refractions.net
      Phone: (250) 885-0632