Storage Policies slow to load for VMware Cloud Director

I had an issue recently with Storage Policies being extremely slow to load on VMware Cloud Director (10.5.1.1). This had a knock on effect for any other tasks that used Storage Polices, such as creating vApps/VMs or setting up VCDA replications. Storage Containers was also slow to load. I am happy to share the fix was quite simple.

With the help of Broadcom support I logged into the VCD primary cell and ran the following query which lists all the VCD storage policies:

sudo -u postgres psql vcloud -c "\timing" -c "select count(*) from dgp_view_pvdc_sp_metrics;"
Timing is on.
 count
-------
    79
(1 row)
Time: 5821.013 ms (00:05.821)

5 seconds is quite slow! We then proceeded to run an ANALYZE.

sudo -u postgres psql vcloud -c "ANALYZE;"
ANALYZE

Rerunning the original query the time had reduced from 5821 ms to 41 ms.

sudo -u postgres psql vcloud -c "\timing" -c "select count(*) from dgp_view_pvdc_sp_metrics;"
Timing is on.
 count
-------
    79
(1 row)
Time: 41.622 ms

So what just happened! The Postgres ANALYSE command once ran will actually collect the latest pg statistics. After that the query planner will find a new optimal and efficient way to run queries. See Postgres documentation.

The conclusion from Broadcom was although Cloud Director execute the Auto-Vacuum and the ANALYSE commands automatically, in some circumstances this will not occur and manually running the ANALYSE is required.

Broadcom has since added a KB 321690 to support this.