Create index on assay columns to improve query performance

LabKey Support Forum (Inactive)
Create index on assay columns to improve query performance alx dobre  2015-09-17 04:21
Status: Closed
 
Hello,

I am building queries that span across several assay tables. The queries are getting somewhat complex using 5 to 7 table joins.
These queries take up to 60 s to run with only a few hundred rows in the tables involved, they need to work for a few hundred thousand.

So I am looking for way to improve query performance.

Please note the queries are written within labkey.

I looked at the table inside the actual postgres database behind labkey that stores the assay data. I think it can be improved by adding some indexes.

The broader question is: How do you go about increasing the performance of labkey queries?

The specific question is: Is it a viable approach to go into the underlying postgres database and create indexes on the assay tables?
 
 
Jon (LabKey DevOps) responded:  2015-09-21 12:51
Hi Alex,

In some cases, queries can be optimized through refactoring the SQL code to be more efficient. In others, you would have to adjust the schema itself. This can mean changing the tables themselves (e.g. Having some of the data in lists or other data types instead of assays; although it's difficult to say without knowing the type of data we're dealing with). Otherwise, it means adding indices instead.

At the moment, we don't have a good way for adding custom indices for these kinds of administrator-defined data types like assays and datasets. It's possible to do it directly on the database itself, but we don't really support this either can can't guarantee an increase in performance consistently, but it will definitely do a little better than your standard labkey configuration that we can predict.

Regards,

Jon