Parent index for child index's

SQL Query to Find Parent Index

This query identifies the parent index from which a given child index was inherited in a PostgreSQL database with partitioned tables:

    
WITH RECURSIVE partition_hierarchy AS (
    -- Start with the child index you're interested in
    SELECT 
        child_idx.indexrelid AS child_index_id,
        child_idx.indrelid AS child_table_id,
        parent_idx.indexrelid AS parent_index_id,
        parent_idx.indrelid AS parent_table_id
    FROM 
        pg_index child_idx
    JOIN 
        pg_inherits ON pg_inherits.inhrelid = child_idx.indexrelid
    JOIN 
        pg_index parent_idx ON pg_inherits.inhparent = parent_idx.indexrelid
    WHERE 
        child_idx.indexrelid = (SELECT oid FROM pg_class WHERE relname = 'events_north_2024_q3_event_name_idx')

    UNION ALL

    -- Recursively find each parent up to the main parent
    SELECT 
        child_idx.indexrelid AS child_index_id,
        child_idx.indrelid AS child_table_id,
        parent_idx.indexrelid AS parent_index_id,
        parent_idx.indrelid AS parent_table_id
    FROM 
        pg_index child_idx
    JOIN 
        pg_inherits ON pg_inherits.inhrelid = child_idx.indexrelid
    JOIN 
        pg_index parent_idx ON pg_inherits.inhparent = parent_idx.indexrelid
    JOIN 
        partition_hierarchy ph ON ph.parent_index_id = child_idx.indexrelid
)
SELECT 
    child_table.relname AS child_table_name,
    child_index.relname AS child_index_name,
    parent_table.relname AS main_parent_table_name,
    parent_index.relname AS main_parent_index_name
FROM 
    partition_hierarchy
JOIN 
    pg_class child_table ON child_table.oid = partition_hierarchy.child_table_id
JOIN 
    pg_class child_index ON child_index.oid = partition_hierarchy.child_index_id
JOIN 
    pg_class parent_table ON parent_table.oid = partition_hierarchy.parent_table_id
JOIN 
    pg_class parent_index ON parent_index.oid = partition_hierarchy.parent_index_id
ORDER BY 
    main_parent_table_name, main_parent_index_name
LIMIT 1