[Investigation] Understand latency of Admin list task executions

[EngHabu]

Describe the bug
LIst task executions is slow. We should figure out why and improve performance. If performance cannot be improved, then we need to figure out a better solution.

Expected behavior
This query should (we think) be faster than it is. It's taking about a second, which is much too slow.

SELECT
  task_executions.*
FROM
  task_executions
  LEFT JOIN tasks
    ON task_executions.project = tasks.project
    AND task_executions.domain = tasks.domain
    AND task_executions.name = tasks.name
    AND task_executions.version = tasks.version
  INNER JOIN node_executions
    ON task_executions.node_id = node_executions.node_id
    AND task_executions.execution_project = node_executions.execution_project
    AND task_executions.execution_domain = node_executions.execution_domain
    AND task_executions.execution_name = node_executions.execution_name
  INNER JOIN executions
    ON node_executions.execution_project = executions.execution_project
    AND node_executions.execution_domain = executions.execution_domain
    AND node_executions.execution_name = executions.execution_name
WHERE
  "task_executions"."deleted_at" IS NULL
  AND ((executions.execution_name = 'f939d0eea6e9c479981b')
  AND (executions.execution_project = 'ourgenesisteam')
  AND (executions.execution_domain = 'production')
  )

Anand and Katrina have already looked through the query plan and nothing looks amiss. All the right indices are being hit. Debugging this and understanding if we have a fundamental problem with our data model will require a deeper look than just going through the query plan.

Flyte component

• Overall
• Flyte Setup and Installation scripts
• Flyte Documentation
• Flyte communication (slack/email etc)
• FlytePropeller
• FlyteIDL (Flyte specification language)
• Flytekit (Python SDK)
• FlyteAdmin (Control Plane service)
• FlytePlugins
• DataCatalog
• FlyteStdlib (common libraries)
• FlyteConsole (UI)
• Other

To Reproduce
Steps to reproduce the behavior:

1. ...
2. ...

Screenshots
If applicable, add screenshots to help explain your problem.

Environment
Flyte component

• Sandbox (local or on one machine)
• Cloud hosted
  • AWS
  • GCP
  • Azure
• Baremetal
• Other

Additional context
NA

[EngHabu]

CC @wild-endeavor

[katrogan]

For context (thank you @anandswaminathan for the pointers) the list task executions for node query was querying on a subset of primary key fields (execution project, execution domain, execution name, node id) that were not indexed. Adding an index for this use case brought down query latency from the order of seconds to ~0.1 ms