From: Jim Nasby <jim.nasby@gmail.com>
To: Jeremiah Bauer <jbauer@agristats.com>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: [EXTERNAL]Re: Refresh Materialized View Issue
Date: Fri, 12 Jan 2024 13:11:05 -0600
Message-ID: <f151126e-ffb8-4481-9b1f-8b43950b6b2f@gmail.com> (raw)
In-Reply-To: <CH2PR19MB35760B9E8A00E5631EB44EAFC26F2@CH2PR19MB3576.namprd19.prod.outlook.com>
References: <CH2PR19MB357660FC69F24AE44EA5FF92C2682@CH2PR19MB3576.namprd19.prod.outlook.com>
<CANzqJaDY=0fuVrNveyZJpf+-Z=HhnisqDU31N1d4+bYAPjGTGQ@mail.gmail.com>
<CH2PR19MB3576ED0CE09DF08D35E3BBFAC2682@CH2PR19MB3576.namprd19.prod.outlook.com>
<CANzqJaCOFt4tsv=uYn+LtYbx4nFB+k1Mig7CdVONKzU8gM8oCg@mail.gmail.com>
<3a8e256d-707b-48f0-b71a-b721964bafa5@gmail.com>
<CH2PR19MB35760B9E8A00E5631EB44EAFC26F2@CH2PR19MB3576.namprd19.prod.outlook.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: jim.nasby@gmail.com, jbauer@agristats.com
Subject: Re: [EXTERNAL]Re: Refresh Materialized View Issue
In-Reply-To: <f151126e-ffb8-4481-9b1f-8b43950b6b2f@gmail.com>
References: <CH2PR19MB357660FC69F24AE44EA5FF92C2682@CH2PR19MB3576.namprd19.prod.outlook.com> <CANzqJaDY=0fuVrNveyZJpf+-Z=HhnisqDU31N1d4+bYAPjGTGQ@mail.gmail.com> <CH2PR19MB3576ED0CE09DF08D35E3BBFAC2682@CH2PR19MB3576.namprd19.prod.outlook.com> <CANzqJaCOFt4tsv=uYn+LtYbx4nFB+k1Mig7CdVONKzU8gM8oCg@mail.gmail.com> <3a8e256d-707b-48f0-b71a-b721964bafa5@gmail.com> <CH2PR19MB35760B9E8A00E5631EB44EAFC26F2@CH2PR19MB3576.namprd19.prod.outlook.com> <f151126e-ffb8-4481-9b1f-8b43950b6b2f@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox