PostgreSQL – Metadata – Index Columns

Background

Admitting nothing real, nor original.

I am just trying to close out this Chrome Tab that showcases the best of Stack Overflow.

Stack Overflow

Luke Francl
List columns with indexes in PostgreSQL
Link

StackOverflow-IndexMysql-LukeFrancl

SQL

Outline

  1. Tables
    • pg_catalog.pg_class
    • pg_catalog.pg_namespace
      • pg_class.relnamespace = pg_namespace.oid
    • pg_catalog.pg_index
      • pg_class.relnamespace = pg_index.indrelid
    • pg_catalog.pg_attribute
      • pg_attribute.attrelid = pg_index.indrelid
        and pg_attribute.attnum = any(pg_index.indkey)
  2. Columns => Filter
    • pg_index.indkey
      • Array list of columns that constitute an index
      • pg_attribute.attnum = any(pg_index.indkey)
        • Match attribute/column number on any of the columns that is listed for an Index
  3. Columns => Projected
    • Find Position of Column number in Column Index List
      • array_position( indkey, attnum )
    • Index Definition
      • Get Index Definition
      • pg_get_indexdef(indexrelid)

Index – Column List

Code


with cteIndexColumn
(
      schema
    , object
    , index
    , indexrelid
    , indexColumnListAsArray
    , indisprimary
    , indisunique
    , indisclustered
    , indisvalid
    , indexColumnPosition
    , columnName
)

as
(
    select 

              tblSchema.nspname
                as schema

            , tblTable.relname
                as  object

            , tblIndexAnchor.relname
                as "index"

            , tblIndex.indexrelid
                as indexrelid

            , tblIndex.indkey
                as indexColumnListAsArray

            , tblIndex.indisprimary

            , tblIndex.indisunique

            , tblIndex.indisclustered   

            , tblIndex.indisvalid

            -- 0 based
            , 1 + array_position
                (
                      tblIndex.indkey
                    , tblColumn.attnum
                )
                as indexColumnPosition

            , tblColumn.attname
                as columnName

    from   pg_catalog.pg_class tblTable

    inner join pg_catalog.pg_namespace tblSchema

            on tblTable.relnamespace = tblSchema.oid

    inner join pg_catalog.pg_index tblIndex
            on tblTable.oid = tblIndex.indrelid

    inner join pg_catalog.pg_attribute tblColumn
            on tblColumn.attrelid = tblIndex.indrelid
            and tblColumn.attnum = any(tblIndex.indkey)

    inner join pg_catalog.pg_class tblIndexAnchor
            on tblIndex.indexrelid = tblindexanchor.oid

    where  tblTable.relkind in
                (
                    'r'
                )

    and     tblSchema.nspname not in
                (
                      'pg_catalog'
                    , 'information_schema'
                )

)
, cteIndexColumnAggregated
(
      schema
    , object
    , index
    , indexrelid
    , indexColumnListAsArray

    , indisprimary
    , indisunique
    , indisclustered
    , indisvalid

    , columnListArray
    , columnList    

)
as
(
    select

          schema
        , object
        , index
        , indexrelid
        , indexColumnListAsArray

        , indisprimary
        , indisunique
        , indisclustered
        , indisvalid

        , array_agg
          (

                    columnName

                    ORDER BY
                        indexColumnPosition
          )
          as "columnListArray"

        , array_to_string
            (
                array_agg
                (

                    columnName

                    ORDER BY
                        indexColumnPosition
                )
                , ', '
            ) as "columnList"

    from cteIndexColumn

    group by
          schema
        , object
        , indexColumnListAsArray
        , index
		, indexrelid

        , indisprimary
        , indisunique
        , indisclustered
        , indisvalid

)

select 

          schema

        , object

        , index

        , pg_get_indexdef(indexrelid)
        	as indexDef

    	, columnListArray

    	, columnList

        , indisprimary
        , indisunique
        , indisclustered
        , indisvalid

from   cteIndexColumnAggregated

order by
          schema
        , object
        , index

Output

Output / 01

pg_catalog.pg_index_aggregated.20190809.1152PM

Output / 02

pg_catalog.pg_index_aggregated.20190810.1215PM.PNG

References

  1. PostgreSQL
    • Functions Array
      Link
    • System Information Functions
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s