Skip to content

How do I create indexes?

Dmitry Ivanov edited this page Aug 29, 2017 · 3 revisions

If you haven't created partitions yet, just create an index on the parent table before calling create_range_partitions() or create_hash_partitions(). The index will be automatically propagated for each partition as new partitions created.

If you want to create an index on existing partitions, use the following snippet (don't forget to change table and key attribute names to actual names):

do
$$
declare
  rec record;
begin
  for rec in select * from pathman_partition_list 
             where parent = 'table_name'::regclass
  loop
    execute format('create index on %s (attr_name)', rec.partition);
  end loop;
end
$$
language plpgsql;
Clone this wiki locally