Question 90699 in PostgreSQL Question posted in
The official documentation can be found here.

What ONLY keyword really means in Postgresql CREATE INDEX command

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)?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search