In PostgreSQL, how can we tell if each index of a table is clustered or not?

Question

In PostgreSQL, how can we tell if each index of a table is clustered or not?

This is counterpart to In MySQL, how can we tell if an index of a table is clustered or not?

Thanks.

Answer 1

Postgres does not support clustered indexes in the sense like in MySql. There may be an index which was used to cluster the table. You can check this by querying the column indisclustered in the system catalog pg_index.

indisclustered - If true, the table was last clustered on this index

Example:

create table my_table(id serial primary key, str text unique);

select relname, indisclustered
from pg_index i
join pg_class c on c.oid = indexrelid
where indrelid = 'public.my_table'::regclass

     relname      | indisclustered 
------------------+----------------
 my_table_str_key | f
 my_table_pkey    | f
(2 rows)

cluster my_table using my_table_str_key;

select relname, indisclustered
from pg_index i
join pg_class c on c.oid = indexrelid
where indrelid = 'public.my_table'::regclass

     relname      | indisclustered 
------------------+----------------
 my_table_str_key | t
 my_table_pkey    | f
(2 rows)

Read in the documentation about CLUSTER:

When a table is clustered, it is physically reordered based on the index information. Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered. That is, no attempt is made to store new or updated rows according to their index order.