ehddnr301 commented on PR #74552:
URL: https://github.com/apache/airflow/pull/74552#issuecomment-6266861836
<!-- SPDX-License-Identifier: Apache-2.0
https://www.apache.org/licenses/LICENSE-2.0 -->
### UI API measurements
I called `/ui/dags` on 1m Dags with display-name ordering and 50 rows per
page. The local API request for the 49-character term took **981 ms**.
| Term | Count + page (ms) | Local API request (ms) |
|---|---:|---:|
| `cafe` | 113.587 | 151.428 |
| `카페` | 146.463 | 189.918 |
| 49-character term | 934.327 | 980.883 |
The earlier TEMP-table run took about 2 s for that term’s two DB queries.
Those scans were serial; all three searches here used `Parallel Seq Scan` with
two workers.
<details>
<summary>Setup and execution plans</summary>
PostgreSQL 16.15 / `en_US.utf8`, Airflow schema and stock indexes. Each term
matched 200 Dags and returned the first 50, ordered by display name and Dag ID.
Request parameters: `limit=50`, `offset=0`, `exclude_stale=true`,
`dag_runs_limit=1`.
One warmup, then three measured runs; the table shows medians. DB totals sum
the count and page query medians. Local `TestClient` timings include the
route’s other queries and serialization, without browser or network latency.
These are controlled test results.
Table layout, ordering and page size also changed from the TEMP benchmark,
so this comparison does not isolate parallel execution. The TEMP ASCII query
used a name index; the display-name CASE query scanned the full table for ASCII
too.
49-character term: `카페에서 매일 수집한 매출 데이터를 지역과 상품별로 정리하고 검증한 결과를 보고하는 카페`
#### `cafe`
**Total count**
```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) AS count_1
FROM (SELECT dag.dag_id AS dag_id, dag.is_paused AS is_paused,
dag.is_draining AS is_draining, dag.is_stale AS is_stale,
dag.exceeds_max_non_backfill AS exceeds_max_non_backfill, dag.last_parsed_time
AS last_parsed_time, dag.last_parse_duration AS last_parse_duration,
dag.last_expired AS last_expired, dag.fileloc AS fileloc, dag.relative_fileloc
AS relative_fileloc, dag.bundle_name AS bundle_name, dag.bundle_version AS
bundle_version, dag.owners AS owners, dag.dag_display_name AS dag_display_name,
dag.description AS description, dag.timetable_type AS timetable_type,
dag.timetable_summary AS timetable_summary, dag.timetable_description AS
timetable_description, dag.timetable_partitioned AS timetable_partitioned,
dag.timetable_periodic AS timetable_periodic, dag.timetable_asset_gated AS
timetable_asset_gated, dag.partition_mapper_info AS partition_mapper_info,
dag.asset_expression AS asset_expression, dag.deadline AS deadline,
dag.max_active_tasks AS max_active_tasks, dag.max_active
_runs AS max_active_runs, dag.max_consecutive_failed_dag_runs AS
max_consecutive_failed_dag_runs, dag.has_task_concurrency_limits AS
has_task_concurrency_limits, dag.has_import_errors AS has_import_errors,
dag.fail_fast AS fail_fast, dag.allowed_run_types AS allowed_run_types,
dag.next_dagrun AS next_dagrun, dag.next_dagrun_data_interval_start AS
next_dagrun_data_interval_start, dag.next_dagrun_data_interval_end AS
next_dagrun_data_interval_end, dag.next_dagrun_partition_key AS
next_dagrun_partition_key, dag.next_dagrun_partition_date AS
next_dagrun_partition_date, dag.next_dagrun_create_after AS
next_dagrun_create_after
FROM dag
WHERE dag.is_stale != true AND CASE WHEN (dag.dag_display_name IS NOT NULL)
THEN dag.dag_display_name ELSE dag.dag_id END >= 'cafe'::VARCHAR AND CASE WHEN
(dag.dag_display_name IS NOT NULL) THEN dag.dag_display_name ELSE dag.dag_id
END < 'caff'::VARCHAR AND substr(CASE WHEN (dag.dag_display_name IS NOT NULL)
THEN dag.dag_display_name ELSE dag.dag_id END, 1::INTEGER, 4::INTEGER) =
'cafe'::VARCHAR) AS anon_1;
```
```text
Finalize Aggregate (cost=29749.57..29749.58 rows=1 width=8) (actual
time=52.004..56.610 rows=1 loops=1)
Buffers: shared hit=16150 read=4266 written=96
-> Gather (cost=29749.36..29749.57 rows=2 width=8) (actual
time=51.939..56.606 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=16150 read=4266 written=96
-> Partial Aggregate (cost=28749.36..28749.37 rows=1 width=8)
(actual time=43.788..43.788 rows=1 loops=3)
Buffers: shared hit=16150 read=4266 written=96
-> Parallel Seq Scan on dag (cost=0.00..28749.33 rows=10
width=0) (actual time=0.964..43.781 rows=67 loops=3)
Filter: ((NOT is_stale) AND ((CASE WHEN
(dag_display_name IS NOT NULL) THEN dag_display_name ELSE dag_id END)::text >=
'cafe'::text) AND ((CASE WHEN (dag_display_name IS NOT NULL) THEN
dag_display_name ELSE dag_id END)::text < 'caff'::text) AND (substr((CASE WHEN
(dag_display_name IS NOT NULL) THEN dag_display_name ELSE dag_id END)::text, 1,
4) = 'cafe'::text))
Rows Removed by Filter: 333267
Buffers: shared hit=16150 read=4266 written=96
Planning Time: 0.061 ms
Execution Time: 56.624 ms
```
**Page**
```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT dag.dag_id, dag.is_paused, dag.is_draining, dag.is_stale,
dag.exceeds_max_non_backfill, dag.last_parsed_time, dag.last_parse_duration,
dag.last_expired, dag.fileloc, dag.relative_fileloc, dag.bundle_name,
dag.bundle_version, dag.owners, dag.dag_display_name, dag.description,
dag.timetable_type, dag.timetable_summary, dag.timetable_description,
dag.timetable_partitioned, dag.timetable_periodic, dag.timetable_asset_gated,
dag.partition_mapper_info, dag.asset_expression, dag.deadline,
dag.max_active_tasks, dag.max_active_runs, dag.max_consecutive_failed_dag_runs,
dag.has_task_concurrency_limits, dag.has_import_errors, dag.fail_fast,
dag.allowed_run_types, dag.next_dagrun, dag.next_dagrun_data_interval_start,
dag.next_dagrun_data_interval_end, dag.next_dagrun_partition_key,
dag.next_dagrun_partition_date, dag.next_dagrun_create_after
FROM dag
WHERE dag.is_stale != true AND CASE WHEN (dag.dag_display_name IS NOT NULL)
THEN dag.dag_display_name ELSE dag.dag_id END >= 'cafe'::VARCHAR AND CASE WHEN
(dag.dag_display_name IS NOT NULL) THEN dag.dag_display_name ELSE dag.dag_id
END < 'caff'::VARCHAR AND substr(CASE WHEN (dag.dag_display_name IS NOT NULL)
THEN dag.dag_display_name ELSE dag.dag_id END, 1::INTEGER, 4::INTEGER) =
'cafe'::VARCHAR ORDER BY CASE WHEN (dag.dag_display_name IS NOT NULL) THEN
dag.dag_display_name ELSE dag.dag_id END ASC, dag.dag_id ASC
LIMIT 50::INTEGER OFFSET 0::INTEGER;
```
```text
Limit (cost=29749.52..29751.86 rows=20 width=2318) (actual
time=52.228..56.700 rows=50 loops=1)
Buffers: shared hit=16242 read=4250 written=96
-> Gather Merge (cost=29749.52..29751.86 rows=20 width=2318) (actual
time=52.227..56.691 rows=50 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=16242 read=4250 written=96
-> Sort (cost=28749.50..28749.52 rows=10 width=2318) (actual
time=43.726..43.728 rows=45 loops=3)
Sort Key: (CASE WHEN (dag_display_name IS NOT NULL) THEN
dag_display_name ELSE dag_id END), dag_id
Sort Method: quicksort Memory: 45kB
Buffers: shared hit=16242 read=4250 written=96
Worker 0: Sort Method: quicksort Memory: 38kB
Worker 1: Sort Method: quicksort Memory: 42kB
-> Parallel Seq Scan on dag (cost=0.00..28749.33 rows=10
width=2318) (actual time=0.879..43.664 rows=67 loops=3)
Filter: ((NOT is_stale) AND ((CASE WHEN
(dag_display_name IS NOT NULL) THEN dag_display_name ELSE dag_id END)::text >=
'cafe'::text) AND ((CASE WHEN (dag_display_name IS NOT NULL) THEN
dag_display_name ELSE dag_id END)::text < 'caff'::text) AND (substr((CASE WHEN
(dag_display_name IS NOT NULL) THEN dag_display_name ELSE dag_id END)::text, 1,
4) = 'cafe'::text))
Rows Removed by Filter: 333267
Buffers: shared hit=16166 read=4250 written=96
Planning Time: 0.075 ms
Execution Time: 56.723 ms
```
#### `카페`
**Total count**
```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) AS count_1
FROM (SELECT dag.dag_id AS dag_id, dag.is_paused AS is_paused,
dag.is_draining AS is_draining, dag.is_stale AS is_stale,
dag.exceeds_max_non_backfill AS exceeds_max_non_backfill, dag.last_parsed_time
AS last_parsed_time, dag.last_parse_duration AS last_parse_duration,
dag.last_expired AS last_expired, dag.fileloc AS fileloc, dag.relative_fileloc
AS relative_fileloc, dag.bundle_name AS bundle_name, dag.bundle_version AS
bundle_version, dag.owners AS owners, dag.dag_display_name AS dag_display_name,
dag.description AS description, dag.timetable_type AS timetable_type,
dag.timetable_summary AS timetable_summary, dag.timetable_description AS
timetable_description, dag.timetable_partitioned AS timetable_partitioned,
dag.timetable_periodic AS timetable_periodic, dag.timetable_asset_gated AS
timetable_asset_gated, dag.partition_mapper_info AS partition_mapper_info,
dag.asset_expression AS asset_expression, dag.deadline AS deadline,
dag.max_active_tasks AS max_active_tasks, dag.max_active
_runs AS max_active_runs, dag.max_consecutive_failed_dag_runs AS
max_consecutive_failed_dag_runs, dag.has_task_concurrency_limits AS
has_task_concurrency_limits, dag.has_import_errors AS has_import_errors,
dag.fail_fast AS fail_fast, dag.allowed_run_types AS allowed_run_types,
dag.next_dagrun AS next_dagrun, dag.next_dagrun_data_interval_start AS
next_dagrun_data_interval_start, dag.next_dagrun_data_interval_end AS
next_dagrun_data_interval_end, dag.next_dagrun_partition_key AS
next_dagrun_partition_key, dag.next_dagrun_partition_date AS
next_dagrun_partition_date, dag.next_dagrun_create_after AS
next_dagrun_create_after
FROM dag
WHERE dag.is_stale != true AND CASE WHEN (dag.dag_display_name IS NOT NULL)
THEN dag.dag_display_name ELSE dag.dag_id END >= '카페'::VARCHAR AND substr(CASE
WHEN (dag.dag_display_name IS NOT NULL) THEN dag.dag_display_name ELSE
dag.dag_id END, 1::INTEGER, 2::INTEGER) = '카페'::VARCHAR) AS anon_1;
```
```text
Finalize Aggregate (cost=28709.62..28709.63 rows=1 width=8) (actual
time=67.633..72.252 rows=1 loops=1)
Buffers: shared hit=16150 read=4266 written=93
-> Gather (cost=28709.40..28709.61 rows=2 width=8) (actual
time=67.575..72.248 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=16150 read=4266 written=93
-> Partial Aggregate (cost=27709.40..27709.41 rows=1 width=8)
(actual time=59.295..59.296 rows=1 loops=3)
Buffers: shared hit=16150 read=4266 written=93
-> Parallel Seq Scan on dag (cost=0.00..27707.67 rows=695
width=0) (actual time=1.137..59.288 rows=67 loops=3)
Filter: ((NOT is_stale) AND ((CASE WHEN
(dag_display_name IS NOT NULL) THEN dag_display_name ELSE dag_id END)::text >=
'카페'::text) AND (substr((CASE WHEN (dag_display_name IS NOT NULL) THEN
dag_display_name ELSE dag_id END)::text, 1, 2) = '카페'::text))
Rows Removed by Filter: 333267
Buffers: shared hit=16150 read=4266 written=93
Planning Time: 0.067 ms
Execution Time: 72.267 ms
```
**Page**
```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT dag.dag_id, dag.is_paused, dag.is_draining, dag.is_stale,
dag.exceeds_max_non_backfill, dag.last_parsed_time, dag.last_parse_duration,
dag.last_expired, dag.fileloc, dag.relative_fileloc, dag.bundle_name,
dag.bundle_version, dag.owners, dag.dag_display_name, dag.description,
dag.timetable_type, dag.timetable_summary, dag.timetable_description,
dag.timetable_partitioned, dag.timetable_periodic, dag.timetable_asset_gated,
dag.partition_mapper_info, dag.asset_expression, dag.deadline,
dag.max_active_tasks, dag.max_active_runs, dag.max_consecutive_failed_dag_runs,
dag.has_task_concurrency_limits, dag.has_import_errors, dag.fail_fast,
dag.allowed_run_types, dag.next_dagrun, dag.next_dagrun_data_interval_start,
dag.next_dagrun_data_interval_end, dag.next_dagrun_partition_key,
dag.next_dagrun_partition_date, dag.next_dagrun_create_after
FROM dag
WHERE dag.is_stale != true AND CASE WHEN (dag.dag_display_name IS NOT NULL)
THEN dag.dag_display_name ELSE dag.dag_id END >= '카페'::VARCHAR AND substr(CASE
WHEN (dag.dag_display_name IS NOT NULL) THEN dag.dag_display_name ELSE
dag.dag_id END, 1::INTEGER, 2::INTEGER) = '카페'::VARCHAR ORDER BY CASE WHEN
(dag.dag_display_name IS NOT NULL) THEN dag.dag_display_name ELSE dag.dag_id
END ASC, dag.dag_id ASC
LIMIT 50::INTEGER OFFSET 0::INTEGER;
```
```text
Limit (cost=28730.78..28736.61 rows=50 width=2318) (actual
time=68.425..72.855 rows=50 loops=1)
Buffers: shared hit=16242 read=4250 written=96
-> Gather Merge (cost=28730.78..28892.96 rows=1390 width=2318) (actual
time=68.424..72.851 rows=50 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=16242 read=4250 written=96
-> Sort (cost=27730.75..27732.49 rows=695 width=2318) (actual
time=59.930..59.932 rows=44 loops=3)
Sort Key: (CASE WHEN (dag_display_name IS NOT NULL) THEN
dag_display_name ELSE dag_id END), dag_id
Sort Method: quicksort Memory: 90kB
Buffers: shared hit=16242 read=4250 written=96
Worker 0: Sort Method: quicksort Memory: 65kB
Worker 1: Sort Method: quicksort Memory: 70kB
-> Parallel Seq Scan on dag (cost=0.00..27707.67 rows=695
width=2318) (actual time=0.430..59.536 rows=67 loops=3)
Filter: ((NOT is_stale) AND ((CASE WHEN
(dag_display_name IS NOT NULL) THEN dag_display_name ELSE dag_id END)::text >=
'카페'::text) AND (substr((CASE WHEN (dag_display_name IS NOT NULL) THEN
dag_display_name ELSE dag_id END)::text, 1, 2) = '카페'::text))
Rows Removed by Filter: 333267
Buffers: shared hit=16166 read=4250 written=96
Planning Time: 0.073 ms
Execution Time: 72.877 ms
```
#### 49-character term
**Total count**
```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) AS count_1
FROM (SELECT dag.dag_id AS dag_id, dag.is_paused AS is_paused,
dag.is_draining AS is_draining, dag.is_stale AS is_stale,
dag.exceeds_max_non_backfill AS exceeds_max_non_backfill, dag.last_parsed_time
AS last_parsed_time, dag.last_parse_duration AS last_parse_duration,
dag.last_expired AS last_expired, dag.fileloc AS fileloc, dag.relative_fileloc
AS relative_fileloc, dag.bundle_name AS bundle_name, dag.bundle_version AS
bundle_version, dag.owners AS owners, dag.dag_display_name AS dag_display_name,
dag.description AS description, dag.timetable_type AS timetable_type,
dag.timetable_summary AS timetable_summary, dag.timetable_description AS
timetable_description, dag.timetable_partitioned AS timetable_partitioned,
dag.timetable_periodic AS timetable_periodic, dag.timetable_asset_gated AS
timetable_asset_gated, dag.partition_mapper_info AS partition_mapper_info,
dag.asset_expression AS asset_expression, dag.deadline AS deadline,
dag.max_active_tasks AS max_active_tasks, dag.max_active
_runs AS max_active_runs, dag.max_consecutive_failed_dag_runs AS
max_consecutive_failed_dag_runs, dag.has_task_concurrency_limits AS
has_task_concurrency_limits, dag.has_import_errors AS has_import_errors,
dag.fail_fast AS fail_fast, dag.allowed_run_types AS allowed_run_types,
dag.next_dagrun AS next_dagrun, dag.next_dagrun_data_interval_start AS
next_dagrun_data_interval_start, dag.next_dagrun_data_interval_end AS
next_dagrun_data_interval_end, dag.next_dagrun_partition_key AS
next_dagrun_partition_key, dag.next_dagrun_partition_date AS
next_dagrun_partition_date, dag.next_dagrun_create_after AS
next_dagrun_create_after
FROM dag
WHERE dag.is_stale != true AND CASE WHEN (dag.dag_display_name IS NOT NULL)
THEN dag.dag_display_name ELSE dag.dag_id END >= '카페에서 매일 수집한 매출 데이터를 지역과 상품별로
정리하고 검증한 결과를 보고하는 카페'::VARCHAR AND substr(CASE WHEN (dag.dag_display_name IS
NOT NULL) THEN dag.dag_display_name ELSE dag.dag_id END, 1::INTEGER,
49::INTEGER) = '카페에서 매일 수집한 매출 데이터를 지역과 상품별로 정리하고 검증한 결과를 보고하는 카페'::VARCHAR) AS
anon_1;
```
```text
Finalize Aggregate (cost=28709.62..28709.63 rows=1 width=8) (actual
time=461.479..466.377 rows=1 loops=1)
Buffers: shared hit=16017 read=4399 written=2
-> Gather (cost=28709.40..28709.61 rows=2 width=8) (actual
time=461.424..466.372 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=16017 read=4399 written=2
-> Partial Aggregate (cost=27709.40..27709.41 rows=1 width=8)
(actual time=453.668..453.669 rows=1 loops=3)
Buffers: shared hit=16017 read=4399 written=2
-> Parallel Seq Scan on dag (cost=0.00..27707.67 rows=695
width=0) (actual time=8.353..453.657 rows=67 loops=3)
Filter: ((NOT is_stale) AND ((CASE WHEN
(dag_display_name IS NOT NULL) THEN dag_display_name ELSE dag_id END)::text >=
'카페에서 매일 수집한 매출 데이터를 지역과 상품별로 정리하고 검증한 결과를 보고하는 카페'::text) AND (substr((CASE
WHEN (dag_display_name IS NOT NULL) THEN dag_display_name ELSE dag_id
END)::text, 1, 49) = '카페에서 매일 수집한 매출 데이터를 지역과 상품별로 정리하고 검증한 결과를 보고하는 카페'::text))
Rows Removed by Filter: 333267
Buffers: shared hit=16017 read=4399 written=2
Planning Time: 0.071 ms
Execution Time: 466.394 ms
```
**Page**
```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT dag.dag_id, dag.is_paused, dag.is_draining, dag.is_stale,
dag.exceeds_max_non_backfill, dag.last_parsed_time, dag.last_parse_duration,
dag.last_expired, dag.fileloc, dag.relative_fileloc, dag.bundle_name,
dag.bundle_version, dag.owners, dag.dag_display_name, dag.description,
dag.timetable_type, dag.timetable_summary, dag.timetable_description,
dag.timetable_partitioned, dag.timetable_periodic, dag.timetable_asset_gated,
dag.partition_mapper_info, dag.asset_expression, dag.deadline,
dag.max_active_tasks, dag.max_active_runs, dag.max_consecutive_failed_dag_runs,
dag.has_task_concurrency_limits, dag.has_import_errors, dag.fail_fast,
dag.allowed_run_types, dag.next_dagrun, dag.next_dagrun_data_interval_start,
dag.next_dagrun_data_interval_end, dag.next_dagrun_partition_key,
dag.next_dagrun_partition_date, dag.next_dagrun_create_after
FROM dag
WHERE dag.is_stale != true AND CASE WHEN (dag.dag_display_name IS NOT NULL)
THEN dag.dag_display_name ELSE dag.dag_id END >= '카페에서 매일 수집한 매출 데이터를 지역과 상품별로
정리하고 검증한 결과를 보고하는 카페'::VARCHAR AND substr(CASE WHEN (dag.dag_display_name IS
NOT NULL) THEN dag.dag_display_name ELSE dag.dag_id END, 1::INTEGER,
49::INTEGER) = '카페에서 매일 수집한 매출 데이터를 지역과 상품별로 정리하고 검증한 결과를 보고하는 카페'::VARCHAR
ORDER BY CASE WHEN (dag.dag_display_name IS NOT NULL) THEN dag.dag_display_name
ELSE dag.dag_id END ASC, dag.dag_id ASC
LIMIT 50::INTEGER OFFSET 0::INTEGER;
```
```text
Limit (cost=28730.78..28736.61 rows=50 width=2318) (actual
time=460.816..465.481 rows=50 loops=1)
Buffers: shared hit=16261 read=4231 written=1
-> Gather Merge (cost=28730.78..28892.96 rows=1390 width=2318) (actual
time=460.815..465.477 rows=50 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=16261 read=4231 written=1
-> Sort (cost=27730.75..27732.49 rows=695 width=2318) (actual
time=452.327..452.328 rows=38 loops=3)
Sort Key: (CASE WHEN (dag_display_name IS NOT NULL) THEN
dag_display_name ELSE dag_id END), dag_id
Sort Method: quicksort Memory: 82kB
Buffers: shared hit=16261 read=4231 written=1
Worker 0: Sort Method: quicksort Memory: 67kB
Worker 1: Sort Method: quicksort Memory: 76kB
-> Parallel Seq Scan on dag (cost=0.00..27707.67 rows=695
width=2318) (actual time=2.209..451.915 rows=67 loops=3)
Filter: ((NOT is_stale) AND ((CASE WHEN
(dag_display_name IS NOT NULL) THEN dag_display_name ELSE dag_id END)::text >=
'카페에서 매일 수집한 매출 데이터를 지역과 상품별로 정리하고 검증한 결과를 보고하는 카페'::text) AND (substr((CASE
WHEN (dag_display_name IS NOT NULL) THEN dag_display_name ELSE dag_id
END)::text, 1, 49) = '카페에서 매일 수집한 매출 데이터를 지역과 상품별로 정리하고 검증한 결과를 보고하는 카페'::text))
Rows Removed by Filter: 333267
Buffers: shared hit=16185 read=4231 written=1
Planning Time: 0.085 ms
Execution Time: 465.506 ms
```
</details>
---
Drafted-by: Codex (GPT-6.1), Claude (5.5); reviewed by ehddnr301 before
posting
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
