[GENERAL] 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 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. Regards Oliver Kohll www.agilebase.co.uk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slow information_schema.views
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