Re: [EXTERNAL]Re: Refresh Materialized View Issue

2024-01-12 Thread Jim Nasby
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

Re: [EXTERNAL]Re: Refresh Materialized View Issue

2024-01-12 Thread Jeremiah Bauer
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

Re: Refresh Materialized View Issue

2024-01-11 Thread Jim Nasby
On 1/11/24 3:40 PM, Ron Johnson wrote: On Thu, Jan 11, 2024 at 3:50 PM Jeremiah Bauer > 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

Re: Refresh Materialized View Issue

2024-01-11 Thread Ron Johnson
On Thu, Jan 11, 2024 at 3:50 PM Jeremiah Bauer 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

Re: Refresh Materialized View Issue

2024-01-11 Thread Jeremiah Bauer
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

Re: Refresh Materialized View Issue

2024-01-11 Thread Ron Johnson
On Thu, Jan 11, 2024 at 3:31 PM Jeremiah Bauer wrote: > Hello all, > > We are having an issue with a materialized view refresh never finishing, > any help is appreciated. It will run at 100% CPU and no IO traffic > indefinitely after about 15 minutes of parallel workers and the parent > worker

Refresh Materialized View Issue

2024-01-11 Thread Jeremiah Bauer
Hello all, We are having an issue with a materialized view refresh never finishing, any help is appreciated. It will run at 100% CPU and no IO traffic indefinitely after about 15 minutes of parallel workers and the parent worker consuming CPU and IO. PostgreSQL Version: 15.5 Due to some