2

I have come up with the following query to get the table name, index_type, operator_classes, and uniqueness of indexes based on the answers mostly from @Erwin Brandstetter:

SELECT i.indrelid::regclass::text AS table
     , c.relname AS index_name
     , a.amname AS index_type
     , opc.operator_classes
     , i.indisunique AS is_unique
     , array_agg(att.attname) as column_names
FROM   pg_catalog.pg_namespace n
JOIN   pg_catalog.pg_class     c ON c.relnamespace = n.oid
JOIN   pg_catalog.pg_attribute att ON att.attrelid = c.oid
JOIN   pg_catalog.pg_index     i ON i.indexrelid = c.oid
JOIN   pg_catalog.pg_am        a ON a.oid = c.relam
CROSS  JOIN LATERAL (
   SELECT ARRAY (SELECT opc.opcname
                 FROM   unnest(i.indclass::oid[]) WITH ORDINALITY o(oid, ord)
                 JOIN   pg_opclass opc ON opc.oid = o.oid
                 ORDER  BY o.ord)
   ) opc(operator_classes)
WHERE  n.nspname !~ '^pg_'
AND    c.relkind = ANY (ARRAY['r', 't', 'i'])
group by
   i.indrelid, c.relname, a.amname, opc.operator_classes, i.indisunique
ORDER  BY 1, 2, 3, 4;

This works great, except it returns the wrong column names when the index is created by transforming data from another column. For example, with the following index:

CREATE INDEX _ixtr_luxury_inventory_images ON public.luxury_inventory
 USING gin (f_textarr2text(images) gin_trgm_ops)

returns:

table            | luxury_inventory
index_name       | _ixtr_luxury_inventory_images
index_type       | gin
operator_classes | ['gin_trgm_ops']
is_unique        | False
column_names     | ['f_textarr2text']

We get f_textarr2text as the column name instead of images.

How can we get the correct column_names? I'm on Postgres 16.2 if that matters.

Update: Here is the table creation SQL:

create table "luxury_inventory" (
id bigserial primary key,
images text[])

CREATE OR REPLACE FUNCTION "public".f_textarr2text(text[]) 
RETURNS text LANGUAGE sql IMMUTABLE AS $$SELECT array_to_string($1, ',')$$;
3
  • can you please post the create table of luxury_inventory
    – nbk
    Mar 21 at 23:23
  • @nbk Sure. Added the details!
    – Erasmose
    Mar 21 at 23:44
  • You could look at pg_depend to see on which columns the index depends. Mar 22 at 6:59

2 Answers 2

0

Thinking on this for a moment, perhaps better than pg_get_indexdef is pulling column ordinals out of the indexprs column noted in the first answer, and specifically the varattno field.

regexp_matches over the 'indexprs' (converting pg_tree_node to text) for varattno)

I threw together the following, going back to the definition in the columns table of information_schema:

SELECT (SELECT JSON_AGG(columns.column_name)
          FROM information_schema.columns
         WHERE table_schema = 'public'
           AND table_name   = i.indrelid::regclass::text
           AND ordinal_position IN (SELECT matches[1]::INTEGER 
                                    FROM regexp_matches(i.indexprs::TEXT,
                                                        'varattno (\d)',
                                                        'g') as matches)) AS argument_columns
     , pg_get_indexdef(att.attrelid, att.attnum, true) 
     , i.indrelid::regclass::text AS table
     , c.relname AS index_nameq
     , att.attname as column_names
FROM   pg_catalog.pg_namespace n
JOIN   pg_catalog.pg_class     c ON c.relnamespace = n.oid
JOIN   pg_catalog.pg_attribute att ON att.attrelid = c.oid
JOIN   pg_catalog.pg_index     i ON i.indexrelid = c.oid
WHERE  n.nspname !~ '^pg_'
AND    c.relkind IN ('r', 't', 'i')

and that produces:

