Adding table partition slow when there is default partition with data (primary key not used to check partition condition)

From: Sasa Vilic <sasavilic(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Adding table partition slow when there is default partition with data (primary key not used to check partition condition)
Date: 2021-06-10 21:37:13
Message-ID: CAOJhpYcx129-b18B3K3cgUa-nbWonmen-7R1QwgG8O+sh3nSuA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I am doing following:

CREATE TABLE "change" (
transaction_id uuid NOT NULL,
id int4 NOT NULL,
change_type varchar NOT NULL,
object_type varchar NOT NULL,
object_content jsonb NOT NULL,
category_id uuid NOT NULL,
CONSTRAINT change_pkey PRIMARY KEY (transaction_id, id)
) partition by list(transaction_id);

create table change_default
partition of "change" default;

insert into "change"
select * from old_change; -- 17437300 rows

create table change_bf6840c7_3e7b_4100_b0e4_f5844fb7635d
partition of "change"
for values in ('bf6840c7-3e7b-4100-b0e4-f5844fb7635d');

Adding this last partition takes around 19 seconds. I understand that
postgres has to check that 'bf6840c7-3e7b-4100-b0e4-f5844fb7635d' is not
present in the default partition, but it can use the primary key for that
and it shouldn't take that long, right? This new table is currently not
being used, so it can't be table lock, so the only reasonable conclusion is
that it does full table scan. Can that be optimized to just do primary key
lookup?

Server:
PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-36), 64-bit
8 cores, 32 GiB RAM, 1TB SSD

Thanks in advance!
Sasa

Browse pgsql-general by date

  From Date Subject
Next Message Steve Litt 2021-06-10 23:31:14 Re: bottom / top posting
Previous Message Rob Sargent 2021-06-10 21:30:10 Re: Even more OT: Ditto machines [was: bottom / top posting]