Liste de tous les noms d'index, de colonnes et de tables d'une base de données PostgreSQL.

Question

Quelle est la requête pour obtenir la liste de tous les noms d'index, le nom de sa colonne et le nom de sa table d'une base de données postgresql ?

J'ai essayé d'obtenir la liste de tous les index dans une base de données en utilisant cette requête mais comment obtenir la liste des index, les noms des colonnes et des tables ?

 SELECT *
 FROM pg_class, pg_index
 WHERE pg_class.oid = pg_index.indexrelid
 AND pg_class.oid IN (
     SELECT indexrelid
     FROM pg_index, pg_class
     WHERE pg_class.oid=pg_index.indrelid
     AND indisunique != 't'
     AND indisprimary != 't'
     AND relname !~ '^pg_');

Answer 1

Cela donnera tous les index avec les détails (extraits de mes définitions de vues) :

SELECT i.relname as indname,
       i.relowner as indowner,
       idx.indrelid::regclass,
       am.amname as indam,
       idx.indkey,
       ARRAY(
       SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
       FROM generate_subscripts(idx.indkey, 1) as k
       ORDER BY k
       ) as indkey_names,
       idx.indexprs IS NOT NULL as indexprs,
       idx.indpred IS NOT NULL as indpred
FROM   pg_index as idx
JOIN   pg_class as i
ON     i.oid = idx.indexrelid
JOIN   pg_am as am
ON     i.relam = am.oid;

Il est possible d'ajouter une jointure supplémentaire à la fin afin de couper les espaces de noms :

SELECT i.relname as indname,
       i.relowner as indowner,
       idx.indrelid::regclass,
       am.amname as indam,
       idx.indkey,
       ARRAY(
       SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
       FROM generate_subscripts(idx.indkey, 1) as k
       ORDER BY k
       ) as indkey_names,
       idx.indexprs IS NOT NULL as indexprs,
       idx.indpred IS NOT NULL as indpred
FROM   pg_index as idx
JOIN   pg_class as i
ON     i.oid = idx.indexrelid
JOIN   pg_am as am
ON     i.relam = am.oid
JOIN   pg_namespace as ns
ON     ns.oid = i.relnamespace
AND    ns.nspname = ANY(current_schemas(false));

Answer 2

Version plus humaine de la solution de @Denis :

SELECT
  U.usename                AS user_name,
  ns.nspname               AS schema_name,
  idx.indrelid :: REGCLASS AS table_name,
  i.relname                AS index_name,
  idx.indisunique          AS is_unique,
  idx.indisprimary         AS is_primary,
  am.amname                AS index_type,
  idx.indkey,
       ARRAY(
           SELECT pg_get_indexdef(idx.indexrelid, k + 1, TRUE)
           FROM
             generate_subscripts(idx.indkey, 1) AS k
           ORDER BY k
       ) AS index_keys,
  (idx.indexprs IS NOT NULL) OR (idx.indkey::int[] @> array[0]) AS is_functional,
  idx.indpred IS NOT NULL AS is_partial
FROM pg_index AS idx
  JOIN pg_class AS i
    ON i.oid = idx.indexrelid
  JOIN pg_am AS am
    ON i.relam = am.oid
  JOIN pg_namespace AS NS ON i.relnamespace = NS.OID
  JOIN pg_user AS U ON i.relowner = U.usesysid
WHERE NOT nspname LIKE 'pg%'; -- Excluding system tables

Answer 3

La requête pour lister tous les index d'une base de données

SELECT
  tablename,
  indexes [1],
  indexes [2],
  indexes [3],
  indexes [4],
  indexes [5],
  indexes [6],
  indexes [7],
  indexes [8],
  indexes [9],
  indexes [10]
FROM (SELECT
  tablename,
  array_agg(indexname) AS indexes
FROM pg_indexes
WHERE schemaname = 'public'
GROUP BY tablename) as sub;

Answer 4

Voici une version qui simplifie les choses par rapport aux autres réponses en

• éviter les sélections imbriquées
• éviter les fonctions intégrées (peut-être difficiles à retenir)

• en utilisant LATERAL y UNNEST(...) WITH ORDINALITY fonctionnalités disponibles dans les versions ultérieures de PostgreSQL (9.4+)

  SELECT tnsp.nspname AS schema_name, trel.relname AS table_name, irel.relname AS index_name, array_agg ( a.attname || ' ' || CASE o.option & 1 WHEN 1 THEN 'DESC' ELSE 'ASC' END || ' ' || CASE o.option & 2 WHEN 2 THEN 'NULLS FIRST' ELSE 'NULLS LAST' END ORDER BY c.ordinality ) AS columns FROM pg_index AS i JOIN pg_class AS trel ON trel.oid = i.indrelid JOIN pg_namespace AS tnsp ON trel.relnamespace = tnsp.oid JOIN pg_class AS irel ON irel.oid = i.indexrelid CROSS JOIN LATERAL unnest (i.indkey) WITH ORDINALITY AS c (colnum, ordinality) LEFT JOIN LATERAL unnest (i.indoption) WITH ORDINALITY AS o (option, ordinality) ON c.ordinality = o.ordinality JOIN pg_attribute AS a ON trel.oid = a.attrelid AND a.attnum = c.colnum GROUP BY tnsp.nspname, trel.relname, irel.relname

Answer 5

Si vous êtes également intéressé par taille de l'indice vous pouvez utiliser cette requête à partir de la page Wiki PostgreSQL .

SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;