87

Is there a way to monitor the progress of the creation of an index in PostgreSQL. I am creating an index on a large table and I would like to see how fast this is occurring.

Is there a way to monitor this?

0

4 Answers 4

66

It is possible since PostgreSQL 12 release (released October 3, 2019).

SELECT
  now()::TIME(0),
  a.query,
  p.phase,
  round(p.blocks_done / p.blocks_total::numeric * 100, 2) AS "% done",
  p.blocks_total,
  p.blocks_done,
  p.tuples_total,
  p.tuples_done,
  ai.schemaname,
  ai.relname,
  ai.indexrelname
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a ON p.pid = a.pid
LEFT JOIN pg_stat_all_indexes ai on ai.relid = p.relid AND ai.indexrelid = p.index_relid;

This can be used to check which index is rebuilding on REINDEX DATABASE command.

See the docs for pg_stat_progress_create_index view and depesz's blog post for details.

5
  • 1
    This just reports the current index reindexing status, not overall indexing. If you run reindex on the entire database, this query looks like an infinite loop is running, since it doesn't even tell you what table/index it's currently on, or how many more indexes are left.
    – Cerin
    Commented Jul 2, 2021 at 14:11
  • Thanks to @AshishBhagasra who proposed edit in which query reports the current index reindexing status.
    – Envek
    Commented Aug 30, 2021 at 11:06
  • @Cerin you are right, the query above tracks only the CREATE INDEX or REINDEX currently in progress as pg_stat_progress_create_index docs states.
    – joseluisq
    Commented Aug 5, 2022 at 11:53
  • sometimes, during the index process, the blocks_total field is zero, so, to avoid the zero division issue, I would add a case when p.blocks_total > 0 Commented Mar 27, 2023 at 15:40
  • 1
    As mentioned by @joseluisq running this query will show you indexes under construction select * from pg_stat_progress_create_index;
    – Eric
    Commented Aug 2, 2023 at 6:57
49

According to Postgres Wiki's Index Maintenance page, you can find out the current state of all your indexes with:

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;

The column num_rows indicates how many rows are covered by your index and index_size will grow as the index is being built.

4
  • 18
    I suspect this may not work for a new index, which may not be visible in the catalog until the transaction that creates it is committed.
    – mustaccio
    Commented Jan 23, 2017 at 19:35
  • 2
    @mustaccio you are correct. i'm creating an index that is taking a long time, and the above command only shows indexes that have already been created; it will not show indexes where 'CREATE INDEX' is still in progress.
    – orange80
    Commented Apr 15, 2017 at 7:21
  • 2
    REINDEX TABLE blocks this query. At least, it did when I ran it on 9.6.
    – RonJohn
    Commented May 5, 2019 at 21:03
  • 4
    This isn't a very useful query. It neither tells you if an index is currently being rebuilt or if it needs to be rebuilt. It just tells you how many indexes you have, which isn't what Op asked for.
    – Cerin
    Commented May 22, 2020 at 14:34
12

So, there is no good way to do it pre Postgres 12, but if you really need to know... first calculate the amount of space the index should take, based on data size * rows + overhead. You can then use something like pfiles or pgtruss to find the files that are being written inside $PGDATA; if your indexes are over 1GB, it will be a series of files like nnnnn.n, where the first set of n's is consistent, and the last n increments for each GB file. Once you know how many files are created, you can watch the growth and figure out how close you are to finishing. Rough estimate, but maybe it helps.

1
  • What, exactly, is pfiles - I did a search and the only thing I could find was this - lsof? Are you referring to Solaris?
    – Vérace
    Commented Dec 13, 2021 at 12:30
5

No, there isn't, even if you are building it in CONCURRENT mode. Although in the past I have kept on eye on the size of the file in the database directory, this isn't really useful since you can only guess how large it is going to be.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.