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

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. 

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
forviews? 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

I think the information_schema.view_column_usage doesn't tell me which view column is based on which table column, it
onlysays 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
isnot 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
programmaticallyis nearly impossible. Anyway the words 'Index Scan' and 'Seq Scan' can change without notice, maybe
evenfrom one locale to another. 

Martin

> -----Original Message-----
> From: Richard Huxton [mailto:dev@archonet.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
>

> 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.
I think you are operating under the faulty assumption that
'Index Scan' in EXPLAIN output signifies that a column is
*indexed*. What it really tells you is whether an index is
actually *used* when getting data from a column. That of
course requires an index to be there. However, an index being
there doesn't guarantee it being used.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Hi All,

I am a relatively new user to postgres. I have created a function that compiles but
generates an error when executed. I know I am overlooking something simple. The
function and error are below.

CREATE OR REPLACE FUNCTION building_insert(varchar, int4, varchar) RETURNS int4 AS
'

/* Return code dictionary:       0 - Success       1 - Valid User, Insert Failed       2 - Invalid User, Abort
*/
DECLARE       p_user ALIAS FOR $1;       p_parcel_id ALIAS FOR $2;       p_name ALIAS FOR $3;

BEGIN
       IF p_user == \'mlong\' THEN               RETURN 2;       END IF;
       INSERT  INTO    building(        parcel_id,        name,        createdate    )VALUES(        p_parcel_id,
p_name,             now()    );
 

       RETURN 0;
END;
' LANGUAGE 'plpgsql' VOLATILE;

/* Query that generates error */
select building_insert('mlong', 20,'building 1');

ERROR:  operator does not exist: character varying == "unknown"
HINT:  No operator matches the given name and argument type(s). You may need to add
explicit type casts.
CONTEXT:  PL/pgSQL function "building_insert" line 14 at if

Thanks,
Mike

Michael Long wrote:
> ERROR:  operator does not exist: character varying == "unknown"
> HINT:  No operator matches the given name and argument type(s). You may need to add
> explicit type casts.
> CONTEXT:  PL/pgSQL function "building_insert" line 14 at if

Common mistake, still make it myself on occasion. The '==' operator 
doesn't exist in plpgsql, you should use '=' when comparing and ':=' for 
assignment.

HTH
--   Richard Huxton  Archonet Ltd