- Pivotal Greenplum 4.3.x
- Operating System- Red Hat Enterprise Linux 6.x
This article helps users to track the progress of "analyze" during gpdbrestore utility.
Long-running "analyze" during gpdbrestore left customer in a dilemma as there is no way to track the progress.
As part of gpdbrestore, the system will perform "analyze" on the entire database or list of tables unless --noanalyze option is specified.
For large databases, the system will take time to do analyze during gpdbrestore if the tables were never had statistics collected or database is restored to a new cluster. To identify progress during analyze step in such scenarios, following steps should be performed:
a. Connect to Database
b. Enable GUC to get invisible rows
c. Run following SQL
select distinct Table_Analyzed from (select c.nspname ||'.'|| b.relname Table_Analyzed
from pg_stat_last_operation a, pg_class b ,pg_namespace c
where a.objid=b.oid and b.relnamespace = c.oid
and a.statime >= (select max(query_start) from pg_stat_activity where current_query='analyze;') and a.staactionname='ANALYZE'
and nspname not in ('gp_toolkit','pg_toast','pg_bitmapindex','pg_catalog','information_schema','pg_aoseg') order by a.statime desc) result;
- This SQL will work only when analyze process is still in progress during gpdbrestore
- Data will be visible only when GUC gp_select_invisible is on. Please enable this GUC at the session level and for this SQL only.
The SQL can be modified according to user requirement for additional attribute visibility.