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.
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 |