Just to add a bit to this, this sort of index selectivity problem is caused 
whan you have a choice of two or more different indexes to select rows from one 
table. The optimizer wants to use the index which selects the fewest rows, as 
it will then have to filter based on the other criteria, row by row. Consider 
the following query:

Select foo from the_table where id1=1 and the_geom && <bbox>;

If there is an index on id1, and a spatial index on the_geom, which one should 
be used? Well, if id1 has a unique/primary key index on it, then it will return 
a single row, and then all we need to do is manually check if the_geom is in 
the <bbox>. If id1 just has a non-unique index on it, then we will still be 
able to find all of the matching rows very quickly, but each row will have to 
be tested to determine whether it is inside the <bbox>. Depending on how many 
rows are found by the spatial index, it may be faster to use the spatial index 
to select the rows, and then filter them for id1=1.

This is where dave's USE_STATS functionality comes in. It helps postgres to 
more accurately determine the number of rows that will be found by the spatial 
index. Then postgres can make a better decision about whether to use the 
spatial index, or another index. 

This sort of query is generated by mapserver if you do a 'filter' in your 
layer, or if you use a subselect with a where clause in your 'data' statement.

Just to spread the confusion...

Chris

Quoting Paul Ramsey <pramsey@refractions.net>:

> 
> 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@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@refractions.net
>       Phone: (250) 885-0632
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 





