Search Postgresql Archives

Re: Slow information_schema.views

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux