Обсуждение: BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition
BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 18402 Logged by: Gilles PARC Email address: gparc@online.fr PostgreSQL version: 16.2 Operating system: Linux Description: Example : First I create a list partitioned table and fill it CREATE TABLE master ( id integer NOT NULL, millesime varchar(4) NOT NULL, CONSTRAINT master_pk PRIMARY KEY (id, millesime) ) partition by list (millesime); CREATE TABLE master_2022 PARTITION OF master FOR VALUES IN ('2022'); CREATE TABLE master_2023 PARTITION OF master FOR VALUES IN ('2023'); CREATE TABLE master_2024 PARTITION OF master FOR VALUES IN ('2024'); CREATE TABLE master_2025 PARTITION OF master FOR VALUES IN ('2025'); DO $do$ BEGIN FOR i IN 2022..2025 LOOP INSERT INTO master VALUES (generate_series(1,100000), i::varchar); END LOOP; END $do$; I now list the created indexes: \diS+ master* List of relations Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description --------+------------------+-------------------+----------+-------------+-------------+---------------+---------+------------- public | master_2022_pkey | index | postgres | master_2022 | permanent | btree | 3088 kB | public | master_2023_pkey | index | postgres | master_2023 | permanent | btree | 3088 kB | public | master_2024_pkey | index | postgres | master_2024 | permanent | btree | 3088 kB | public | master_2025_pkey | index | postgres | master_2025 | permanent | btree | 3088 kB | public | master_pk | partitioned index | postgres | master | permanent | btree | 0 bytes | (5 rows) Now I prepare a new 2026 partition with correct constraints and indexes and attach it : CREATE TABLE master_2026 (LIKE master INCLUDING DEFAULTS INCLUDING CONSTRAINTS); ALTER TABLE master_2026 ADD CONSTRAINT master_2026_ck CHECK (millesime = '2026'); DO $do$ BEGIN INSERT INTO master_2026 VALUES (generate_series(1,100000), '2026'); END $do$; CREATE UNIQUE INDEX CONCURRENTLY master_2026_pkey ON master_2026 (id, millesime); ALTER TABLE master ATTACH PARTITION master_2026 FOR VALUES IN ('2026'); ALTER TABLE master_2026 DROP CONSTRAINT master_2026_ck; But now, when I list the indexes again, I see that my unique index above is not considered to use and a new one is created (master_2026_pkey1). \diS+ master* Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description --------+-------------------+-------------------+----------+-------------+-------------+---------------+---------+------------- public | master_2022_pkey | index | postgres | master_2022 | permanent | btree | 3088 kB | public | master_2023_pkey | index | postgres | master_2023 | permanent | btree | 3088 kB | public | master_2024_pkey | index | postgres | master_2024 | permanent | btree | 3088 kB | public | master_2025_pkey | index | postgres | master_2025 | permanent | btree | 3088 kB | public | master_2026_pkey | index | postgres | master_2026 | permanent | btree | 3104 kB | public | master_2026_pkey1 | index | postgres | master_2026 | permanent | btree | 3104 kB | public | master_pk | partitioned index | postgres | master | permanent | btree | 0 bytes | (7 rows) why is it so ? Bug or Feature ? Regards Gilles
Re: BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition
От
gparc@free.fr
Дата:
Hello, is there a week retention before this report is delivered to the mailing list ? Regards Gilles ----- Mail original ----- > De: "PG Bug reporting form" <noreply@postgresql.org> > À: "pgsql-bugs" <pgsql-bugs@lists.postgresql.org> > Cc: "gparc" <gparc@online.fr> > Envoyé: Mercredi 20 Mars 2024 15:29:14 > Objet: BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition > The following bug has been logged on the website: > > Bug reference: 18402 > Logged by: Gilles PARC > Email address: gparc@online.fr > PostgreSQL version: 16.2 > Operating system: Linux > Description: > > Example : > > First I create a list partitioned table and fill it > CREATE TABLE master > ( > id integer NOT NULL, > millesime varchar(4) NOT NULL, > CONSTRAINT master_pk PRIMARY KEY (id, millesime) > ) partition by list (millesime); > > CREATE TABLE master_2022 PARTITION OF master FOR VALUES IN ('2022'); > CREATE TABLE master_2023 PARTITION OF master FOR VALUES IN ('2023'); > CREATE TABLE master_2024 PARTITION OF master FOR VALUES IN ('2024'); > CREATE TABLE master_2025 PARTITION OF master FOR VALUES IN ('2025'); > > DO > $do$ > BEGIN > FOR i IN 2022..2025 LOOP > INSERT INTO master VALUES (generate_series(1,100000), i::varchar); > END LOOP; > END > $do$; > > I now list the created indexes: > \diS+ master* > List of relations > Schema | Name | Type | Owner | Table | > Persistence | Access method | Size | Description > --------+------------------+-------------------+----------+-------------+-------------+---------------+---------+------------- > public | master_2022_pkey | index | postgres | master_2022 | > permanent | btree | 3088 kB | > public | master_2023_pkey | index | postgres | master_2023 | > permanent | btree | 3088 kB | > public | master_2024_pkey | index | postgres | master_2024 | > permanent | btree | 3088 kB | > public | master_2025_pkey | index | postgres | master_2025 | > permanent | btree | 3088 kB | > public | master_pk | partitioned index | postgres | master | > permanent | btree | 0 bytes | > (5 rows) > > > Now I prepare a new 2026 partition with correct constraints and indexes and > attach it : > > CREATE TABLE master_2026 (LIKE master INCLUDING DEFAULTS INCLUDING > CONSTRAINTS); > ALTER TABLE master_2026 ADD CONSTRAINT master_2026_ck CHECK (millesime = > '2026'); > DO > $do$ > BEGIN > INSERT INTO master_2026 VALUES (generate_series(1,100000), '2026'); > END > $do$; > CREATE UNIQUE INDEX CONCURRENTLY master_2026_pkey ON master_2026 (id, > millesime); > ALTER TABLE master ATTACH PARTITION master_2026 FOR VALUES IN ('2026'); > ALTER TABLE master_2026 DROP CONSTRAINT master_2026_ck; > > But now, when I list the indexes again, I see that my unique index above is > not considered to use > and a new one is created (master_2026_pkey1). > \diS+ master* > Schema | Name | Type | Owner | Table | > Persistence | Access method | Size | Description > --------+-------------------+-------------------+----------+-------------+-------------+---------------+---------+------------- > public | master_2022_pkey | index | postgres | master_2022 | > permanent | btree | 3088 kB | > public | master_2023_pkey | index | postgres | master_2023 | > permanent | btree | 3088 kB | > public | master_2024_pkey | index | postgres | master_2024 | > permanent | btree | 3088 kB | > public | master_2025_pkey | index | postgres | master_2025 | > permanent | btree | 3088 kB | > public | master_2026_pkey | index | postgres | master_2026 | > permanent | btree | 3104 kB | > public | master_2026_pkey1 | index | postgres | master_2026 | > permanent | btree | 3104 kB | > public | master_pk | partitioned index | postgres | master | > permanent | btree | 0 bytes | > (7 rows) > > why is it so ? > > Bug or Feature ? > Regards > Gilles
Re: BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition
От
"Euler Taveira"
Дата:
On Wed, Mar 20, 2024, at 11:29 AM, PG Bug reporting form wrote:
CREATE TABLE master_2026 (LIKE master INCLUDING DEFAULTS INCLUDINGCONSTRAINTS);ALTER TABLE master_2026 ADD CONSTRAINT master_2026_ck CHECK (millesime ='2026');DO$do$BEGININSERT INTO master_2026 VALUES (generate_series(1,100000), '2026');END$do$;CREATE UNIQUE INDEX CONCURRENTLY master_2026_pkey ON master_2026 (id,millesime);ALTER TABLE master ATTACH PARTITION master_2026 FOR VALUES IN ('2026');
You don't add a primary key or unique constraint here. Hence, the ALTER TABLE ..
ATTACH PARTITION doesn't consider the master_2026_pkey as a candidate for
primary key index. Add the following command and your index will be used.
ALTER TABLE master_2026
ADD CONSTRAINT master_2026_pk PRIMARY KEY USING INDEX master_2026_pkey;
See [1]. Documentation [2] says
UNIQUE and PRIMARY KEY constraints from the parent table will be created in the
partition, if they don't already exist.