List all index names, column names and its table name of a PostgreSQL database
PostgresqlPostgresql Problem Overview
What is the query to get the list all index names, its column name and its table name of a postgresql database?
I have tried to get the list of all indexes in a db by using this query but how to get the list of indexes, its column names and its table names?
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_');
Postgresql Solutions
Solution 1 - Postgresql
This will output all indexes with details (extracted from my view definitions):
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;
Optionally add an extra join to the end so as to trim the namespaces:
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));
Solution 2 - Postgresql
More human friendly version of @Denis solution:
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
Solution 3 - Postgresql
The Query to list all the indexes of a database
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;
Solution 4 - Postgresql
Here's a version that simplifies things compared to other answers by
- avoiding nested selects
- avoiding built-in functions (maybe hard to remember)
- using
LATERALandUNNEST(...) WITH ORDINALITYfeatures available in later PostgreSQL versions (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
Solution 5 - Postgresql
If you are also interested in index size, you may use this query from the PostgreSQL Wiki.
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;
Solution 6 - Postgresql
For Non-Composite Indexes
select t.relname,i.relname ,
STRING_AGG(pga.attname||'', ','order by i.relname,pga.attnum) as columnName
from pg_class t inner join pg_index ix
on t.oid = ix.indrelid
inner join pg_class i
on i.oid = ix.indexrelid
inner join pg_attribute pga
on
pga.attrelid = i.oid
inner join pg_indexes pgidx
on pgidx.indexname=i.relname
where
t.relkind = 'r'
and pgidx.schemaname='asit_cm'
and t.relname ='accessory'
group by t.relname,i.relname having count(*)=1
For Composite Indexes
select t.relname,i.relname ,
STRING_AGG(pga.attname||'', ','order by i.relname,pga.attnum) as columnName
from pg_class t inner join pg_index ix
on t.oid = ix.indrelid
inner join pg_class i
on i.oid = ix.indexrelid
inner join pg_attribute pga
on
pga.attrelid = i.oid
inner join pg_indexes pgidx
on pgidx.indexname=i.relname
where
t.relkind = 'r'
and pgidx.schemaname='asit_cm'
and t.relname ='accessory'
group by t.relname,i.relname having count(*)=1