# PostgreSQL Index bloat under a microscope

DevFeed: [PostgreSQL Index bloat under a microscope](<https://devfeed.tech/articles/postgresql-index-bloat-under-a-microscope-33655.md>)

Original publisher: [Read original article](<https://pgeoghegan.blogspot.com/2017/07/postgresql-index-bloat-microscope.html>)

Author: Peter Geoghegan (noreply@blogger.com)

Published: 2017-07-19T05:43:00Z

Content type: article

Language: en

Sources: [Peter Geoghegan's blog](<https://devfeed.tech/sources/peter-geoghegan-s-blog.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Database](<https://devfeed.tech/topics/database.md>)

Tags: [index](<https://devfeed.tech/tags/index.md>), [indexes](<https://devfeed.tech/tags/indexes.md>), [internals](<https://devfeed.tech/tags/internals.md>), [nbtree](<https://devfeed.tech/tags/nbtree.md>), [pageinspect](<https://devfeed.tech/tags/pageinspect.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [query](<https://devfeed.tech/tags/query.md>), [wiki](<https://devfeed.tech/tags/wiki.md>)

## AI overview

This technical article presents a PostgreSQL query that summarizes the keyspace of a target B-Tree index. Using pageinspect and a pgbench database, it examines index levels, page utilization, indexed value ranges, and block pointers, then interprets the resulting structure and relationships between the index and table.

## Source excerpt

I've posted a snippet query to the PostgreSQL Wiki that "summarizes the keyspace" of a target B-Tree index. This means that it displays which range of indexed values belong on each page, starting from the root. It requires pageinspect. The query recursively performs a breadth-first search. Along the way, it also displays information about the space utilization of each page, and the number of distinct key values that actually exist on the page, allowing you to get a sense of how densely filled each page is relative to what might be expected. The query is available from: https://wiki.postgresql.org/wiki/Index_Maintenance#Summarize_keyspace_of_a_B-Tree_index If I use the query against the largest index that results from initializing a pgbench database at scale factor 10 (pgbench_accounts_pkey), the query takes about 3 seconds to execute on my laptop, and returns the following: level | l_item | blkno | btpo_flags | type | live_items | dead_items | avg_item_size | page_size | free_size | distinct_real_item_keys | highkey | distinct_block_pointers -------+--------+-------+------------+------+------------+------------+---------------+-----------+-----------+-------------------------+---------+------------------------- 2 | 1 | 290 | 2 | r | 10 | 0 | 15 | 8192 | 7956 | 10 | | 10 1 | 1 | 3 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 103945 | 284 1 | 2 | 289 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 207889 | 284 1 | 3 | 575 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 311833 | 284 1 | 4 | 860 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 415777 | 284 1 | 5 | 1145 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 519721 | 284 1 | 6 | 1430 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 623665 | 284 1 | 7 | 1715 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 727609 | 284 1 | 8 | 2000 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 831553 | 284 1 | 9 | 2285 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 935497 | 284 1 | 10 | 2570 | 0 | i | 177 | 0 | 15 | 8192 | 4616 | 177 | | 177 0 | 1 | 1 |