Monitor and fix slow Postgres queries

Created by: tsenart

Background

We currently don't monitor slow Postgres queries. While working #1404 (closed), it became apparent through observation of logs that we have quite a few low hanging fruits to speed things up for our users.

Slow queries

LOG: duration: 6019.831 ms execute 10543: SELECT name FROM repo WHERE enabled = true

This is probably slow because it returns a lot of results. But it should be validated if the right indexes are in place by EXPLAINing the query in the production database. If the plan looks ok, we can speed this up by ensuring that Postgres holds all the data in memory, which is easy to do with the right configuration.

• https://www.citusdata.com/blog/2018/06/12/configuring-work-mem-on-postgres/
• https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
• https://www.percona.com/blog/2018/08/31/tuning-postgresql-database-parameters-to-optimize-performance/

LOG:  duration: 1361.002 ms  execute 9974: SELECT id, name, description, language, enabled, created_at, updated_at, external_id, external_service_type, external_service_id FROM repo WHERE TRUE AND lower(name) ~* $1 AND enabled ORDER BY id ASC LIMIT $2 OFFSET $3

This seems like a very common repo lookup query.

LOG:  duration: 558.002 ms  execute 1610: 
 SELECT x.id, x.uuid, x.publisher_user_id, x.publisher_org_id, x.name, x.created_at, x.updated_at,
 CONCAT(COALESCE(users.username, orgs.name), '/', x.name) AS non_canonical_extension_id, COALESCE(users.username, orgs.name) AS non_canonical_publisher_name,
 (rer.manifest IS NULL OR COALESCE((rer.manifest->>'wip')::jsonb = 'true'::jsonb, rer.manifest->>'title' SIMILAR TO $1, false)) AS non_canonical_is_work_in_progress  	
 FROM registry_extensions x
 LEFT JOIN users ON users.id=publisher_user_id AND users.deleted_at IS NULL
 LEFT JOIN orgs ON orgs.id=publisher_org_id AND orgs.deleted_at IS NULL
 LEFT JOIN registry_extension_releases rer ON rer.registry_extension_id=x.id AND rer.deleted_at IS NULL
 WHERE ((CONCAT(COALESCE(users.username, orgs.name), '/', x.name) ILIKE $2 ) OR ( CASE WHEN rer.manifest IS NOT NULL THEN (rer.manifest->>'description' ILIKE $3 OR rer.manifest->>'title' ILIKE $4) ELSE false END))
 -- Join only to latest release from registry_extension_releases.
 
AND NOT EXISTS (SELECT 1 FROM registry_extension_releases rer2
WHERE rer.registry_extension_id=rer2.registry_extension_id
 AND rer2.deleted_at IS NULL
 AND rer2.created_at > rer.created_at
 ) 
AND x.deleted_at IS NULL 
ORDER BY CONCAT(COALESCE(users.username, orgs.name), '/', x.name) IN (NULL) ASC , TRUE,
-- Always sort WIP extensions last.
(rer.manifest IS NULL OR COALESCE((rer.manifest->>'wip')::jsonb = 'true'::jsonb, rer.manifest->>'title' SIMILAR TO $5, false)) ASC,
 x.id ASC

Not sure about this one.

Action items

• Setup monitoring and alerting of slow queries, or overall, of slow user requests above a threshold.
• Look into optimising these queries, prioritised by return on investment.