Pg_index

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 …

List PostgreSQL Object Comments with SQL pg_description is where PostgreSQL keeps every comment attached with COMMENT ON, whether the target is a table, a column, an index, or a function. Psql shows these comments, and so does any tool that reads the catalog, which makes them a cheap way to keep a schema …

How to Find Index Bloat and Wasted Space in PostgreSQL Just like tables, PostgreSQL indexes can become bloated over time. When rows are updated or deleted, the old index entries are not removed immediately. They stay in the index as dead entries, wasting space and slowing down index scans. B-tree indexes are the most …

Find Unused PostgreSQL Indexes Before They Cost You Which indexes have gone unused since the statistics were last reset? pg_stat_all_indexes counts every index scan per index, and an index whose idx_scan is still zero after weeks of normal traffic is a candidate to drop. The query below lists those indexes, largest …

Find the Largest PostgreSQL Indexes by Size Which indexes are taking up the most disk in this database? pg_index joined to pg_class answers that in one query: every index with its table, schema, tablespace, size, and the command that would recreate it, sorted with the biggest first. It is the inventory step before any …