pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFind out whether a view's column is indexed?
4+ messages / 3 participants
[nested] [flat]
* Find out whether a view's column is indexed?
@ 2004-06-10 07:56 Martin Schäfer <Martin.Schaefer@cadcorp.com>
2004-06-10 08:41 ` Re: Find out whether a view's column is indexed? Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 4+ messages in thread
From: Martin Schäfer @ 2004-06-10 07:56 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Find out whether a view's column is indexed?
2004-06-10 07:56 Find out whether a view's column is indexed? Martin Schäfer <Martin.Schaefer@cadcorp.com>
@ 2004-06-10 08:41 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2004-06-10 08:41 UTC (permalink / raw)
To: Martin Schäfer <Martin.Schaefer@cadcorp.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Find out whether a view's column is indexed?
@ 2004-06-10 13:48 Martin Schäfer <Martin.Schaefer@cadcorp.com>
2004-06-10 14:06 ` Re: Find out whether a view's column is indexed? Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 1 reply; 4+ messages in thread
From: Martin Schäfer @ 2004-06-10 13:48 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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@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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Find out whether a view's column is indexed?
2004-06-10 13:48 Re: Find out whether a view's column is indexed? Martin Schäfer <Martin.Schaefer@cadcorp.com>
@ 2004-06-10 14:06 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 0 replies; 4+ messages in thread
From: Karsten Hilbert @ 2004-06-10 14:06 UTC (permalink / raw)
To: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-06-10 14:06 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-10 07:56 Find out whether a view's column is indexed? Martin Schäfer <Martin.Schaefer@cadcorp.com>
2004-06-10 08:41 ` Richard Huxton <dev@archonet.com>
2004-06-10 13:48 Re: Find out whether a view's column is indexed? Martin Schäfer <Martin.Schaefer@cadcorp.com>
2004-06-10 14:06 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox