28

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?

migrated from stackoverflow.com Jan 22 '12 at 15:52

This question came from our site for professional and enthusiast programmers.

  • if you are using psql, you should try \timing – sftsz Jan 23 '12 at 12:26
19

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.

  • 6
    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 Jan 23 '17 at 19:35
  • @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 Apr 15 '17 at 7:21
  • 1
    REINDEX TABLE blocks this query. At least, it did when I ran it on 9.6. – RonJohn May 5 at 21:03
8

So, there is no good way to do it, 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.

4

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, privacy policy and cookie policy

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