How to create index on table which is partitioned?

Question

How to create an index on a partitioned table in PostgreSQL 11.2?

My table is:

CREATE TABLE sometablename
(
    column1 character varying(255) COLLATE pg_catalog."default" NOT NULL,
    column2 integer NOT NULL,
    column3 character varying(255) COLLATE pg_catalog."default" NOT NULL,
    "timestamp" timestamp without time zone NOT NULL,
    avg_val double precision,
    max_val double precision,
    min_val double precision,
    p95_val double precision,
    sample_count double precision,
    sum_val double precision,
    unit character varying(255) COLLATE pg_catalog."default",
    user_id bigint NOT NULL,
    CONSTRAINT testtable_pkey PRIMARY KEY (column1, column2, column3, "timestamp", user_id)
)
PARTITION BY HASH (user_id) 
    WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;


CREATE UNIQUE INDEX testtable_unique_pkey
ON sometablename USING btree (column1 COLLATE pg_catalog."default", column2
COLLATE pg_catalog."default", "timestamp", user_id)
TABLESPACE pg_default;

As you can see testtable_unique_pkey is my index.

but when I run:

SELECT tablename, indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'sometablename'

I can't see my index.

I checked the explain analysis on my queries which is also not using the index.

Answer 1

The index for the base table is never really created, so it doesn't show up in pg_indexes:

CREATE TABLE base_table
(
    column1 varchar(255) NOT NULL,
    column2 integer NOT NULL,
    user_id bigint NOT NULL
)
PARTITION BY HASH (user_id);
CREATE UNIQUE INDEX idx_one ON base_table (column1, column2, user_id);

So the following returns nothing:

select *
from pg_indexes
where tablename = 'base_table';

It is however stored in pg_class:

select i.relname as indexname, t.relname as tablename
from pg_class i
  join pg_index idx on idx.indexrelid = i.oid
  join pg_class t on t.oid = idx.indrelid
where i.relkind = 'I'
  and t.relname = 'base_table';

returns:

indexname | tablename 
----------+-----------
idx_one   | base_table

But for each partition the index will show up in pg_indexes:

create table st_p1 partition of base_table for values with (modulus 4, remainder 0);
create table st_p2 partition of base_table for values with (modulus 4, remainder 1);
create table st_p3 partition of base_table for values with (modulus 4, remainder 2);
create table st_p4 partition of base_table for values with (modulus 4, remainder 3);

And then:

select tablename, indexname
from pg_indexes
where tablename  in ('st_p1', 'st_p2', 'st_p3', 'st_p4');

returns:

tablename | indexname                        
----------+----------------------------------
st_p1     | st_p1_column1_column2_user_id_idx
st_p2     | st_p2_column1_column2_user_id_idx
st_p3     | st_p3_column1_column2_user_id_idx
st_p4     | st_p4_column1_column2_user_id_idx

---

Update 2020-06-26:

The fact that the index did not show up in pg_indexes was acknowledged as a bug by the Postgres team and was fixed in Postgres 12.

So the above explanation is only valid for Postgres 10 and 11. Starting with Postgres 12, the index on base_table will be shown in `pg_indexes.