SQL Queries - Common SQL Queries - How to count the number of indexes in a schema

Purpose of Query

This query can be relevant when you have performance issues while reading data from the schema.

Indexes should improve performance significanly for all reading operations.

Applicable CAST Version
8.3.x (tick)
8.2.x (tick)
Applicable RDBMS
Oracle Server (tick)
Microsoft SQL Server (tick)
CSS (tick)
Query for CSS
  ns.nspname               AS schema_name,
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
and ns.nspname like '<MY_SCHEMA>'
group by ns.nspname
order by ns.nspname
Query result example


Query result interpretation

There are 694 indexes on the tables of schema MY_SCHEMA.

Query for Oracle - TO BE DONE

Query result example

Query for Microsoft SQL Server - TO BE DONE

Query result example


Related Pages