Index Maintenance
One day, you will probably need to cope with routine reindexing on your database, particularly if you don't use VACUUM aggressively enough. A particularly handy command in this area is CLUSTER, which can help with other types of cleanup.
Avoid using VACUUM FULL in versions 8.4 and earlier.
Index summary
Here's a sample query to pull the number of rows, indexes, and some info about those indexes for each table.
SELECT
pg_class.relname,
pg_size_pretty(pg_class.reltuples::bigint) AS rows_in_bytes,
pg_class.reltuples AS num_rows,
COUNT(*) AS total_indexes,
COUNT(*) FILTER ( WHERE indisunique) AS unique_indexes,
COUNT(*) FILTER ( WHERE indnatts = 1 ) AS single_column_indexes,
COUNT(*) FILTER ( WHERE indnatts IS DISTINCT FROM 1 ) AS multi_column_indexes
FROM
pg_namespace
LEFT JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
LEFT JOIN pg_index ON pg_class.oid = pg_index.indrelid
WHERE
pg_namespace.nspname = 'public' AND
pg_class.relkind = 'r'
GROUP BY pg_class.relname, pg_class.reltuples
ORDER BY pg_class.reltuples DESC;
Index size/usage statistics
Table & index sizes along which indexes are being scanned and how many tuples are fetched. See Disk Usage for another view that includes both table and index sizes.
SELECT
t.schemaname,
t.tablename,
c.reltuples::bigint AS num_rows,
pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
psai.indexrelname AS index_name,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique",
psai.idx_scan AS number_of_scans,
psai.idx_tup_read AS tuples_read,
psai.idx_tup_fetch AS tuples_fetched
FROM
pg_tables t
LEFT JOIN pg_class c ON t.tablename = c.relname
LEFT JOIN pg_index i ON c.oid = i.indrelid
LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid
WHERE
t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;
Duplicate indexes
Finds multiple indexes that have the same set of columns, same opclass, expression and predicate -- which make them equivalent. Usually it's safe to drop one of them, but I give no guarantees. :)
SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size,
(array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2,
(array_agg(idx))[3] as idx3, (array_agg(idx))[4] as idx4
FROM (
SELECT indexrelid::regclass as idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) as key
FROM pg_index) sub
GROUP BY key HAVING count(*)>1
ORDER BY sum(pg_relation_size(idx)) DESC;
Index Bloat
Based on check_postgres
One of the common needs for a REINDEX is when indexes become bloated due to either sparse deletions or use of VACUUM FULL (with pre 9.0 versions). An estimator for the amount of bloat in a table has been included in the check_postgres script, which you can call directly or incorporate into a larger monitoring system. Scripts based on this code and/or its concepts from other sources include:
- bloat view (Dimitri Fontaine) - extracted from check_postgres
- Visualizing Postgres - index_byte_sizes view (Michael Glaesemann, myYearbook)
- OmniTI Tasty Treats for PostgreSQL - shell and Perl pg_bloat_report scripts
New query
A new query has been created to have a better bloat estimate for Btree indexes. Unlike the query from check_postgres, this one focus only on BTree index its disk layout.
See articles about it.
The monitoring script check_pgactivity is including a check based on this work.
Summarize keyspace of a B-Tree index
Show database bloat
>=9.3
SQL
contrib/pageinspect
The following query uses contrib/pageinspect to summarize the keyspace of a B-Tree quickly. This can be useful to experts that wish to determine exactly how an index may have become unbalanced over time. It visualizes the keyspace of the index.
The query outputs the highkey for every page, starting from the root and working down, in logical/keyspace order.
If the query takes too long to execute, consider uncommenting "/* and level > 0 */" to make it only include internal pages.
See also: "PostgreSQL index bloat under a microscope" blogpost
Note: You are expected to change "pgbench_accounts_pkey" to the name of the index that is to be summarized.
WITH RECURSIVE index_details AS (
SELECT
'pgbench_accounts_pkey'::text idx
),
size_in_pages_index AS (
SELECT
(pg_relation_size(idx::regclass) / (2^13))::int4 size_pages
FROM
index_details
),
page_stats AS (
SELECT
index_details.*,
stats.*
FROM
index_details,
size_in_pages_index,
lateral (SELECT i FROM generate_series(1, size_pages - 1) i) series,
lateral (SELECT * FROM bt_page_stats(idx, i)) stats
),
meta_stats AS (
SELECT
*
FROM
index_details s,
lateral (SELECT * FROM bt_metap(s.idx)) meta
),
pages_raw AS (
SELECT
*
FROM
page_stats
ORDER BY
btpo DESC
),
/* XXX: Note ordering dependency within this CTE */
pages_walk(item, blk, level) AS (
SELECT
1,
blkno,
btpo
FROM
pages_raw
WHERE
btpo_prev = 0
AND btpo = (SELECT level FROM meta_stats)
UNION
SELECT
CASE WHEN level = btpo THEN w.item + 1 ELSE 1 END,
blkno,
btpo
FROM
pages_raw i,
pages_walk w
WHERE
i.btpo_prev = w.blk OR (btpo_prev = 0 AND btpo = w.level - 1)
)
SELECT
/* Uncomment if these details interesting */
/*
idx,
btpo_prev,
btpo_next,
*/
/*
* "level" is level of tree -- 0 is leaf. First tuple returned is root.
*/
btpo AS level,
/*
* Ordinal number of item on this level
*/
item AS l_item,
/*
* Block number, and details of page
*/
blkno,
btpo_flags,
TYPE,
live_items,
dead_items,
avg_item_size,
page_size,
free_size,
/*
* distinct_real_item_keys is how many distinct "data" fields on page
* (excludes highkey).
*
* If this is less than distinct_block_pointers on an internal page, that
* means that there are so many duplicates in its children that there are
* duplicate high keys in children, so the index is probably pretty bloated.
*
* Even unique indexes can have duplicates. It's sometimes interesting to
* watch out for how many distinct real items there are within leaf pages,
* compared to the number of live items, or total number of items. Ideally,
* these will all be exactly the same for unique indexes.
*/
distinct_real_item_keys,
/*
* Per pageinspect docs, first item on non-rightmost page on level is "high
* key" item, which represents an upper bound on items on the page.
* (Rightmost pages are sometimes considered to have a conceptual "positive
* infinity" item, and are shown to have a high key that's NULL by this query)
*
* This can be used to visualize how finely or coarsely separated the
* keyspace is.
*
* Note that below int4_from_page_data() function could produce more useful
* visualization of split points.
*/
CASE WHEN btpo_next != 0 THEN first_item END AS highkey,
/*
* distinct_block_pointers is table blocks that are pointed to by items on
* the page (not including high key, which doesn't point anywhere).
*
* This is interesting on leaf pages, because it indicates how fragmented the
* index is with respect to table accesses, which is important for range
* queries.
*
* This should be redundant on internal levels, because all downlinks in internal
* pages point to distinct blocks in level below.
*/
distinct_block_pointers
FROM
pages_walk w,
pages_raw i,
lateral (
SELECT
count(distinct (case when btpo_next = 0 or itemoffset > 1 then (data collate "C") end)) as distinct_real_item_keys,
count(distinct (case when btpo_next = 0 or itemoffset > 1 then (ctid::text::point)[0]::bigint end)) as distinct_block_pointers,
(array_agg(data))[1] as first_item
FROM bt_page_items(idx, blkno)
) items
where w.blk = i.blkno
/* Uncomment to avoid showing leaf level (faster): */
/* and level > 0*/
ORDER BY btpo DESC, item;
Interpreting bt_page_items() "data" field as a little-endian int4 attribute
Show database bloat
>=9.2
SQL
contrib/pageinspect
The following convenience functions can be used to display the "data" field in bt_page_items() as their native type, at least for indexes whose pg_attribute entries consist of a single int4/integer attribute. This includes SERIAL primary key indexes. It can be used to make the above "Summarize keyspace of a B-Tree index" query display the keyspace split points using native type representation.
Note: The byteswap is only necessary on little-endian (Intel) CPUs.
--
-- Sources:
--
-- https://stackoverflow.com/questions/17208945/whats-the-easiest-way-to-represent-a-bytea-as-a-single-integer-in-postgresql
-- https://stackoverflow.com/questions/11142235/convert-bigint-to-bytea-but-swap-the-byte-order
--
create or replace function reverse_bytes_iter(bytes bytea, length int, midpoint int, index int)
returns bytea as
$$
select case when index >= midpoint then bytes else
reverse_bytes_iter(
set_byte(
set_byte(bytes, index, get_byte(bytes, length-index)),
length-index, get_byte(bytes, index)
),
length, midpoint, index + 1
)
end;
$$ language sql immutable strict;
create or replace function reverse_bytes(bytes bytea) returns bytea as
$$
select reverse_bytes_iter(bytes, octet_length(bytes)-1, octet_length(bytes)/2, 0)
$$
language sql immutable strict;
create or replace function int4_from_bytea(bytea) returns int4
as $$
select ('x' || right($1::text, 6))::bit(24)::int;
$$
language sql immutable strict;
create or replace function int4_from_page_data(text) returns int4
as $$
select int4_from_bytea(reverse_bytes(decode($1, 'hex')));
$$
language sql immutable strict;
--
-- Use:
--
-- postgres=# select *, int4_from_page_data(data) from bt_page_items('f', 1) limit 15;
-- itemoffset │ ctid │ itemlen │ nulls │ vars │ data │ int4_from_page_data
-- ────────────┼────────────┼─────────┼───────┼──────┼─────────────────────────┼─────────────────────
-- 1 │ (17698,69) │ 16 │ f │ f │ 5c 00 00 00 00 00 00 00 │ 92
-- 2 │ (0,1) │ 16 │ f │ f │ 01 00 00 00 00 00 00 00 │ 1
-- 3 │ (8849,126) │ 16 │ f │ f │ 01 00 00 00 00 00 00 00 │ 1
-- 4 │ (17699,25) │ 16 │ f │ f │ 01 00 00 00 00 00 00 00 │ 1
-- 5 │ (17699,26) │ 16 │ f │ f │ 01 00 00 00 00 00 00 00 │ 1
-- 6 │ (0,2) │ 16 │ f │ f │ 02 00 00 00 00 00 00 00 │ 2
-- 7 │ (8849,125) │ 16 │ f │ f │ 02 00 00 00 00 00 00 00 │ 2
-- 8 │ (17699,23) │ 16 │ f │ f │ 02 00 00 00 00 00 00 00 │ 2
-- 9 │ (17699,24) │ 16 │ f │ f │ 02 00 00 00 00 00 00 00 │ 2
-- 10 │ (0,3) │ 16 │ f │ f │ 03 00 00 00 00 00 00 00 │ 3
-- 11 │ (8849,124) │ 16 │ f │ f │ 03 00 00 00 00 00 00 00 │ 3
-- 12 │ (17699,21) │ 16 │ f │ f │ 03 00 00 00 00 00 00 00 │ 3
-- 13 │ (17699,22) │ 16 │ f │ f │ 03 00 00 00 00 00 00 00 │ 3
-- 14 │ (0,4) │ 16 │ f │ f │ 04 00 00 00 00 00 00 00 │ 4
-- 15 │ (8849,123) │ 16 │ f │ f │ 04 00 00 00 00 00 00 00 │ 4
-- (15 rows)
Unused Indexes
Since indexes add significant overhead to any table change operation, they should be removed if they are not being used for either queries or constraint enforcement (such as making sure a value is unique). How to find such indexes:
- Index pruning techniques
- Finding unused indexes
- Finding useless indexes
- 'Monitor unused indexes' by Johnny Morano
References
- Index statistics queries from "Refactoring SQL Applications" review