argument_columns pg_get_indexdef table index_nameq column_names
NULL content_tsv file_lookup_16k fl_16k_tsv_idx content_tsv
NULL content_tsv file_lookup_4k fl_4k_tsv_idx content_tsv
NULL file_id file_lookup_4k date_index file_id
["file_id"] abs(file_id) file_lookup_4k date2_index abs
["sequence_no"] round(sequence_no::double precision) file_lookup_4k date3_index round
["sequence_no"] round(sequence_no::double precision) file_lookup_4k date4_index round
["content"] "substring"(content, 1, 5) file_lookup_4k date5_index substring
["file_id", "sequence_no"] abs(file_id - sequence_no) file_lookup_4k date6_index abs
NULL content_tsv file_lookup_8k fl_8k_tsv_idx content_tsv

I feel that this is far more in line with what we would both want.

Note that:

  • I joined only to the attname table, because that is really all I need
  • JSON_AGG(columns.column_name) can be replaced with JSON_AGG(columns.*) or the like to select the entire row for the columns.
  • ANY (ARRAY['r', 't', 'i'] was updated to IN ANY ('r', 't', 'i')

EDIT:

In order to bring this into a single column, Let's COALESCE, and whenever the new function returns NULL, we will insert an array containing the result of the pg_get_indexdef, and prefer ARRAY_AGG to JSON_AGG. All together:

SELECT COALESCE((SELECT ARRAY_AGG(columns.column_name)
                 FROM information_schema.columns
                WHERE table_schema = 'public'
                  AND table_name   = i.indrelid::regclass::text
                  AND ordinal_position IN (SELECT matches[1]::INTEGER 
                                           FROM regexp_matches(i.indexprs::TEXT,
                                                 'varattno (\d)',
                                                 'g') as matches)), 
                ARRAY[(pg_get_indexdef(att.attrelid, att.attnum, true))]) AS argument_columns
     , i.indrelid::regclass::text AS table
     , c.relname AS index_nameq
     , att.attname as column_names
FROM   pg_catalog.pg_namespace n
JOIN   pg_catalog.pg_class     c ON c.relnamespace = n.oid
JOIN   pg_catalog.pg_attribute att ON att.attrelid = c.oid
JOIN   pg_catalog.pg_index     i ON i.indexrelid = c.oid
WHERE  n.nspname !~ '^pg_'
AND    c.relkind IN ('r', 't', 'i');

This produces:

argument_columns table index_nameq column_names
{file_id} file_lookup_4k date_index file_id
{file_id} file_lookup_4k date2_index abs
{sequence_no} file_lookup_4k date3_index round
{sequence_no} file_lookup_4k date4_index round
{content} file_lookup_4k date5_index substring
{file_id,sequence_no} file_lookup_4k date6_index abs
2
  • Thanks for the new query. So the column name in showing up in the argument_columns array, but if argument_columns is null, then it shows up as a string (not array) in the column_names?
    – Erasmose
    Mar 22 at 20:18
  • Yes - but if you wrap the new array column in COALESCE and offered ARRAY(pg_get_indexdef(att.attrelid, att.attnum, true)) as the second argument to COALESCE you would always have an array of one of more strings. In that case, JSON_AGG would have to become ARRAY_AGG, or you may have to perform more suitable coersion.... Let me update the answer Mar 23 at 3:21
0

Try pg_get_indexdef, like so:

SELECT pg_get_indexdef(att.attrelid, att.attnum, true)
FROM   pg_catalog.pg_namespace n
JOIN   pg_catalog.pg_class     c ON c.relnamespace = n.oid
JOIN   pg_catalog.pg_attribute att ON att.attrelid = c.oid
JOIN   pg_catalog.pg_index     i ON i.indexrelid = c.oid
JOIN   pg_catalog.pg_am        a ON a.oid = c.relam
CROSS  JOIN LATERAL (
   SELECT ARRAY (SELECT opc.opcname
                 FROM   unnest(i.indclass::oid[]) WITH ORDINALITY o(oid, ord)
                 JOIN   pg_opclass opc ON opc.oid = o.oid
                 ORDER  BY o.ord)
   ) opc(operator_classes)
WHERE  n.nspname !~ '^pg_'
AND    c.relkind = ANY (ARRAY['r', 't', 'i']);
pg_get_indexdef
"substring"(content, 1, 5)
abs(file_id)
child_id
content_tsv
content_tsv
content_tsv
father_id
file_id
file_id
installed_rank
round(sequence_no::double precision)
round(sequence_no::double precision)
success

One should recall that an index can refer to multiple columns, and so can a function require multiple columns as arguments.

That said, by retrieving pg_get_indexdef and applying a little regexp, we can get our arguments

SELECT (regexp_matches(
         regexp_replace(
            regexp_replace(pg_get_indexdef(att.attrelid, att.attnum, true), 
                           att.attname, 
                           '', 
                           'g'), 
            '^\w|^,|^\:|"', 
                      '', 
            'g'),
         '\((.+)\)'))[1] AS fn_args
     , pg_get_indexdef(att.attrelid, att.attnum, true) 
     , i.indrelid::regclass::text AS table
     , c.relname AS index_name
     , a.amname AS index_type
     , opc.operator_classes
     , i.indisunique AS is_unique
     , att.attname as column_names
FROM   pg_catalog.pg_namespace n
JOIN   pg_catalog.pg_class     c ON c.relnamespace = n.oid
JOIN   pg_catalog.pg_attribute att ON att.attrelid = c.oid
JOIN   pg_catalog.pg_index     i ON i.indexrelid = c.oid
JOIN   pg_catalog.pg_am        a ON a.oid = c.relam
CROSS  JOIN LATERAL (
   SELECT ARRAY (SELECT opc.opcname
                 FROM   unnest(i.indclass::oid[]) WITH ORDINALITY o(oid, ord)
                 JOIN   pg_opclass opc ON opc.oid = o.oid
                 ORDER  BY o.ord)
   ) opc(operator_classes)
WHERE  n.nspname !~ '^pg_'
AND    c.relkind = ANY (ARRAY['r', 't', 'i'])
group by
   i.indrelid, c.relname, a.amname, opc.operator_classes, i.indisunique, att.attrelid, att.attnum

Where only indices defined with function calls return rows here:

fn_args pg_get_indexdef table index_name index_type operator_classes is_unique column_names
content, 1, 5 "substring"(content, 1, 5) file_lookup_4k date5_index btree {text_ops} 0 substring
file_id abs(file_id) file_lookup_4k date2_index btree {int4_ops} 0 abs
sequence_no::double precision round(sequence_no::double precision) file_lookup_4k date3_index btree {float8_ops} 0 round
sequence_no::double precision round(sequence_no::double precision) file_lookup_4k date4_index btree {float8_ops} 0 round

Ultimately, you are getting the function name back because that function call is being used to build the index, and not simply the underlying column.

Look at the pg_catalog.pg_index.indexprs column in your query and you will see something like the following, which exposes how the function is indexed:

({FUNCEXPR :funcid 1342 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 
:args ({FUNCEXPR :funcid 316 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 2 :funccollid 0 :inputcollid 0 
:args ({VAR :varno 1 :varattno 2 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 50}) :location -1}) :location 44})
3
  • Thanks @jeff-vermeer I was hoping for something simpler. I think what I am going to do is to parse the index definition in Python and extract the information I need without using regex. I try to stay away from Regex unless there is no choice.
    – Erasmose
    Mar 22 at 5:21
  • 1
    Why use ANY (ARRAY['r', 't', 'i']) instead of IN ('r', 't', 'i')?
    – Vérace
    Mar 22 at 8:22
  • Why ANY (ARRAY['r', 't', 'i']) instead of IN ('r', 't', 'i') ? Copy Pasta! Good catch Mar 22 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.