From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | kurt(at)roeckx(dot)be |
Subject: | BUG #16443: Too much memory usage on insert query |
Date: | 2020-05-17 14:42:09 |
Message-ID: | 16443-030629aae2859708@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 16443
Logged by: Kurt Roeckx
Email address: kurt(at)roeckx(dot)be
PostgreSQL version: 12.3
Operating system: Debian
Description:
Hi,
I have this in my config file:
shared_buffers = 2048MB # min 128kB
work_mem = 1024MB # min 64kB
I was executing this query:
insert into ct_entry_chain (ct_entry_id, certificate_id) select id,
unnest(certificate_chain_id) from ct_entry;
The process was using at least 14 GB, of the 8 GB of RAM that's available.
Swap was also using around the 8GB maximum, while normally around 200 MB is
swapped out. I didn't expect this process to use more than around 3 GB.
This has resulted in an out of memory condition.
I was converting my schema, ct_entry_chain is a new table that looks like:
Table "public.ct_entry_chain"
Column | Type | Collation | Nullable | Default
----------------+--------+-----------+----------+--------------------------------------------
id | bigint | | not null |
nextval('ct_entry_chain_id_seq'::regclass)
ct_entry_id | bigint | | not null |
certificate_id | bigint | | not null |
Indexes:
"ct_entry_chain_pkey" PRIMARY KEY, btree (id)
"ct_entry_chain_ct_entry_id_certificate_id_key" UNIQUE CONSTRAINT, btree
(ct_entry_id, certificate_id)
Foreign-key constraints:
"ct_entry_chain_certificate_id_fkey" FOREIGN KEY (certificate_id)
REFERENCES raw_certificates(id)
"ct_entry_chain_ct_entry_id_fkey" FOREIGN KEY (ct_entry_id) REFERENCES
ct_entry(id)
ct_entry itself looks like:
Table "public.ct_entry"
Column | Type | Collation | Nullable |
Default
----------------------+--------------------------+-----------+----------+--------------------------------------
id | bigint | | not null |
nextval('ct_entry_id_seq'::regclass)
ct_log_id | integer | | not null |
raw_certificate_id | bigint | | not null |
log_entry | bigint | | not null |
timestamp | timestamp with time zone | | not null |
version | integer | | not null |
entry_type | integer | | not null |
leaf_type | integer | | not null |
extensions | bytea | | |
issuer_key_hash | bytea | | |
pre_certificate_id | bigint | | |
certificate_chain_id | bigint[] | | |
Indexes:
"ct_entry_pkey" PRIMARY KEY, btree (id)
"ct_entry_raw_certificate_id_idx" btree (raw_certificate_id)
Foreign-key constraints:
"ct_entry_ct_log_id_fkey" FOREIGN KEY (ct_log_id) REFERENCES
ct_logs(id)
"ct_entry_pre_certificate_id_fkey" FOREIGN KEY (pre_certificate_id)
REFERENCES raw_certificates(id)
"ct_entry_raw_certificate_id_fkey" FOREIGN KEY (raw_certificate_id)
REFERENCES raw_certificates(id)
Referenced by:
TABLE "ct_entry_chain" CONSTRAINT "ct_entry_chain_ct_entry_id_fkey"
FOREIGN KEY (ct_entry_id) REFERENCES ct_entry(id)
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2020-05-17 14:54:12 | BUG #16444: Vacuum writes the table twice |
Previous Message | David G. Johnston | 2020-05-16 02:17:33 | Re: BUG #16442: terraform provider postgresql 9.0.0/9.4/12.3/current/latest fail |