Audit Single vs Multi-Column Indexes in PostgreSQL

calendar Apr 16, 2026 · 4 min read · postgresql administration indexing database pg_index pg_class pg_namespace  ·
Share on: linkedin copy

Audit Single vs Multi-Column Indexes in PostgreSQL

Not all indexes are created equal. A single-column index covers one column and is simple to reason about. A multi-column (composite) index covers two or more columns and can serve a wider range of queries — but only when the leading columns match the query filter.

As a database grows, it is easy to accumulate indexes without a clear picture of what type each table has. This query counts single-column and multi-column indexes per table so you can spot tables that may be over-indexed, poorly indexed, or missing composite indexes altogether.

Sample Code

 1SELECT
 2    pg_class.relname,
 3    COUNT(*) FILTER (WHERE indnatts = 1)                AS single_column_indexes,
 4    COUNT(*) FILTER (WHERE indnatts IS DISTINCT FROM 1) AS multi_column_indexes
 5FROM
 6    pg_namespace
 7    LEFT JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
 8    LEFT JOIN pg_index ON pg_class.oid = pg_index.indrelid
 9WHERE
10    pg_namespace.nspname = 'public'
11    AND pg_class.relkind = 'r'
12GROUP BY pg_class.relname, pg_class.reltuples
13ORDER BY pg_class.reltuples DESC;

Notes: Works on all supported PostgreSQL versions. Scoped to the public schema by default — change nspname = 'public' to query a different schema. Returns all tables in the schema, including those with no indexes (both counts will be zero). Results are sorted by estimated row count, largest tables first.

Code Breakdown

  • pg_namespace — The system catalog that stores schema names. Used here to filter results to a single schema.
  • pg_class — The system catalog that stores tables, indexes, views, and other relations. Joined to pg_namespace to get tables in the target schema.
  • pg_index — The system catalog that stores one row per index. The column indnatts holds the number of columns in the index.
  • pg_class.relkind = 'r' — Filters to ordinary tables only. The value 'r' means regular table. This excludes views, sequences, and other relation types.
  • indnatts = 1 — Selects indexes with exactly one column (single-column indexes).
  • indnatts IS DISTINCT FROM 1 — Selects indexes with more than one column (multi-column indexes). Using IS DISTINCT FROM handles any unexpected null values safely.
  • COUNT(*) FILTER (WHERE ...) — Counts rows that match a condition within the aggregate. This lets both counts be calculated in a single pass over the data.
  • pg_class.reltuples — The estimated number of rows in the table, as tracked by the planner statistics. Used for sorting but not displayed.

Key Points

  • A table with many single-column indexes but no multi-column indexes may be missing composite indexes that could serve multi-column WHERE clauses more efficiently.
  • A table with many multi-column indexes warrants a review of each index's leading column — queries that do not filter on the leading column cannot use the index.
  • Tables with zero indexes in both columns may be lookup or configuration tables that are always scanned in full, which may be intentional.

Insights and Explanations

When to prefer a multi-column index:

If your queries consistently filter on two or more columns together — for example, WHERE user_id = $1 AND created_at > $2 — a composite index on (user_id, created_at) will outperform two separate single-column indexes. PostgreSQL can only use one index per table scan without a bitmap OR operation.

When single-column indexes make sense:

Single-column indexes are best for high-selectivity columns that are queried independently. A primary key, a UUID, or a foreign key column each make good candidates. Adding more columns to these indexes rarely helps and increases write overhead.

Reading the output:

A table showing single_column_indexes = 5 and multi_column_indexes = 0 is a signal to check whether queries on that table ever filter on multiple columns simultaneously. If they do, a composite index could replace two or three single-column indexes and improve query performance while reducing index bloat.

Additional Considerations

  • Schema scope: Change pg_namespace.nspname = 'public' to audit a different schema, or remove the filter entirely to see all schemas at once.
  • Index usage: This query counts indexes but does not show whether they are being used. Combine with pg_stat_user_indexes to identify which indexes have zero scans and may be safe to drop.
  • Permissions: Any user with access to the pg_catalog views can run this query.

References

Posts in this series