Optimize index bloat output

[MichaelDBA]

Very cool SQL tool! Thanks!!!!!

Using b2 option, we get a lot of unnecessary bloat output for very small indexes:

      | pg_sequence_seqrelid_index                   +| 16 kB  | ~0 bytes (0.00%)     | ~0 bytes (0.00%)     | ~16 kB  |         90
        |   (pg_catalog.pg_sequence)                    |        |                      |                      |         |
        | pg_shdescription_o_c_index                   +| 16 kB  | ~0 bytes (0.00%)     | ~0 bytes (0.00%)     | ~16 kB  |         90
        |   (pg_catalog.pg_shdescription)               |        |                      |                      |         |
        | pg_tablespace_oid_index                      +| 16 kB  | ~0 bytes (0.00%)     | ~0 bytes (0.00%)     | ~16 kB  |         90
        |   (pg_catalog.pg_tablespace)                  |        |                      |                      |         |
        | pg_tablespace_spcname_index                  +| 16 kB  | ~0 bytes (0.00%)     | ~0 bytes (0.00%)     | ~16 kB  |         90
        |   (pg_catalog.pg_tablespace)                  |        |                      |                      |         |
        | pg_trigger_oid_index                         +| 16 kB  | ~0 bytes (0.00%)     | ~0 bytes (0.00%)     | ~16 kB  |         90
        |   (pg_catalog.pg_trigger)                     |        |                      |                      |         |
        | pg_trigger_tgconstraint_index                +| 16 kB  | ~0 bytes (0.00%)     | ~0 bytes (0.00%)     | ~16 kB  |         90
        |   (pg_catalog.pg_trigger)                     |        |                      |                      |         |

Can you optimize the where clause so that very small or not used or not populated indexes are not shown in the output?

Also for b4 output, same kinda thing:

              Table               |                   Index                   | Table size | Index size | Index Scans | Wasted, % |   Wasted
----------------------------------+-------------------------------------------+------------+------------+-------------+-----------+------------
 sample_c1.stock_0                | sample_c1.stock_0_warehouse_id_idx        | 8192 bytes | 16 kB      |           0 |      99.0 | 16 kB
 sample_c2.activity               | sample_c2.activity_pid_idx                | 8192 bytes | 16 kB      |           0 |      99.0 | 16 kB
 sample.items_2                   | sample.items_2_pkey                       | 8192 bytes | 16 kB      |           1 |      99.0 | 16 kB
 sample_c1.stock_0                | sample_c1.stock_0_item_id_idx             | 8192 bytes | 16 kB      |           0 |      99.0 | 16 kB
 sample_c1.items_2                | sample_c1.items_2_pkey                    | 8192 bytes | 16 kB      |           0 |      99.0 | 16 kB
 sample.warehouses                | sample.warehouses_pkey                    | 8192 bytes | 16 kB      |           1 |      99.0 | 16 kB
 sample.stock_0                   | sample.stock_0_item_id_idx                | 8192 bytes | 16 kB      |           0 |      99.0 | 16 kB
 sample_c2.stock_0                | sample_c2.stock_0_warehouse_id_idx        | 8192 bytes | 16 kB      |           0 |      99.0 | 16 kB
 sample.stock_0                   | sample.stock_0_warehouse_id_idx           | 8192 bytes | 16 kB      |           0 |      99.0 | 16 kB

[tembo]

Looking at the relevant SQL files, here's some context that may help with implementation:

For b2 (sql/b2_btree_estimation.sql):

The current query only filters on idx.relpages > 0 (line ~40 in the subquery). To filter out small indexes, you could add a minimum size threshold in step4 or the final SELECT, for example:

where real_size > 32768  -- 32 kB minimum

The output shows pg_catalog indexes because the query joins directly on pg_class/pg_index without schema filtering. The nspname is already available in step1, so adding this to the WHERE would exclude system catalogs:

and schema_name not in ('pg_catalog', 'information_schema')

For b4 (sql/b4_btree_pgstattuple.sql):

This query already uses pg_stat_user_indexes so it excludes system catalogs, but has no size filter. The data CTE could add:

and pg_relation_size(p.indexrelid) > 32768  -- 32 kB minimum

Or filter in the final SELECT to exclude indexes where absolute wasted space is trivial:

where free_space > 32768  -- only show if wasted > 32 kB

Suggested thresholds to consider:

• Minimum index size: 32 kB or 64 kB (2-4 pages)
• Minimum bloat/wasted: Could also require both a percentage (e.g., > 20%) AND an absolute size (e.g., > 1 MB) to be shown

The existing indexrelname ~ '' placeholder in b4 (line ~24) could also be repurposed to allow users to filter by pattern if needed.

[MichaelDBA]

Also, is there a way to exclude system schemas?

[NikolayS]

thanks -- will look into it

regarding system catalogs, they can also be bloated.. So, it would make sense to exclude them optionally. Perhaps, we need a switcher.