Add composite index for executions

[pablocasares]

Why are the changes needed?

We are experiencing high load on query:
SELECT * FROM "executions" WHERE executions.execution_project = $1 AND executions.execution_domain = $2 AND executions.phase in ($3) AND executions.execution_created_at >= $4 AND executions.execution_created_at <= $5 AND executions.state = $6 LIMIT $7

It takes 95% of the db load. In our setup it is roughly called ~800k times per hour. The average execution time for the query is 13ms. It sometimes hits the pkey index but some other times it has to do a scan taking around 150ms.

We added a composite index on execution_project, execution_domain, phase, execution_created_at and state and the avg execution time went down to 0.01ms.

That query was also taking most of our memory as most shared cache hits were done by it. After adding the change the query went from ~180k/s shared block accessed to ~200/s (roughly a factor of 1000). This is also helping to use the memory for other queries.

What changes were proposed in this pull request?

This change adds a composite index in executions table to avoid doing scans.

How was this patch tested?

The index was manually created in the db.

Setup process

Screenshots

Load by time (first row is the mentioned query)

Check all the applicable boxes

• I updated the documentation accordingly.
• All new and existing tests passed.
• All commits are signed-off.

Related PRs

Docs link

[welcome]

Thank you for opening this pull request! 🙌

These tips will help get your PR across the finish line:

• Most of the repos have a PR template; if not, fill it out to the best of your knowledge.
• Sign off your commits (Reference: DCO Guide).

[katrogan]

hi @pablocasares thanks for this change, I'm curious if you're running an out of band process that generates these queries? from inspecting the console I don't see may queries issued to the execution table that filter on phase (but do see that they query on created_at DESC)

[codecov]

Codecov Report

All modified and coverable lines are covered by tests ✅

Project coverage is 59.76%. Comparing base (1b3a5cc) to head (a7d1658).
Report is 1044 commits behind head on master.

Additional details and impacted files

@@            Coverage Diff             @@
##           master    #4872      +/-   ##
==========================================
+ Coverage   58.98%   59.76%   +0.77%     
==========================================
  Files         645      463     -182     
  Lines       55162    37276   -17886     
==========================================
- Hits        32539    22279   -10260     
+ Misses      20048    13325    -6723     
+ Partials     2575     1672     -903     

Flag	Coverage Δ
unittests	59.76% <ø> (+0.77%)	⬆️

Flags with carried forward coverage won't be shown. Click here to find out more.

☔ View full report in Codecov by Sentry.
📢 Have feedback on the report? Share it here.

[eapolinario]

Cleaning stale PRs. Please reopen if you wan to discuss this further.