[postgis-users] Index problem

[Paul Ramsey]

Searle Ross wrote:
> 
> msPOSTGISLayerWhichShapes(): Query error. Error executing POSTGIS SQL
> statement (in FETCH ALL): DECLARE mycursor BINARY CURSOR FOR SELECT
> asbinary(force_collection(force_2d(the_geom)),'NDR'),oid::text from
> sedcdb WHERE (lotplan = '3RW9') and (the_geom && setSRID( 'BOX3D(147.9
> -28.52,154.6 -23.27)'::BOX3D,find_srid('','sedcdb','the_geom') ))

Maybe we need to review the postgis/mapserver itemquery code, because
this SQL definately includes a spatial component. Hrm. (Quite a large
spatial component.) The slowness is definately cause by the same thing
which was slowing the filter, namely the prioritization of the spatial
component over the attribute. The "final solution" is unfortunately
extremely involved, namely the proper integration of spatial indexes
into the optimizer, with scores which match up properly right with the
attribute query scores. Right now we rig the election so we always win
:/ However, this particular problem might just involve a change to the
postgis/mapserver layer, since an itemquery should never have a spatial
component to it. 

Hopefully,
Paul