OpenFn / OpenFn/lightning

Improve performance of the History page queries

Open
#981 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

architecture needs detail
Dominant language
Elixir
Stars
296
Forks
86
Avg merge
1d 13h
Merged PRs (30d)
50

Description

Coming off #968.

We have several places where performance of the History page query is hampered, here is a list of things I noticed:

  1. We should not cast columns when searching, for example both log_lines and the dataclip tables have their columns cast into varchars - this is very expensive.
  2. Finding the last run by joining on finished_at is exponentially less efficient depending on how many attempts & runs there are.

Proposed solutions

  • #1898
  • #1899
  • Add index to attempt_runs for attempt_id and/or run_id, currently the compound index doesn't appear to be used for these queries.

These solutions are distinct (and should be done regardless) from taking another approach which is creating aggregate tables

  1. Create aggregate (or columns) that get updated when an attempt/run is created or updated. Removing the need to join and filter Runs by exit code and finished_at.

References:

invocation.ex:417 - list_work_orders_for_project_query/2

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start at invocation.ex:417 and trace list_work_orders_for_project_query/2, then review the remaining unchecked work in #1899 and the proposed attempt_runs index. Done means the History page queries avoid the listed column casts and inefficient finished_at joins, with the query behavior and performance improvements verified against the stated cases.

Written by the indexing model from the issue text.

Assessment

Tech stack
elixir
Domain
backend, databases, performance
Issue type
Refactor
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.