Re: [EXTERNAL]Re: Refresh Materialized View Issue

[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 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

Re: [EXTERNAL]Re: Refresh Materialized View Issue

[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 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


--


Jeremiah

CONFIDENTIALITY NOTICE: The information contained in this email (and any 
attachments) is privileged and confidential and protected from disclosure. If 
you are not the intended recipient of this email or the attachments, be aware 
that any disclosure, copying, distribution or use of this email or any 
attachment is strictly prohibited and you should not read the message or read 
or open any attachment. If you have received this email by mistake, please 
immediately notify the sender and delete it permanently from your system. Agri 
Stats, Inc. and its subsidiaries will not be held liable to any person or 
entity resulting from the unintended or unauthorized use of any information 
contained in this email.

Re: Refresh Materialized View Issue

[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 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

Re: Refresh Materialized View Issue

[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 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.

Adding id1 to the end of idx_large_table_id2_id3 *might* be a better
option, since it'll reuse much of the existing disk space.


> A materialized view isn't too different from an unlogged table.
>
> So an unlogged table would also be an appropriate solution?
>

Sure, since materialized views are effectively tables with a SELECT
statement bound to it, and are logged.  Thus, unlogged tables are faster to
create.  Of course, being unlogged is a two-edged sword: any unclean
shutdown makes the data go away; you'd have to rebuild the table.

Re: Refresh Materialized View Issue

[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 IF NOT EXISTS idx_large_table_id1
ON public.large_table USING btree
(id1 ASC NULLS LAST)
TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_large_table_id2
ON public.large_table USING btree
(id2 ASC NULLS LAST)
TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_large_table_id3
ON public.large_table USING btree
(id3 ASC NULLS LAST)
TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_large_table_id2_id3
ON public.large_table USING btree
(id2 ASC NULLS LAST, id3 ASC NULLS LAST)
TABLESPACE pg_default;

A materialized view isn't too different from an unlogged table.
So an unlogged table would also be an appropriate solution?

--


Jeremiah

CONFIDENTIALITY NOTICE: The information contained in this email (and any 
attachments) is privileged and confidential and protected from disclosure. If 
you are not the intended recipient of this email or the attachments, be aware 
that any disclosure, copying, distribution or use of this email or any 
attachment is strictly prohibited and you should not read the message or read 
or open any attachment. If you have received this email by mistake, please 
immediately notify the sender and delete it permanently from your system. Agri 
Stats, Inc. and its subsidiaries will not be held liable to any person or 
entity resulting from the unintended or unauthorized use of any information 
contained in this email.

Re: Refresh Materialized View Issue

[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 consuming CPU and IO.
>
> PostgreSQL Version: 15.5
>
> Due to some design decisions, we have a large table that we need a
> distinct list of associated id's out of for an application interface.  This
> table has approximately 1,650,000,000 rows with a table size of 175GB and
> 250GB of indexes.
>
> I anonymized the column names in the table structure, but this is the
> table structure of our large table.
>
> create table public.large_table(
>large_table_id bigint NOT NULL DEFAULT
> nextval(public.large_table_id_seq'::regclass),
> nmrc numeric(30,5),
> id1 bigint NOT NULL,
> id2 bigint NOT NULL,
> id3 bigint NOT NULL,
> id4 bigint NOT NULL,
> id5 bigint NOT NULL,
> last_updt_ts timestamp without time zone,
> last_db_updt_ts timestamp without time zone,
> charval character varying(30)
>
> )
>
> The materialized view is defined as:
>
> create materialized view public.vw_distinct_list_of_ids as (
>
> select distinct id1, id2, id3 from public.large_table
>
> ) with no data;
>
> When refreshed the materialized view will contain approximately 59,000,000
> rows.
>
> It takes approximately 12 minutes to run the select statement from the
> view definition.  When you refresh the materialized view for the first
> time, it will spawn several parallel workers that run at 100% CPU and
> produce some I/O for about 12 or 13 minutes, then they finish their work
> and terminate.  The parent worker will continue to run at 100% CPU until I
> terminate the process.  I've let it run for over 24 hours.
>
> What I find interesting is that if I do this:
>
> create table public.table_of_distinct_list_of_ids as (
>
> select distinct id1, id2, id3 from public.large_table
>
> );
>
> It will complete in 12 or 13 minutes, the same as the select statement.  I
> have three questions.
>

My question is: what indexes are on public.large_table?  Hopefully there's
a compound b-tree index on id1, id2, id3.

>
>1. Why is the materialized view refresh stalling and never completing?
>2. Is there a setting I am missing that affects materialized view
>refreshes?
>3. Should I just go with a table that is dropped and recreated
>instead?  I was hoping to avoid this.
>
> A materialized view isn't too different from an unlogged table.