When I’m reviewing a PostgreSQL’s database structure, I often wanna look at one or more tables to understand their current index designs. The check_indexes function in our open source SmartPostgres Box of Tricks (Github) makes that easy:
|
1 |
select * from check_indexes('public', 'users'); |
The parameters include:
- v_schema_name: can be null (which returns all schemas), or a single schema’s name
- v_table_name: can be null (which returns all tables), or a single table’s name
- v_warning_format: defaults to ‘rows’, which is the only supported output type for now. We return one row per index, but if an index has multiple warnings, it will get multiple rows in the output. (At some point in the future, we’ll support json, xml, or varchar output for multiple warnings in the same row.)
- v_debug_level: 0 = no messages, 1 = critical messages, 2 = all messages.
It returns a result set describing the indexes:
The Output Columns
- schema_name, table_name, index_name
- index_type – because my Fundamentals of Index Tuning for Postgres class focuses on btree indexes, btree indexes are the most well-supported type, but we do at least return basic info for all other index types.
- index_definition
- size_kb
- estimated_tuples, estimated_tuples_as_of – from pg_class.reltuples, which is updated by things like vacuum and analyze, so we put dates in here where possible so you know roughly when the statistics were updated.
- dead_tuples – so you know if vacuum isn’t keeping up
- last_autovacuum, last_manual_nonfull_vacuum – but unfortunately Postgres doesn’t automatically track the last manual full vacuum date.
- fill_factor, is_unique, is_primary
- table_oid, index_oid – useful if you want to join the output of check_indexes to other diagnostic queries.
- reloptions – from pgclass
- drop_object_command – in case you need to script out dropping the object
- and then the warnings columns start.
The Warning Columns
Check_indexes not only returns simple metadata, but also gives you warnings and advice about what’s happening with your indexes. Some of the warnings include:
- Vacuum full or cluster is running right now on a table, indicating that performance will be slower or the object may even be offline
- Transaction ID wraparound risk
- Autovacuum not keeping up
- Outdated statistics
- Vacuum running now
- Autovacuum settings manually specified at the table level
The warnings show up in these output columns:
- priority – 1 is being the highest, most urgent warning
- warning_summary – like the headline news of the warning
- warning_details – specifics including metrics
- warning_url – copy/paste this into your browser to get advice and learn more about what the warning is telling you
For Support or Changes
Use Github Issues in the Box of Tricks repo. When filing an issue, make sure to include your Postgres version and any screenshots to help explain the problem you’re running into.
For feature requests, please be prepared to contribute the code yourself. Open source doesn’t mean free consulting or development from others – it means being willing to roll up your sleeves if there’s something you really want included. Don’t be scared – the source code on this one is really simple! Hop in there and get started.
Hope check_indexes makes your job & training easier. Cheers!