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?

Then it is hard to believe, unless your storage has massive problems with writing. — Laurenz Albe, Jul 06 '20 at 07:48
create new materialized view with data use the same sql statements with pgadmin4, it cost about 10 seconds. but run ```REFRESH MATERIALIZED VIEW``` it cost 4 hours. — Mal Sund, Jul 06 '20 at 07:49
What is the performance if you run `CREATE TABLE test AS SELECT ...;` with your query? — Laurenz Albe, Jul 06 '20 at 07:53
There is no ```UPDATE, DELETE, INSERT``` statements, only some ```SELECT, JOIN, GROUP BY``` in cte — Mal Sund, Jul 06 '20 at 08:26
Sorry, I meant `CREATE TABLE test AS WITH /* your CTEs */ SELECT ...;`. Essentially, use `CREATE TABLE AS` instead of `CREATE MATERIALIZED VIEW` and see if it makes a difference. — Laurenz Albe, Jul 06 '20 at 08:38
Well, then use that rather than a materialized view. But I don't think that is possible; the queries must be different. — Laurenz Albe, Jul 06 '20 at 10:57
another question similer to this, may be the same reason. ```CREATE VIEW v1 AS /* CTEs */ SELECT ...``` WHEN use ```SELECT * FROM v1``` cost 5 seconds, use ```SELECT * FROM v1 LIMIT 10``` cost 3 hours. — Mal Sund, Jul 06 '20 at 13:00
Aha. If you add `LIMIT 10`, that changes everything. Is there anything like that in your present case? — Laurenz Albe, Jul 06 '20 at 13:02

score 0 · Answer 1 · answered Jul 09 '20 at 10:34

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.

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

1 Answers1