From the docs: "Indicates not to recurse creating indexes on partitions, if the table is partitioned. The default is to recurse.".

Am I understand correctly that index will not be created on existing partitons? What kind of index will be created then (on what)?

CodePudding user response：

The objective is to build a partitioned index with as little locking as possible.

Normally, you'd use CREATE INDEX CONCURRENTLY to create an index on each partition, then CREATE INDEX on the partitioned table. If the index definitions match, the previously created indexes will become partitions of the partitioned index. See this related question.

The potential problem with that is that all partitions will be locked at the same time. Instead, you can do it one partition at a time:

• create the index ONLY on the partitioned table (the index will be invalid)

• use ALTER INDEX ... ATTACH PARTITION to attach the indexes on the partitions as partitions of the index

• once all partitions are attached, the partitioned index will become valid

CodePudding user response：

When CREATE INDEX is invoked on a partitioned table, the default behavior is to recurse to all partitions to ensure they all have matching indexes. Each partition is first checked to determine whether an equivalent index already exists, and if so, that index will become attached as a partition index to the index being created, which will become its parent index. If no matching index exists, a new index will be created and automatically attached; the name of the new index in each partition will be determined as if no index name had been specified in the command. If the ONLY option is specified, no recursion is done, and the index is marked invalid. (ALTER INDEX ... ATTACH PARTITION marks the index valid, once all partitions acquire matching indexes.) Note, however, that any partition that is created in the future using CREATE TABLE ... PARTITION OF will automatically have a matching index, regardless of whether ONLY is specified.

small demo example:

create table index_part (a int, b int) partition by range (a, b);
create table index_part1 partition of index_part for values from (0,0) to (10, 10);
create table index_part2 partition of index_part for values from (10,10) to (20, 20);
create index index_part_a_b_idx on only index_part (a, b);

now is INVALID:

\d  index_part_a_b_idx
---
btree, for table "public.index_part", invalid
Partitions: index_part2_a_b_idx
Access method: btree

create index idxpart1_a_b_idx on index_part1 (a, b);
alter index index_part_a_b_idx attach partition idxpart1_a_b_idx;

still INVALID.

\d  index_part_a_b_idx
---
btree, for table "public.index_part", invalid
Partitions: idxpart1_a_b_idx
Access method: btree

then

create index idxpart2_a_b_idx on index_part2(a, b);
alter index index_part_a_b_idx attach partition idxpart2_a_b_idx;

now ISVALID.

select indisvalid from pg_index  where indexrelid = 'idxpart2_a_b_idx'::regclass; ---return true.