Re: Inquiry About Determining Parallel Plans for REFRESH MATERIALIZED VIEW

On Thu, Jan 16, 2025 at 3:05 PM Zhang Mingli <zmlpostgres(at)gmail(dot)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;
$$;