Index Maintenance

From PostgreSQL wiki
Jump to navigationJump to search


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.

Performance Snippets

Index summary

Works with PostgreSQL

>=9.4

Written in

SQL

Depends on

Nothing 

SELECTpg_class.relname,pg_size_pretty(pg_class.reltuples::bigint)ASrows_in_bytes,pg_class.reltuplesASnum_rows,COUNT(*)AStotal_indexes,COUNT(*)FILTER(WHEREindisunique)ASunique_indexes,COUNT(*)FILTER(WHEREindnatts=1)ASsingle_column_indexes,COUNT(*)FILTER(WHEREindnattsISDISTINCTFROM1)ASmulti_column_indexesFROMpg_namespaceLEFTJOINpg_classONpg_namespace.oid=pg_class.relnamespaceLEFTJOINpg_indexONpg_class.oid=pg_index.indrelidWHEREpg_namespace.nspname='public'ANDpg_class.relkind='r'GROUPBYpg_class.relname,pg_class.reltuplesORDERBYpg_class.reltuplesDESC;

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.

Performance Snippets

Index statistics

Works with PostgreSQL

>=8.1

Written in

SQL

Depends on

Nothing 

SELECTt.schemaname,t.tablename,c.reltuples::bigintASnum_rows,pg_size_pretty(pg_relation_size(c.oid))AStable_size,psai.indexrelnameASindex_name,pg_size_pretty(pg_relation_size(i.indexrelid))ASindex_size,CASEWHENi.indisuniqueTHEN'Y'ELSE'N'ENDAS"unique",psai.idx_scanASnumber_of_scans,psai.idx_tup_readAStuples_read,psai.idx_tup_fetchAStuples_fetchedFROMpg_tablestLEFTJOINpg_classcONt.tablename=c.relnameLEFTJOINpg_indexiONc.oid=i.indrelidLEFTJOINpg_stat_all_indexespsaiONi.indexrelid=psai.indexrelidWHEREt.schemanameNOTIN('pg_catalog','information_schema')ORDERBY1,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. :) 

SELECTpg_size_pretty(sum(pg_relation_size(idx))::bigint)assize,(array_agg(idx))[1]asidx1,(array_agg(idx))[2]asidx2,(array_agg(idx))[3]asidx3,(array_agg(idx))[4]asidx4FROM(SELECTindexrelid::regclassasidx,(indrelid::text||E'\n'||indclass::text||E'\n'||indkey::text||E'\n'||coalesce(indexprs::text,'')||E'\n'||coalesce(indpred::text,''))askeyFROMpg_index)subGROUPBYkeyHAVINGcount(*)>1ORDERBYsum(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:

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

Performance Snippets

Show database bloat

Works with PostgreSQL

>=9.3

Written in

SQL

Depends on

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. 

WITHRECURSIVEindex_detailsAS(SELECT'pgbench_accounts_pkey'::textidx),size_in_pages_indexAS(SELECT(pg_relation_size(idx::regclass)/(2^13))::int4size_pagesFROMindex_details),page_statsAS(SELECTindex_details.*,stats.*FROMindex_details,size_in_pages_index,lateral(SELECTiFROMgenerate_series(1,size_pages-1)i)series,lateral(SELECT*FROMbt_page_stats(idx,i))stats),meta_statsAS(SELECT*FROMindex_detailss,lateral(SELECT*FROMbt_metap(s.idx))meta),pages_rawAS(SELECT*FROMpage_statsORDERBYbtpoDESC),/* XXX: Note ordering dependency within this CTE */pages_walk(item,blk,level)AS(SELECT1,blkno,btpoFROMpages_rawWHEREbtpo_prev=0ANDbtpo=(SELECTlevelFROMmeta_stats)UNIONSELECTCASEWHENlevel=btpoTHENw.item+1ELSE1END,blkno,btpoFROMpages_rawi,pages_walkwWHEREi.btpo_prev=w.blkOR(btpo_prev=0ANDbtpo=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. */btpoASlevel,/* * Ordinal number of item on this level */itemASl_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. */CASEWHENbtpo_next!=0THENfirst_itemENDAShighkey,/* * 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_pointersFROMpages_walkw,pages_rawi,lateral(SELECTcount(distinct(casewhenbtpo_next=0oritemoffset>1then(datacollate"C")end))asdistinct_real_item_keys,count(distinct(casewhenbtpo_next=0oritemoffset>1then(ctid::text::point)[0]::bigintend))asdistinct_block_pointers,(array_agg(data))[1]asfirst_itemFROMbt_page_items(idx,blkno))itemswherew.blk=i.blkno/* Uncomment to avoid showing leaf level (faster): *//* and level > 0*/ORDERBYbtpoDESC,item;

Interpreting bt_page_items() "data" field as a little-endian int4 attribute

Performance Snippets

Show database bloat

Works with PostgreSQL

>=9.2

Written in

SQL

Depends on

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--createorreplacefunctionreverse_bytes_iter(bytesbytea,lengthint,midpointint,indexint)returnsbyteaas$$selectcasewhenindex>=midpointthenbyteselsereverse_bytes_iter(set_byte(set_byte(bytes,index,get_byte(bytes,length-index)),length-index,get_byte(bytes,index)),length,midpoint,index+1)end;$$languagesqlimmutablestrict;createorreplacefunctionreverse_bytes(bytesbytea)returnsbyteaas$$selectreverse_bytes_iter(bytes,octet_length(bytes)-1,octet_length(bytes)/2,0)$$languagesqlimmutablestrict;createorreplacefunctionint4_from_bytea(bytea)returnsint4as$$select('x'||right($1::text,6))::bit(24)::int;$$languagesqlimmutablestrict;createorreplacefunctionint4_from_page_data(text)returnsint4as$$selectint4_from_bytea(reverse_bytes(decode($1,'hex')));$$languagesqlimmutablestrict;---- 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:

References

Retrieved from "https://wiki.postgresql.org/index.php?title=Index_Maintenance&oldid=36165"