If I create an index on a partitioned table, each partition gets its own index. These are not dropped if I drop the main index.
Is there an easy way to do this?
I have written a function that looks for all the indexes that match the top level index name with a partition suffix, and that works fine for indexes that were created on partitions.
However, when Greenplum adds a new partition through a default partition split, it generates a new index for that partition with a totally different naming convention.
Existing partitions get a name like indexname_1_prt_partitionname
New partitions get a name like tablename_1_prt_partitionname_indexcolumn
Any idea how I can identify that the new partition index is part of the parent index, when the name doesn't match? Or do I just call my index deleter twice with two different patterns to match?
I will be using either Bell's query below, or this one which is adapted to take a partition index (in case we have already deleted the head index):
SELECT child_index.indexrelid::regclass
FROM pg_index AS partition_index
-- Find the partition that the partition index is on
INNER JOIN pg_partition_rule parindex_rule ON parindex_rule.parchildrelid = partition_index.indrelid
-- Follup up to the partitioning scheme
INNER JOIN pg_partition ON pg_partition.oid = parindex_rule.paroid
-- Follow the links through to the individual partitions
INNER JOIN pg_partition_rule ON pg_partition_rule.paroid = pg_partition.oid
-- Find the indexes on each partition
INNER JOIN pg_index AS child_index ON child_index.indrelid = pg_partition_rule.parchildrelid
-- Which are on the same field as the named index
AND child_index.indkey = partition_index.indkey
-- Using the same comparison operator
AND child_index.indclass = partition_index.indclass
-- Filtered for the index we're trying to drop
WHERE partition_index.indexrelid = 'schema.partitionindexname'::regclass
Best Answer
Greenplum doesn't (as at version 4.3.8) maintain a record in the catalogue linking indexes on partitions to indexes on base tables. The best option is to follow the partitions and find indexes on the partitions matching the definition of the base index.
If there another index with the same definition (fields, comparison operators) it's partition indexes will also be dropped. Not a perfect answer but better than substring matching.