Re: 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