Optimize Time Consuming Queries

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.

Open a shell in the database container

1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.

2. Click the Pod Shell button to open a shell in the container.

   

   

3. Enter the postgres shell:

   psql
   

4. 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.

1. From the Deployments section in UMHLens / OpenLens, click on united-manufacturing-hub-factoryinsight-deployment to open the details page.

2. Click the Edit button to open the deployment’s configuration.

   

   

3. Scroll down to the spec.containers section and change the image value to unitedmanufacturinghub/factoryinsight:0.9.4.
4. Click Save.

What’s next

• See Access the Database
• See Reduce Database Size