Re: Refresh Materialized View Issue

On 1/11/24 3:40 PM, Ron Johnson wrote:
> On Thu, Jan 11, 2024 at 3:50 PM Jeremiah Bauer <jbauer(at)agristats(dot)com
> <mailto:jbauer(at)agristats(dot)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