#487 Verifications query extremely inefficient Issue created by mkielar Pre issue-raising checklist I have already (please mark the applicable with an

x

): ☑︎ Upgraded to the latest Pact Broker OR ☑︎ Checked the CHANGELOG to see if the issue I am about to raise has been fixed ☐ Created an executable example that demonstrates the issue using either a: • Dockerfile • Git repository with a Travis or Appveyor (or similar) build Software versions • pact-broker gem version: ??? • pact-broker docker version:

2.81.0.1

• OS: We're using the docker version on ECS Fargate platform 1.4 with RDS Aurora Postgres running

r5.large

• pact broker client details:

pact-python 1.4.0

Expected behaviour SQL queries to be written in a more optimized way. See below. Actual behaviour We have an RDS Aurora Postgres cluster running a single

r5.large

instance of Postgres 12.4. We also have several microservices that are bound with API contracts which we use PACT for to verify. The verification process is scheduled - each PACT Provider has a scheduled Gitlab Pipeline that runs every 15 minutes and verifies the contracts. We have recently started to observe queries on

verifications

table start to take very long (over 15s per query) - so long, that our Nginx / ALBs / Clients are starting to timeout and our application deployment pipelines started failing. Inspecting PACT Broker database showed the follownig: • There are ~20 entries in

pact_versions

table • There are ~96000 entries in

verifications

table, and that table size is currently ~72MB in size The long query in question is:

SELECT "verifications".* 
  FROM "verifications" 
  LEFT JOIN (SELECT "verifications"."id", 
                    "verifications"."pact_version_id" 
               FROM "verifications" 
              WHERE ("verifications"."pact_version_id" = 7)) AS "v2" 
         ON (("verifications"."pact_version_id" = "v2"."pact_version_id") 
        AND  ("v2"."id" > "verifications"."id")) 
 WHERE (("verifications"."pact_version_id" = 7) 
   AND ("v2"."id" IS NULL))

and - as mentioned already - takes over 15s to complete. While I'm not sure in which context this query is being used, I can see it tries to select the

latest

verification for given

pact_version_id

. If I understand this correctly, this query does the following: 1. For each row in

verifications

table, that matches

pact_version_id=7

.... 2. ...check if there are any other

verifications

rows that match

pact_version_id=7

, but with a newer

id

... 3. ...and if so, skip this row. This produces a "Hash Anti Join" rule in execution plan, which is extremely costly. Then, if I undersand the intention correctly - it merely selects the latest verification for given

pact_version_id

. Thus, this query, could be rewritten into this:

SELECT "verifications".*
  FROM "verifications" 
 WHERE "verifications"."id" = (SELECT MAX(id) FROM "verifications" v2 WHERE "v2"."pact_version_id" = 7)

This new query returns the same results on our database, and is several orders of magnitude faster (on given 96k-of-records, it executed in ~500ms instead of over 15s). Steps to reproduce See actual behaviour. Relevant log files N/A Summary We're going to enable maintenance jobs as mentioned in https://docs.pact.io/pact_broker/administration/maintenance/ and https://docs.pact.io/pact_broker/docker_images/pactfoundation/#automatic-data-clean-up, and hope this will clean up unnecessary duplicates in

verifications

table, eventually decreasing execution times of this query. However, please consider optimizing it for better performance. pact-foundation/pact_broker