Re: Slow information_schema.views

[Oliver Kohll - Mailing Lists <oliver.lists@xxxxxxxxxx>]

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 = ''::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