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.
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.
--
Sincerely,
Jeremiah Bauer
Agri Stats, Inc.
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.
