Slow DB select concept by uri

[rkorytkowski]

DB is reporting a lot of queries like this to be slow:
2021-11-05 11:00:11 UTC:10.1.11.104(36992):postgres@postgres:[3082]:LOG: duration: 628517.541 ms statement: SELECT "concepts"."id", "concepts"."public_access", "concepts"."created_at", "concepts"."updated_at", "concepts"."created_by_id", "concepts"."updated_by_id", "concepts"."is_active", "concepts"."extras", "concepts"."uri", "concepts"."version", "concepts"."released", "concepts"."retired", "concepts"."is_latest_version", "concepts"."name", "concepts"."full_name", "concepts"."default_locale", "concepts"."supported_locales", "concepts"."website", "concepts"."description", "concepts"."external_id", "concepts"."concept_class", "concepts"."datatype", "concepts"."comment", "concepts"."parent_id", "concepts"."versioned_object_id", "concepts"."mnemonic" FROM "concepts" WHERE ("concepts"."is_latest_version" AND "concepts"."uri"::text LIKE '%/orgs/Regenstrief-Institute/sources/LOINC/concepts/LP427294-6/%') ORDER BY "concepts"."id" ASC LIMIT 1

We need to fix finding by URI. I recall a similar issue was fixed in the past. Is this a regression? @snyaggarwal

[rkorytkowski]

I couldn't quickly track down where in code this query gets executed, but I manually enabled full-text search modules on staging: pg_trgm and btree_gist. I've also added index using those with:
CREATE INDEX concepts_uri_id_gin ON concepts USING gist (uri gist_trgm_ops, id) WHERE is_latest_version;

It fixed the query time to be a few hundred ms instead of minutes.

I consider it to be a temporary fix. We need to put these changes in code as well as try to find the DB query and fix it not to use LIKE if not needed.

[rkorytkowski]

The problematic query might be coming from process_hierarchy_for_concept_version...

[rkorytkowski]

Note that I needed to clear up queues on staging. Many of process_hierarchy_for_concept_version has not been completed. They blocked pretty much all exports. We need to be more careful with deployments that might affect normal operation. Especially long running tasks should be assigned to dedicated workers.

[snyaggarwal]

@rkorytkowski this query was used in populating hierarchy. I have changed the query to use exact match so now rather than LIKE it will use uri=.

[snyaggarwal]

@rkorytkowski Hierarchy processing async tasks are now moved to concurrent queue.

[rkorytkowski]

@snyaggarwal thanks! Could you please add a few lines of description on the hierarchy processing tasks. What are they doing and when are they executed?

[rkorytkowski]

Is there a way to enable DB extensions from django?

[snyaggarwal]

@rkorytkowski Yes you can create db extensions from django, its documented here

[rkorytkowski]

@snyaggarwal Thanks! Added in OpenConceptLab/oclapi2@904904a