Slow information_schema.views

Oliver Kohll - Mailing Lists wrote:

I'm doing some SELECTs from information_schema.views to find views

with dependencies on other views,

i.e.

SELECT table_name FROM information_schema.views WHERE view_definition

ILIKE '%myviewname%';

and each is taking about 1/2 a second, which is getting a bit slow for

my use. There are 1213 views

listed in information_schema.views

Doing an explain analyze, it looks like the issue is likely to be the

pg_get_viewdef function or one

of the privilege check functions. I'm not worried about privilege

checks and I don't need a nicely

formatted definition. Is there a way of finding out how pg_get_viewdef

works so I can perhaps do a

lower level query?

I've previously used pg_catalog.pg_views which performs similarly.

Or is there a better way of finding view dependencies? I see there's a

pg_catalog entry for tables

that a view depends on but that's not what I'm after.

You can use pg_depend and pg_rewrite as follows:

SELECT DISTINCT r.ev_class::regclass
FROM pg_depend d JOIN
pg_rewrite r ON (d.objid = r.oid)
WHERE d.classid = 'pg_rewrite'::regclass
AND d.refclassid = 'pg_class'::regclass
AND r.ev_class <> d.refobjid
AND d.refobjid::regclass::text LIKE '%myviewname%';

I didn't test it very much, so play around with it a little before
you trust it.

I don't know if it will perform better in your case, but it should
return more appropriate results
(you don't want to find VIEW dummy AS SELECT * FROM t WHERE a =
'myviewname').

Yours,
Laurenz Albe

On 22 Mar 2012, at 10:17, Albe Laurenz wrote:

Or is there a better way of finding view dependencies? I see there's a

pg_catalog entry for tables

that a view depends on but that's not what I'm after.

You can use pg_depend and pg_rewrite as follows:

SELECT DISTINCT r.ev_class::regclass
FROM pg_depend d JOIN
pg_rewrite r ON (d.objid = r.oid)
WHERE d.classid = 'pg_rewrite'::regclass
AND d.refclassid = 'pg_class'::regclass
AND r.ev_class <> d.refobjid
AND d.refobjid::regclass::text LIKE '%myviewname%';

I didn't test it very much, so play around with it a little before
you trust it.

I don't know if it will perform better in your case, but it should
return more appropriate results
(you don't want to find VIEW dummy AS SELECT * FROM t WHERE a =
'myviewname').

Yours,
Laurenz Albe

Thank you - I did come to a similar method yesterday following some pointers from previous messages but I'm glad to have some confirmation it's the right direction. It does perform an order of magnitude faster for me, from 500ms down to under 20ms. My exact query is

SELECT distinct dependent.relname
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class as dependent ON pg_rewrite.ev_class = dependent.oid
JOIN pg_class as dependee ON pg_depend.refobjid = dependee.oid
WHERE dependee.relname = 'myviewname'
AND dependent.relname != 'myviewname'

Haven't tested this much yet either. I'll compare yours to mine and check the differences.

Regards
Oliver
www.agilebase.co.uk