review ui endpoints for N+1 queries

[rawwar]

Body

Similar to #57430

comment - #57430 (comment)

UI Endpoints:

• - GET /ui/auth/menus - I don't think so - Logs
• - GET /ui/next_run_assets/{dag_id} @pierrejeambrun Add number of queries guard for ui next_run_assets #58052
• - GET @pierrejeambrun /ui/config Add number of queries guard for ui config #57868
• - GET /ui/connections/hook_meta @pierrejeambrun Add number of queries guard for ui connections #57869
• - GET /ui/dags Fix n+1 query to fetch tags in the dags list page #57270
• - GET /ui/dags/{dag_id}/latest_run No list of items returned.
• - GET /ui/dependencies @pierrejeambrun Add number of queries guard for ui dependencies #57957
• - GET /ui/dashboard/historical_metrics_data @pierrejeambrun Add number of queries guard for ui dashboard #57956
• - GET /ui/dashboard/dag_stats @pierrejeambrun Add number of queries guard for ui dashboard #57956
• - GET /ui/structure/structure_data @pierrejeambrun Add number of queries guard for ui structure #58051
• - GET /ui/backfills @pierrejeambrun Add number of queries guard for ui backfill #57820
• - GET /ui/grid/structure/{dag_id} @pierrejeambrun Add number of queries guard for ui grid #57977
• - GET /ui/grid/runs/{dag_id} @pierrejeambrun Add number of queries guard for ui grid #57977
• - GET /ui/grid/ti_summaries/{dag_id}/{run_id} @pierrejeambrun Add number of queries guard for ui grid #57977
• - GET /ui/calendar/{dag_id} @pierrejeambrun Add number of queries guard for ui calendar #58044
• - GET /ui/teams @pierrejeambrun Add number of queries guard for ui team #57980

Committer

• I acknowledge that I am a maintainer/committer of the Apache Airflow project.

[rawwar]

ui endpoints have multiple endpoints. If anyone would like to contribute, please feel free to pick one of the endpoint and work on it.

I realised, I might not have enough time to work on this, if its planned to be included in 3.1.2.(Which has milestone of November 5th)

EDIT: Kaxil confirmed this can go in 3.1.3. So, I'll be working on this.

[KoviAnusha]

@rawwar I can give it a try. Can you assign this to me?

[rawwar]

@rawwar I can give it a try. Can you assign this to me?

This issue only the analysis to confirm if we have an issue. Please feel free to pick an UI endpoint, and post your analysis as a sub-issue.

[rawwar]

@rawwar I can give it a try. Can you assign this to me?

Please do let me know which endpoint you are looking at, so that we don't duplicate efforts

[KoviAnusha]

I am looking at GET /ui/auth/menus

As per my analysis,

/ui/auth/menus -> airflow-core/src/airflow/api_fastapi/core_api/routes/ui/auth.py→ get_auth_menus()

airflow-core/src/airflow/api_fastapi/auth/managers/base_auth_manager.py→ get_authorized_menu_items()

providers/fab/src/airflow/providers/fab/auth_manager/fab_auth_manager.py→ filter_authorized_menu_items()

providers/fab/src/airflow/providers/fab/auth_manager/fab_auth_manager.py→ _is_authorized()

providers/fab/src/airflow/providers/fab/auth_manager/models/init.py→ user.perms (QUERY)

User.perms property executes database query each time accessed. filter_authorized_menu_items() calls _is_authorized() for each menu item. Each menu item check accesses user.perms → triggers query. So N+1 query issue is there.

[rawwar]

@KoviAnusha , Can you share logs from api-server showing that N+1 queries being triggered?