#2812 [Core feature] Explore FlyteAdmin slow SQL Issue created by katrogan Motivation: Why do you think this is important? A running Flyte deployment logs frequent slow SQL:

flyteadmin-794f779986-mqrs7 flyteadmin 
flyteadmin-794f779986-mqrs7 flyteadmin 2022/08/24 19:09:37 /go/src/github.com/flyteorg/flyteadmin/pkg/repositories/gormimpl/workflow_repo.go:79 SLOW SQL >= 200ms
flyteadmin-794f779986-mqrs7 flyteadmin [703.604ms] [rows:1] SELECT * FROM "workflows" WHERE project = 'flytesnacks' AND domain = 'development' AND name = 'core.control_flow.high_cpu_wf.single_integer_map_task' ORDER BY created_at desc LIMIT 1
flyteadmin-794f779986-mqrs7 flyteadmin 
flyteadmin-794f779986-mqrs7 flyteadmin 2022/08/24 19:09:37 /go/src/github.com/flyteorg/flyteadmin/pkg/repositories/gormimpl/workflow_repo.go:79 SLOW SQL >= 200ms
flyteadmin-794f779986-mqrs7 flyteadmin [720.675ms] [rows:1] SELECT * FROM "workflows" WHERE project = 'flytesnacks' AND domain = 'development' AND name = '<http://core.control_flow.dynamics.wf|core.control_flow.dynamics.wf>' ORDER BY created_at desc LIMIT 1
flyteadmin-794f779986-mqrs7 flyteadmin 
flyteadmin-794f779986-mqrs7 flyteadmin 2022/08/24 19:09:37 /go/src/github.com/flyteorg/flyteadmin/pkg/repositories/gormimpl/workflow_repo.go:79 SLOW SQL >= 200ms
flyteadmin-794f779986-mqrs7 flyteadmin [747.358ms] [rows:1] SELECT * FROM "workflows" WHERE project = 'flytesnacks' AND domain = 'development' AND name = 'core.control_flow.conditions.multiplier_2' ORDER BY created_at desc LIMIT 1
flyteadmin-794f779986-mqrs7 flyteadmin 
flyteadmin-794f779986-mqrs7 flyteadmin 2022/08/24 19:09:37 /go/src/github.com/flyteorg/flyteadmin/pkg/repositories/gormimpl/launch_plan_repo.go:133 SLOW SQL >= 200ms
flyteadmin-794f779986-mqrs7 flyteadmin [483.240ms] [rows:1] SELECT "launch_plans"."id","launch_plans"."created_at","launch_plans"."updated_at","launch_plans"."deleted_at","launch_plans"."project","launch_plans"."domain","launch_plans"."name","launch_plans"."version","launch_plans"."spec","launch_plans"."workflow_id","launch_plans"."closure","launch_plans"."state","launch_plans"."digest","launch_plans"."schedule_type" FROM "launch_plans" inner join workflows on launch_plans.workflow_id = workflows.id WHERE launch_plans.project = 'flytesnacks' AND launch_plans.domain = 'development' AND launch_plans.name = 'core.control_flow.chain_entities.chain_workflows_wf' LIMIT 1
flyteadmin-794f779986-mqrs7 flyteadmin 
flyteadmin-794f779986-mqrs7 flyteadmin 2022/08/24 19:09:37 /go/src/github.com/flyteorg/flyteadmin/pkg/repositories/gormimpl/launch_plan_repo.go:133 SLOW SQL >= 200ms
flyteadmin-794f779986-mqrs7 flyteadmin [485.314ms] [rows:1] SELECT "launch_plans"."id","launch_plans"."created_at","launch_plans"."updated_at","launch_plans"."deleted_at","launch_plans"."project","launch_plans"."domain","launch_plans"."name","launch_plans"."version","launch_plans"."spec","launch_plans"."workflow_id","launch_plans"."closure","launch_plans"."state","launch_plans"."digest","launch_plans"."schedule_type" FROM "launch_plans" inner join workflows on launch_plans.workflow_id = workflows.id WHERE launch_plans.project = 'flytesnacks' AND launch_plans.domain = 'development' AND launch_plans.name = 'core.control_flow.chain_tasks.chain_tasks_wf' LIMIT 1
flyteadmin-794f779986-mqrs7 flyteadmin 
flyteadmin-794f779986-mqrs7 flyteadmin 2022/08/24 19:09:37 /go/src/github.com/flyteorg/flyteadmin/pkg/repositories/gormimpl/launch_plan_repo.go:133 SLOW SQL >= 200ms
flyteadmin-794f779986-mqrs7 flyteadmin [406.597ms] [rows:1] SELECT "launch_plans"."id","launch_plans"."created_at","launch_plans"."updated_at","launch_plans"."deleted_at","launch_plans"."project","launch_plans"."domain","launch_plans"."name","launch_plans"."version","launch_plans"."spec","launch_plans"."workflow_id","launch_plans"."closure","launch_plans"."state","launch_plans"."digest","launch_plans"."schedule_type" FROM "launch_plans" inner join workflows on launch_plans.workflow_id = workflows.id WHERE launch_plans.project = 'flytesnacks' AND launch_plans.domain = 'development' AND launch_plans.name = '<http://control_flow.large_fanout_lp.wf|control_flow.large_fanout_lp.wf>' LIMIT 1

we should investigate indices or other performance optimizations to improve the query performance Goal: What should the final outcome look like, ideally? Fast queries to select workflows and launch plans Describe alternatives you've considered slower performance Propose: Link/Inline OR Additional context No response Are you sure this issue hasn't been raised already? ☑︎ Yes Have you read the Code of Conduct? ☑︎ Yes flyteorg/flyte

#2812 [Core feature] Explore FlyteAdmin slow SQL Issue closed as not planned by github-actions[bot] flyteorg/flyte