pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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






view thread (7+ messages)

Message-ID: <f151126e-ffb8-4481-9b1f-8b43950b6b2f@gmail.com>
Permalink:  ../f151126e-ffb8-4481-9b1f-8b43950b6b2f@gmail.com/
Also on:    postgresql.org/message-id/f151126e-ffb8-4481-9b1f-8b43950b6b2f@gmail.com

 · 

reply

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