To resolve this issue, add an index on the
originatoruri column for the
vmo_logevent table to dramatically speed up these queries.
- Ensure there is enough storage space on /storage/db partition
- Run the command to see available disk space:
df -h /storage/db
- Query the current size of the vmo_logevent table. The new index will be smaller than the entire table.
SELECT pg_size_pretty(pg_relation_size('vmo_logevent'::regclass));
- Log in to the VMware vRealize Automation virtual appliance using SSH.
- Launch PostgreSQL CLI
- cd /opt/vmware/vpostgres/current/bin
- su postgres
- psql vcac
- Run the following query to create the index for originatoruri in the vmo_logevent table.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_logeventoriginatoruri01 ON vmo_logevent (originatoruri);
Note: It is safe to create the index while vRealize Automation services are running, however it will take longer than if the services are stopped.
- Inspect the vmo_logevent table to confirm the index has been created
- \d vmo_logevent
- Confirm the new idx_logeventoriginatoruri index appears in the "Indexes:" section of the output
- \q