Thread: Slow information_schema.views

Hello,

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
checkfunctions. I'm not worried about privilege checks and I don't need a nicely formatted definition. Is there a way
offinding 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
onbut that's not what I'm after. 

Regards
Oliver Kohll
www.agilebase.co.uk

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