Re: Find out whether a view's column is indexed?

I think the information_schema.view_column_usage doesn't tell me which view column is based on which table column, it only says generally which set of table/view columns are used for the view as a whole.

I need a bit more detailed information. If I have two views defined as this:

CREATE VIEW v1 AS SELECT a,b,c FROM t;
CREATE VIEW v2 AS SELECT b AS a,a AS b,c FROM t;

then their entries in view_column_usage is identical, but it is entirely possible that e.g. v1.a is indexed, but v2.a is not indexed.

I can do

EXPLAIN SELECT * FROM v2 WHERE a = 'foo';

and I can see whether a sequential scan or an index scan is performed, but parsing the output of EXPLAIN programmatically is nearly impossible. Anyway the words 'Index Scan' and 'Seq Scan' can change without notice, maybe even from one locale to another.

Martin

> -----Original Message-----
> From: Richard Huxton [mailto:dev(at)archonet(dot)com]
>
> Martin Schäfer wrote:
> > 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
> [snip]
> > 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'?
>
> If you're running 7.4 you can look in the information schema, in
> view_column_usage - that will tell you which table-columns a
> view uses.
>
>
> --
> Richard Huxton
> Archonet Ltd
>