Long running distinct queries

Problem

In version before 0.9.6, the index was only optimized to fetch distinct values, but not based on the asset_id: **CREATE** **INDEX** **ON** processValueTable **(**valuename**);**

When using the 0.9.4 command from factoryinsight to fetch the distinct valuenames for a specific asset, the resulting query was not optimized EXPLAIN SELECT DISTINCT(valuename) FROM processvaluetable WHERE asset_id=2;

Solution

Two steps:

1. Create Index

Execute:

CREATE INDEX ON processvaluetable(valuename, asset_id) WITH (timescaledb.transaction_per_chunk);

See also

Executing SQL commands in the United Manufacturing Hub?

2. use better query

Additionally, roll back the factoryinsight Deployment back to v0.9.4 as the optimized command using CTE from #1193 and v0.9.5 is taking longer, than the original command from v0.9.4

CTE version:

Naive distinct call: