Re: How to find out programmatically whether a query on a view will use an index?

> > I'm using the PostGIS spatial extension. Some of my spatial
> queries (like
> > live zooming and panning) should only be performed when the column
> > containing the spatial data is spatially indexed, otherwise
> the first query
> > takes almost forever and users will just kill the
> application out of
> > frustration.
>
> If the real problem is long running queries, maybe using a
> statement timeout
> will solve your problem?

Using a timeout to test for the presence of an index is not exact enough: I can't guard myself against false positives or false negatives. If the server is very busy at the moment all views might seem to be 'unindexed', i.e. unusable for live zooming and panning. The next day it might look different.

I need to know in advance whether the queries would use a spatial index on the views. If no spatial index would be used, I have to make a local copy of (a subset of) the view (or table), create a local index and display the local copy instead. This is better than waiting for the timeout to expire and display nothing.

With Oracle I can fire a spatial query on a view or table, and if the spatial column is not indexed, the entire query will fail. Unfortunately, with PostgreSQL, the spatial queries always succeed.

Martin