Index Maintenance

From PostgreSQL wiki

Jump to: navigation, 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.

Contents

Index summary

Here's a sample query to pull the number of rows, indexes, and some info about those indexes for each table. (Only works on 8.3; ditch the pg_size_pretty if you’re on an earlier version)

Performance Snippets

Index summary

Works with PostgreSQL

>=8.1

Written in

SQL

Depends on

Nothing

SELECT
    pg_class.relname,
    pg_size_pretty(pg_class.reltuples::BIGINT) AS rows_in_bytes,
    pg_class.reltuples AS num_rows,
    COUNT(indexname) AS number_of_indexes,
    CASE WHEN x.is_unique = 1 THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    SUM(CASE WHEN number_of_columns = 1 THEN 1
              ELSE 0
            END) AS single_column,
    SUM(CASE WHEN number_of_columns IS NULL THEN 0
             WHEN number_of_columns = 1 THEN 0
             ELSE 1
           END) AS multi_column
FROM pg_namespace 
LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
LEFT OUTER JOIN
       (SELECT indrelid,
           MAX(CAST(indisunique AS INTEGER)) AS is_unique
       FROM pg_index
       GROUP BY indrelid) x
       ON pg_class.oid = x.indrelid
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid  )
    AS foo
    ON pg_class.relname = foo.ctablename
WHERE 
     pg_namespace.nspname='public'
AND  pg_class.relkind = 'r'
GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
ORDER BY 2;

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

SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
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:

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.

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

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
--
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:

References

Personal tools