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
Add a comment
|
4 Answers
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.
-
1This 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.– CerinCommented Jul 2, 2021 at 14:11
-
Thanks to @AshishBhagasra who proposed edit in which query reports the current index reindexing status.– EnvekCommented Aug 30, 2021 at 11:06
-
@Cerin you are right, the query above tracks only the
CREATE INDEXorREINDEXcurrently in progress aspg_stat_progress_create_indexdocs states. 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 > 0Commented Mar 27, 2023 at 15:40 -
1As mentioned by @joseluisq running this query will show you indexes under construction
select * from pg_stat_progress_create_index;– EricCommented Aug 2, 2023 at 6:57
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.
-
18I 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. 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.– orange80Commented Apr 15, 2017 at 7:21
-
2REINDEX TABLE blocks this query. At least, it did when I ran it on 9.6.– RonJohnCommented May 5, 2019 at 21:03
-
4This 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.– CerinCommented May 22, 2020 at 14:34
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.
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.
