List Columns with Indexes in Postgresql

List columns with indexes in PostgreSQL

Create some test data...

create table test (a int, b int, c int, constraint pk_test primary key(a, b));
create table test2 (a int, b int, c int, constraint uk_test2 unique (b, c));
create table test3 (a int, b int, c int, constraint uk_test3b unique (b), constraint uk_test3c unique (c),constraint uk_test3ab unique (a, b));

List indexes and columns indexed:

select
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname like 'test%'
order by
    t.relname,
    i.relname;

 table_name | index_name | column_name
------------+------------+-------------
 test       | pk_test    | a
 test       | pk_test    | b
 test2      | uk_test2   | b
 test2      | uk_test2   | c
 test3      | uk_test3ab | a
 test3      | uk_test3ab | b
 test3      | uk_test3b  | b
 test3      | uk_test3c  | c

Roll up the column names:

select
    t.relname as table_name,
    i.relname as index_name,
    array_to_string(array_agg(a.attname), ', ') as column_names
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname like 'test%'
group by
    t.relname,
    i.relname
order by
    t.relname,
    i.relname;

 table_name | index_name | column_names
------------+------------+--------------
 test       | pk_test    | a, b
 test2      | uk_test2   | b, c
 test3      | uk_test3ab | a, b
 test3      | uk_test3b  | b
 test3      | uk_test3c  | c

How to list all indexes of a table with their corresponding size in PostgreSQL?

Use pg_indexes.

select indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) as size
from pg_indexes
where tablename = 'my_table';

List all index names, column names and its table name of a PostgreSQL database

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));

How to list indexes created for table in postgres

The view pg_indexes provides access to useful information about each index in the database, eg.

select *
from pg_indexes
where tablename not like 'pg%';

Is it possible to create a PostgreSQL index with json Include Columns?

You cannot have data->>'variantCode' as INCLUDE column, but also adding it as index key won't help you to get an index-only scan. PostgreSQL doesn't consider index keys that are not columns for an index-only scan. There is no fundamental problem that prevents that, it is just not implemented.

You'd have to add the whole data to the index, but that is not possible, since there is no B-tree operator class for jsonb.

So the only remaining option to get an index-only scan would be a generated column:

ALTER TABLE products ADD data_varcode text
   GENERATED ALWAYS AS (data->>'variantCode') STORED;

Then you can use that column in your query and index.

postgresql: how list indexed columns?

Those things are pretty easy to find out.

Simply run psql with the -E option and it will show you the SQL statements that are used. So when running \d index_name the following statement (among others) is used to retrieve the index columns:

SELECT a.attname,
       pg_catalog.format_type (a.atttypid,a.atttypmod),
       (SELECT SUBSTRING (pg_catalog.pg_get_expr (d.adbin,d.adrelid) FOR 128)
        FROM pg_catalog.pg_attrdef d
        WHERE d.adrelid = a.attrelid
        AND   d.adnum = a.attnum
        AND   a.atthasdef)a.attnotnull,
       a.attnum,
       pg_catalog.pg_get_indexdef (a.attrelid,a.attnum,TRUE) AS indexdef
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (SELECT oid FROM pg_class WHERE relname = 'index_name')
AND   a.attnum > 0
AND   NOT a.attisdropped
ORDER BY a.attnum;

---

Related Topics