Postgresql :- Error – “relation … is not a btree index”

Background

Reviewing PostgreSQL metadata and ran into a tiny error.

Error

The error reads …

Text


relation is not a btree index

Image

isNotABTreeIndex.01.20191022.1101AM

Premise

PostgreSQL supports a variety of index types.

Among supported index types are B-tree and fulltext ( Gin and Gist ).

Remediation

Outline

Because the set of operations supported by an Index Type may vary, it is best to inspect index types and make adjustment based on the type.

Metadata

Tables and Indexes access methods are exposed via the Relation Access Method table.

The name of the table is pg_catalog.pg_am; am stands for Access Method.

Script

SQL


select 
    
          tblN.nspname 
            as "schema"
      
        , tblPCC.relname 
            as "table"

        , tblPCPI.relname 
            as "index"
        
        , tblPCI.indisprimary
            as "primary"
          
        , tblPCC.reltuples::int
            as "tableNumberofRows"
        
        , tblPCC.relpages 
            as "tableNumberofPages"
    
        , pg_relation_size
            (
                tblPCC.oid                  
            )       
            as "tableSize"

        , pg_size_pretty
          (
                pg_relation_size
                (
                    tblPCC.oid
                )
            ) as "tableSizeFormatted"
            
    
        , pg_relation_size
            (
                tblPCPI.oid
            )       
            as "indexSize"

        , pg_size_pretty
          (
          
                  pg_relation_size
                    (
                        tblPCPI.oid
                    )
                
          )
          as "indexSizeFormatted"

        , pg_size_pretty
          (
      
              pg_indexes_size
              (   
                    tblPCC.oid
              )
          )
          as "indexAllSizeFormatted"

        , tblIAM.amname 
            as "indexType"

from   pg_catalog.pg_class tblPCC
    
join pg_catalog.pg_namespace tblN

    on tblPCC.relnamespace = tblN.oid
    
inner join  pg_catalog.pg_index tblPCI

    on tblPCC.oid = tblPCI.indrelid

inner join  pg_catalog.pg_class tblPCPI

    on tblPCI.indexrelid = tblPCPI.oid


inner join pg_catalog.pg_am tblIAM

    on tblIAM.oid = tblPCPI.relam

where tblPCC.relkind in
        (
            -- r = ordinary table
            -- , i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign table
              'r'
            , 'v' -- view 
            , 'm' -- Materialized View
            , 't' -- TOAST Table
            , 'p'
        )
    

and   tblN.nspname not in 
        (
              'information_schema'
            , 'pg_catalog'
            , 'pg_toast'
        )
        
     
order by     
          tblN.nspname 
    
        , tblPCC.relname
        
        , tblPCPI.relname
    ;        

    

Output

index.bible.01.20191022.1154AM

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