On Thu, Jan 16, 2025 at 3:05 PM Zhang Mingli <zmlpostg...@gmail.com> wrote: > > > Thank you for your help! That’s certainly a viable approach to logging the > plan during the REFRESH operation. > However, I want to clarify that we’re particularly interested in examining > the SQL cases. When there are numerous queries that may also include REFRESH, > it can be challenging to sift through the logs and identify the specific > query we want to analyze using SQL. > > Ideally, it would be beneficial if we could obtain an explanation of the SQL > associated with a REFRESH command, allowing us to see the SELECT plan without > having to execute the REFRESH itself. > We could limit EXPLAIN utility command to only REFRESH , on the AS SELECT > part, similar to how we can with CREATE TABLE AS, is it possible and > worthwhile?
you can use pg_get_viewdef to get the matview definition then use it in the plpgsql function. create function explain_query_json(query_sql text) returns table (explain_line json) language plpgsql as $$ begin return query execute 'EXPLAIN (FORMAT json) ' || query_sql; end; $$;
