Find out whether a view's column is indexed?

From: Martin Schäfer <Martin(dot)Schaefer(at)cadcorp(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Find out whether a view's column is indexed?
Date: 2004-06-10 07:56:05
Message-ID: 37936EEC582B394A9E1AA951991A551604C3BF@dev001_pdc.Dev.cadcorp.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2004-06-10 08:41:55 Re: Find out whether a view's column is indexed?
Previous Message Tom Lane 2004-06-10 06:32:14 Re: Getting FK relationships from information_schema