Optimize Time Consuming Queries
2 minute read
When you have a large database, it is possible that some queries take a long time to execute. This especially shows when you are using Grafana and the dropdown menu in the datasource takes a long time to load or does not load at all.
Before you begin
You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.
Your United Manufacturing Hub must be at or later than version 0.9.4. To check the United Manufacturing Hub version, open UMHLens / OpenLens and go to Helm > Releases. The version is listed in the Version column.Open a shell in the database container
From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.
Click the Pod Shell button to open a shell in the container.
Enter the postgres shell:
psql
Connect to the database:
\c factoryinsight
Create an index
Indexes are used to speed up queries. Run this query to create an index on the
processvaluetable
table:
CREATE INDEX ON processvaluetable(valuename, asset_id) WITH (timescaledb.transaction_per_chunk);
Rollback factoryinsight
If you have already created an index, you can rollback the factoryinsight deployment to version 0.9.4. This way it will use a less optimized but faster query, significantly reducing the execution time.
- From the Deployments section in UMHLens / OpenLens, click on united-manufacturing-hub-factoryinsight-deployment to open the details page.
Click the Edit button to open the deployment’s configuration.
- Scroll down to the
spec.containers
section and change theimage
value to unitedmanufacturinghub/factoryinsight:0.9.4. - Click Save.