Skip to content

fix: optimize /api/task_runs/count endpoint performance#19548

Merged
zzstoatzz merged 2 commits intoPrefectHQ:mainfrom
nehaaprasad:fix/api-task
Nov 26, 2025
Merged

fix: optimize /api/task_runs/count endpoint performance#19548
zzstoatzz merged 2 commits intoPrefectHQ:mainfrom
nehaaprasad:fix/api-task

Conversation

@nehaaprasad
Copy link
Copy Markdown
Contributor

@nehaaprasad nehaaprasad commented Nov 26, 2025

fix: #19347

Overview

  • fixes slow /api/task_runs/count endpoint (10+ seconds) by replacing EXISTS subqueries with direct JOINs for complex filters. Maintains backward compatibility.

Files changed: src/prefect/server/models/task_runs.py

@github-actions github-actions bot added the bug Something isn't working label Nov 26, 2025
@codspeed-hq
Copy link
Copy Markdown

codspeed-hq bot commented Nov 26, 2025 •
edited
Loading

CodSpeed Performance Report

Merging #19548 will not alter performance

Comparing naaa760:fix/api-task (f0cbb62) with main (350b067)

Summary

✅ 2 untouched

@zzstoatzz
Copy link
Copy Markdown
Collaborator

zzstoatzz commented Nov 26, 2025

hi @naaa760

i've run the pre-commits against your PR to fix the static analysis CI

I've also tested this change against a large server instance (~780k task runs, ~140k flow runs) and it seems like a great improvement!

Filter Type Before (EXISTS) After (JOIN) Speedup
flow.name LIKE '%test%' 7,259ms 183ms ~40x
flow_run.state_type = 'COMPLETED' 2,394ms 630ms ~4x
query plan: EXISTS approach (before) - flow name filter 
 Aggregate  (cost=86744.48..86744.49 rows=1 width=8) (actual time=7258.659..7258.663 rows=1 loops=1)
   ->  Nested Loop  (cost=19566.04..86689.02 rows=22181 width=0) (actual time=7258.656..7258.659 rows=0 loops=1)
         ->  HashAggregate  (cost=19565.62..19605.75 rows=4013 width=16) (actual time=7185.902..7199.423 rows=46732 loops=1)
               Group Key: flow_run.id
               Batches: 1  Memory Usage: 4113kB
               ->  Nested Loop  (cost=297.77..19555.58 rows=4013 width=16) (actual time=13.965..7119.353 rows=46732 loops=1)
                     ->  Seq Scan on flow  (cost=0.00..1.44 rows=1 width=16) (actual time=0.014..0.026 rows=2 loops=1)
                           Filter: ((name)::text ~~ '%test%'::text)
                           Rows Removed by Filter: 39
                     ->  Bitmap Heap Scan on flow_run  (cost=297.77..19495.63 rows=5852 width=32) (actual time=7.035..3550.307 rows=23366 loops=2)
                           Recheck Cond: (flow_id = flow.id)
                           Heap Blocks: exact=21510
                           ->  Bitmap Index Scan on ix_flow_run__flow_id  (cost=0.00..296.31 rows=5852 width=0) (actual time=4.872..4.872 rows=24749 loops=2)
                                 Index Cond: (flow_id = flow.id)
         ->  Index Only Scan using ix_task_run__flow_run_id on task_run  (cost=0.42..15.09 rows=163 width=16) (actual time=0.001..0.001 rows=0 loops=46732)
               Index Cond: (flow_run_id = flow_run.id)
               Heap Fetches: 0
 Planning Time: 12.589 ms
 Execution Time: 7259.247 ms
query plan: JOIN approach (after) - flow name filter 
 Aggregate  (cost=86694.31..86694.32 rows=1 width=8) (actual time=182.940..182.943 rows=1 loops=1)
   ->  Nested Loop  (cost=298.20..86638.86 rows=22181 width=0) (actual time=182.935..182.937 rows=0 loops=1)
         ->  Nested Loop  (cost=297.77..19555.58 rows=4013 width=16) (actual time=15.709..125.761 rows=46736 loops=1)
               ->  Seq Scan on flow  (cost=0.00..1.44 rows=1 width=16) (actual time=0.018..0.031 rows=2 loops=1)
                     Filter: ((name)::text ~~ '%test%'::text)
                     Rows Removed by Filter: 39
               ->  Bitmap Heap Scan on flow_run  (cost=297.77..19495.63 rows=5852 width=32) (actual time=7.851..60.620 rows=23368 loops=2)
                     Recheck Cond: (flow_id = flow.id)
                     Heap Blocks: exact=21511
                     ->  Bitmap Index Scan on ix_flow_run__flow_id  (cost=0.00..296.31 rows=5852 width=0) (actual time=5.712..5.712 rows=24765 loops=2)
                           Index Cond: (flow_id = flow.id)
         ->  Index Only Scan using ix_task_run__flow_run_id on task_run  (cost=0.42..15.09 rows=163 width=16) (actual time=0.001..0.001 rows=0 loops=46736)
               Index Cond: (flow_run_id = flow_run.id)
               Heap Fetches: 0
 Planning Time: 6.238 ms
 Execution Time: 183.333 ms

thanks for the contribution!

@zzstoatzz zzstoatzz added the performance Related to an optimization or performance improvement label Nov 26, 2025
@zzstoatzz zzstoatzz merged commit a9d0a54 into PrefectHQ:main Nov 26, 2025
52 of 54 checks passed
saschwartz pushed a commit to saschwartz/prefect that referenced this pull request Nov 26, 2025
@nehaaprasad @zzstoatzz @claude
fix: optimize /api/task_runs/count endpoint performance (PrefectHQ#19548
707deec 
)

Co-authored-by: zzstoatzz <[email protected]>
Co-authored-by: Claude <[email protected]>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@zzstoatzz zzstoatzz zzstoatzz approved these changes

@cicdw cicdw Awaiting requested review from cicdw cicdw is a code owner

@desertaxle desertaxle Awaiting requested review from desertaxle desertaxle is a code owner

@chrisguidry chrisguidry Awaiting requested review from chrisguidry chrisguidry is a code owner

Assignees

No one assigned

Labels

bug Something isn't working performance Related to an optimization or performance improvement

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

Very slow endpoint api/task_runs/count

2 participants

@nehaaprasad @zzstoatzz