[postgis-users] Index problem

[Searle Ross]

Hi

I am trying to port an application we have from a Sun box to a pc. All
is well except for a problem with using attribute queries. The interface
we have lets you type in a value and zoom to that particular feature
using a mapserver itemquery. Works fine in the existing app (postgres
7.1 and postgis 0.6) but can't get the pc version (latest of both) to
use the indexes correctly. I have an index on the oid, an index on the
item field and a a gist index on the geometry.

The browser eventually times out and returns the following

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') )) 

When I run this with an explain the following plan is returned

Index Scan using dcdb_sp_idx on sedcdb  (cost=0.00..37.55 rows=1
width=36)    -----    which is the spatial index

suggesting that it thinks it is easier to return all the spatial records
and then search for the item instead of the other way round.

Simple select queries run on the oid and lotplan fields work fine.

I noted Pauls comments regarding filters in a recent posting but would
think this is a different case given that we are using an itemquery.
Anyone got any ideas

Thanks
Ross

PS is it possible to get postgis to return its query somewhere for
debugging purposes, as it takes forever to wait for the browser to time
out and return the query.





************************************************************************
The information in this e-mail together with any attachments is
intended only for the person or entity to which it is addressed
and may contain confidential and/or privileged material.

Any form of review, disclosure, modification, distribution
and/or publication of this e-mail message is prohibited.  

If you have received this message in error, you are asked to
inform the sender as quickly as possible and delete this message
and any copies of this message from your computer and/or your
computer system network.  
************************************************************************