pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim Nasby <jim.nasby@gmail.com>
To: Ron Johnson <ronljohnsonjr@gmail.com>
To: Jeremiah Bauer <jbauer@agristats.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Refresh Materialized View Issue
Date: Thu, 11 Jan 2024 18:15:41 -0600
Message-ID: <3a8e256d-707b-48f0-b71a-b721964bafa5@gmail.com> (raw)
In-Reply-To: <CANzqJaCOFt4tsv=uYn+LtYbx4nFB+k1Mig7CdVONKzU8gM8oCg@mail.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>

On 1/11/24 3:40 PM, Ron Johnson wrote:
> On Thu, Jan 11, 2024 at 3:50 PM Jeremiah Bauer <jbauer@agristats.com 
> <mailto:jbauer@agristats.com>> wrote:
> 
>         My question is: what indexes are on public.large_table? 
>         Hopefully there's a compound b-tree index on id1, id2, id3.
> 
>     There is not, after further investigation.  There are these 4
>     indexes that involve id1, id2, and id3.  Should I try creating an
>     index on all three of the columns?
> 
>     CREATE INDEX IF NOT EXISTS idx_large_table_id1
> 
> [snip]
> 
>     CREATE INDEX IF NOT EXISTS idx_large_table_id2
> 
> [snip]
> 
>     CREATE INDEX IF NOT EXISTS idx_large_table_id3
> 
> [snip]
> 
>     CREATE INDEX IF NOT EXISTS idx_large_table_id2_id3
> 
> [snip]
> I'd strongly think about creating such an index, since the current 
> indices don't help much.

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.
-- 
Jim Nasby, Data Architect, Austin TX






view thread (7+ messages)  latest in thread

Message-ID: <3a8e256d-707b-48f0-b71a-b721964bafa5@gmail.com>
Permalink:  ../3a8e256d-707b-48f0-b71a-b721964bafa5@gmail.com/
Also on:    postgresql.org/message-id/3a8e256d-707b-48f0-b71a-b721964bafa5@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, ronljohnsonjr@gmail.com, jbauer@agristats.com
  Subject: Re: Refresh Materialized View Issue
  In-Reply-To: <3a8e256d-707b-48f0-b71a-b721964bafa5@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>

* 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