This article is only relevant for v0.9.4 to v0.9.6.

Optimize Time Consuming Queries

This page shows how to optimize the database in order to reduce the time needed to execute 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.

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

  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.

    Lens Pod Shell
    Lens Pod Shell

  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.

    Lens deployment Edit
    Lens deployment Edit

  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

Last modified April 17, 2023: build: version 0.9.13 (6cb0a01)