Обсуждение: Inquiry About Determining Parallel Plans for REFRESH MATERIALIZED VIEW

On Thu, 16 Jan 2025 12:39:06 +0800
Zhang Mingli <zmlpostgres@gmail.com> wrote:

> Hi, all
> 
> 
> I am currently exploring the execution of the REFRESH MATERIALIZED VIEW command  and have a specific question
regardingthe underlying query plan. As you know, the REFRESH command is a utility command, and using EXPLAIN
REFRESH doesnot provide a plan structure for analysis.
 
> 
> During development, we want to ascertain whether the SELECT part of the REFRESH command executes with a parallel
plan.While I understand that we can run EXPLAIN on the SELECT statement directly, we are interested in knowing if there
isa method to determine the execution plan under the REFRESH command context.
 
> 
> Currently, we have configured the necessary settings for parallel queries and have been recording the execution time
ofthe REFRESH command to compare parallel and non-parallel executions.
 
> 
> However, I’m looking for a more definitive way to verify if the plan is indeed parallel during the execution of
the REFRESH command.
> 
> Any suggestions?

You will be able to log the plan during the REFRESH by using auto_explain and setting
log_analyze and log_nested_statements to on.

Regards,
Yugo Nagata

-- 
Yugo Nagata <nagata@sraoss.co.jp>

On Thu, Jan 16, 2025 at 3:05 PM Zhang Mingli <zmlpostgres@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
queriesthat may also include REFRESH, it can be challenging to sift through the logs and identify the specific query we
wantto analyze using SQL. 
>
> Ideally, it would be beneficial if we could obtain an explanation of the SQL associated with a REFRESH command,
allowingus 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
TABLEAS, 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;
$$;