如何刪除所有分區上的索引？

如果我在分區表上創建索引，每個分區都有自己的索引。如果我刪除主索引，這些不會被刪除。

是否有捷徑可尋？

我編寫了一個函式，用於查找與頂級索引名稱和分區後綴匹配的所有索引，並且該函式適用於在分區上創建的索引。

但是，當 Greenplum 通過預設分區拆分添加新分區時，它會為該分區生成一個具有完全不同命名約定的新索引。

現有分區的名稱類似於 indexname_1_prt_partitionname

新分區的名稱類似於 tablename_1_prt_partitionname_indexcolumn

當名稱不匹配時，知道如何辨識新分區索引是父索引的一部分嗎？或者我只是用兩種不同的模式呼叫我的索引刪除器兩次來匹配？

我將使用下面的 Bell 查詢，或者適用於獲取分區索引的這個查詢（如果我們已經刪除了頭索引）：

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

Greenplum 沒有（在版本 4.3.8 中）在將分區索引連結到基表索引的目錄中維護記錄。最好的選擇是跟踪分區並在與基本索引的定義匹配的分區上查找索引。

CREATE OR REPLACE FUNCTION drop_child_indexes (index_name varchar)
RETURNS VOID
AS
$functionBody$
DECLARE
 child_index_name varchar;
BEGIN

 FOR child_index_name IN
   SELECT child_index.indexrelid::regclass
     FROM pg_index AS parent_index
       -- Find the partitioning scheme for the table the index is on
       INNER JOIN pg_partition ON pg_partition.parrelid = parent_index.indrelid
       -- 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 = parent_index.indkey
         -- Using the same comparison operator
         AND child_index.indclass = parent_index.indclass
     -- Filtered for the index we're trying to drop
     WHERE parent_index.indexrelid = $1::regclass::oid
     -- Drop leaves first, even if it doesn't really matter in this case
     ORDER BY pg_partition.parlevel DESC

LOOP
 RAISE NOTICE '%', child_index_name||' ';
 EXECUTE 'DROP INDEX '||child_index_name||';';
END LOOP;

END
$functionBody$
LANGUAGE plpgsql;

如果有另一個具有相同定義（欄位、比較運算符）的索引，它的分區索引也將被刪除。不是一個完美的答案，但比子字元串匹配更好。

引用自：https://dba.stackexchange.com/questions/136745