That'd be a band-aid at best, because we know that the query used to
define the materialized view runs in a reasonable amount of time on it's
own, as does a CTAS. So either the REFRESH is doing something odd when
writing into the new relation (which looking at the code seems very
unlikely), or REFRESH is getting a different query plan for some reason.
Unfortunately, I don't know of any easy way to get the query plan for
the REFRESH (it might be possible via gdb, but I'm not sure). We do at
least know that the REFRESH is using parallel workers.
Can you post the output of EXPLAIN ANALYZE for the SELECT? That might
provide some clues.
Sure, here is the explain analyze for the select:
HashAggregate (cost=123986470.20..129486707.63 rows=164493082 width=24) (actual time=697250.385..741548.965 rows=59015171 loops=1)
Group Key: id1, id2, id3
Planned Partitions: 64 Batches: 65 Memory Usage: 328209kB Disk Usage: 6750176kB
-> Gather (cost=64653301.50..107228737.47 rows=328986164 width=24) (actual time=357598.331..594226.355 rows=161151623 loops=1)
Workers Planned: 2
Workers Launched: 2
-> HashAggregate (cost=64652301.50..74329121.07 rows=164493082 width=24) (actual time=357572.082..578038.457 rows=53717208 loops=3)
Group Key: id1, id2, id3
Planned Partitions: 64 Batches: 65 Memory Usage: 328209kB Disk Usage: 25774088kB
Worker 0: Batches: 65 Memory Usage: 328209kB Disk Usage: 25375784kB
Worker 1: Batches: 65 Memory Usage: 328209kB Disk Usage: 25382936kB
-> Parallel Seq Scan on large_table (cost=0.00..29740358.40 rows=685387840 width=24) (actual time=12.954..99596.289 rows=548310252 loops=3)
Planning Time: 5.380 ms
Execution Time: 745750.371 ms
CONFIDENTIALITY NOTICE: The information contained in this email (and any attachments) is privileged and confidential and protected from disclosure. If you are not the intended recipient of this email or the attachments, be aware that any disclosure, copying, distribution or use of this email or any attachment is strictly prohibited and you should not read the message or read or open any attachment. If you have received this email by mistake, please immediately notify the sender and delete it permanently from your system. Agri Stats, Inc. and its subsidiaries will not be held liable to any person or entity resulting from the unintended or unauthorized use of any information contained in this email.