Re: [EXTERNAL]Re: Refresh Materialized View Issue

On 1/12/24 6:34 AM, Jeremiah Bauer wrote:
>
> 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

Ok, so that's using a parallel query as well, just like REFRESH, so no
help there.

At this point I think you'd need to do some OS-level investigation using
a tool like perf to determine what the parent process is doing once the
workers finish.

BTW, I did just come across a script[1] that will print the query plan
for a running query. Based on what you've said I suspect it wouldn't
tell us much here, but I wanted to mention it.

1: https://github.com/StarfishStorage/explain-running-query
--
Jim Nasby, Data Architect, Austin TX