Is there any way to find out whether a column that's used in a view is indexed?
The following query:
SELECT ic.relname AS index_name
FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a, pg_opclass oc, pg_namespace n
WHERE i.indrelid = bc.oid AND i.indexrelid = ic.oid AND i.indkey[0] = a.attnum AND i.indclass[0] = oc.oid
AND a.attrelid = bc.oid AND oc.opcname = 'gist_geometry_ops' AND n.oid = bc.relnamespace AND bc.relkind
~'[rv]' AND ic.relkind = 'i' AND n.nspname = 'foo' AND bc.relname = 'bar' AND a.attname = 'foobar';
lets me find out whether a table column is indexed, but it doesn't work for views. Is there anything that can be done
forviews? At least for simple views of the kind 'CREATE VIEW v AS SELECT a,b,c FROM t'?
Can anybody help?
Martin
PS: as you can see from the query I'm using the PostGIS extension, and I'm only interested in spatial indices on
geometrycolumns.
