Why the cost time is difference between those two case about postgresql meterialized view?

Question

everyone. In postgresql 12, i have a materialized view like this:

CREATE MATERIALIZED VIEW mv
AS
  WITH cte AS (SELECT ...), cte2 AS (SELECT ...) SELECT ...
WITH DATA;

when i invoke REFRESH MATERIALIZED VIEW mv, or perform this command in linux crontab, it cost 4 hours.

but i perform WITH cte AS (SELECT ...), cte2 AS (SELECT ...) SELECT ... in pgadmin4 Query Tool, it only cost 7 seconds.

I don't know why it's too diffrence. I would like to cost 7 seconds in crontab, What shoud i do?

Answer 1

may be i find the reason, but i am not sure.

in WITH cte AS (SELECT ...), cte2 AS (SELECT ...) SELECT ..., there are two foreign data wrapper tables. I copy fdw table to local use CREATE TABLE test AS SELECT * FROM fdw.table_name, and replace fdw table use local table in cte. When i invoke CREATE MATERIALIZED VIEW mv AS ... WITH DATA it cost 12 seconds, REFRESH MATERIALIZED VIEW mv_name, it cost 2 seconds.

I think foreign data wrapper is the reason. why fdw cost so many times?

not network speed, -> SELECT * FROM fdw.table_name only cost seconds.

may be some transform statements in fdw, -> when REFRESH MATERIALIZED VIEW(use fdw table), cpu work 100% in whole 4 hours.