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 for views? 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 geometry columns.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
