opening explore page "for the first time" can take >10s

[ltalirz]

E.g. when clicking on any of the AiiDA icons in https://dev-www.materialscloud.org/discover/curated-cofs#mcloudHeader
it can take a very long time to open the EXPLORE page for the structure (the page opens fast, but then the animated loading bar loads for a very long time).

This does not happen the second time I click on an icon.
It also does not happen when I open the page again afterwards in a different browser.

I'll try to catch this "in action" and provide a network trace using the "network" tab.

Any ideas as to what might be causing this would be welcome @giovannipizzi @waychal @asle85

[giovannipizzi]

We saw this also yesterday for a list of entries from explore, on dev-www.

From the browser, it seems the long-running request is the REST API that actually does a query.
My guess is that the DB is very slow (probably caches the query the first time, that's why it takes much less later).

My suggestion to debug is to put some print statements in the REST API to see if the slow part is indeed the query via the QueryBuilder, or something else around it.

[ltalirz]

hm... I also suspect it's the DB but it can't really be on the query level, since the queries should differ depending on the page shown (and after the first page, the others are fast).

It could be something like that the database generates an insane amount of indices that take a long time to load into memory and process?
We should certainly investigate things like the kombu table size as well