Difference between revisions of "Reindex"
Jump to navigation
Jump to search
(Created page with "# REINDEX IN Postgres https://www.postgresql.org/docs/9.4/sql-reindex.html REINDEX DATABASE foo; REINDEX TABLE foo delete from ids where table_name='item_tag' and nextid=...") |
|||
| Line 9: | Line 9: | ||
delete from ids where table_name='item_tag' and nextid=212870; | delete from ids where table_name='item_tag' and nextid=212870; | ||
| + | |||
| + | |||
| + | Get reindex status | ||
| + | ``` | ||
| + | SELECT | ||
| + | now()::TIME(0), | ||
| + | a.query, | ||
| + | p.phase, | ||
| + | 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; | ||
| + | ``` | ||
Revision as of 03:25, 15 May 2022
REINDEX IN Postgres
https://www.postgresql.org/docs/9.4/sql-reindex.html
REINDEX DATABASE foo;
REINDEX TABLE foo
delete from ids where table_name='item_tag' and nextid=212870;
Get reindex status
SELECT now()::TIME(0), a.query, p.phase, 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;