手动删除子表的索引

DROP INDEX cnp_wip.sfc_tempein_handle_key;

SELECT parent_index.indexrelid::regclass,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
        LEFT  JOIN pg_partition_rule ON pg_partition_rule.paroid = pg_partition.oid
        -- Find the indexes on each partition
        LEFT 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 = 'cnp_wip.sfc_tempein_handle_key'::regclass::oid
      -- Drop leaves first, even if it doesn't really matter in this case
      ORDER BY pg_partition.parlevel DESC

      SELECT indexrelid::regclass
      --parent_index.indexrelid::regclass::text
      --,substring(parent_index.indexrelid::regclass,1,7)
       FROM pg_index   

     select * from pg_indexes where schemaname = 'cnp_wip'
     --and tablename like '%sfc%' 
    -- and indexname like '%idx_cw_ss_%'
     order by tablename

select count(*) from cnp_wip.sfc_step

       select * from pg_partition_rule

ana

       ——————————————————————————————————————————————————

DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_16-20_per1m_50";
DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_16-20_per1m_51";
DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_16-20_per1m_52";
DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_16-20_per1m_53";
DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_16-20_per1m_54";
DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_16-20_per1m_55";
DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_16-20_per1m_56";
DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_16-20_per1m_57";
DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_16-20_per1m_58";
DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_16-20_per1m_59";
DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_16-20_per1m_6";
DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_16-20_per1m_60";
DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_16-20_per1m_7";
DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_16-20_per1m_8";
DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_16-20_per1m_9";
DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_bf2016";
DROP INDEX cnp_wip."idx_cw_ss_handle_1_prt_df";

 

————————————————————————————————————————————————————

 

-- Function: drop_child_indexes(character varying)

-- DROP FUNCTION drop_child_indexes(character varying);

CREATE OR REPLACE FUNCTION drop_child_indexes(index_name character varying)
  RETURNS void AS
$BODY$
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
$BODY$
  LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION drop_child_indexes(character varying)
  OWNER TO gpadmin;