[DOP-26672] Hint number of rows returned by fetch_bulk queries by dolfinus · Pull Request #497 · MTSWebServices/data-rentgen

dolfinus · 2026-07-15T12:31:55Z

Change Summary

Without limit:

explain analyze select * from run where id = any('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-5aa5-7410-8523-7c5012f84d43}'::uuid[]) order by id desc, created_at desc;
                                                                           QUERY PLAN                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=41.37..41.42 rows=21 width=510) (actual time=0.132..0.135 rows=2 loops=1)
   Sort Key: run.id DESC, run.created_at DESC
   Sort Method: quicksort  Memory: 25kB
   ->  Append  (cost=0.00..40.90 rows=21 width=510) (actual time=0.114..0.124 rows=2 loops=1)
         ->  Seq Scan on run_y2020 run_1  (cost=0.00..0.00 rows=1 width=508) (actual time=0.007..0.008 rows=0 loops=1)
               Filter: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-5aa5-7410-8523-7c5012f84d43}'::uuid[]))
         ->  Seq Scan on run_y2021 run_2  (cost=0.00..0.00 rows=1 width=508) (actual time=0.002..0.002 rows=0 loops=1)
               Filter: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-5aa5-7410-8523-7c5012f84d43}'::uuid[]))
         ->  Seq Scan on run_y2022 run_3  (cost=0.00..0.00 rows=1 width=538) (actual time=0.002..0.002 rows=0 loops=1)
               Filter: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-5aa5-7410-8523-7c5012f84d43}'::uuid[]))
         ->  Seq Scan on run_y2023 run_4  (cost=0.00..0.00 rows=1 width=537) (actual time=0.001..0.001 rows=0 loops=1)
               Filter: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-5aa5-7410-8523-7c5012f84d43}'::uuid[]))
         ->  Seq Scan on run_y2024 run_5  (cost=0.00..0.00 rows=1 width=537) (actual time=0.001..0.002 rows=0 loops=1)
               Filter: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-5aa5-7410-8523-7c5012f84d43}'::uuid[]))
         ->  Seq Scan on run_y2025 run_6  (cost=0.00..2.21 rows=2 width=505) (actual time=0.013..0.013 rows=0 loops=1)
               Filter: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-5aa5-7410-8523-7c5012f84d43}'::uuid[]))
               Rows Removed by Filter: 17
         ->  Seq Scan on run_y2026_m01 run_7  (cost=0.00..0.00 rows=1 width=525) (actual time=0.001..0.002 rows=0 loops=1)
               Filter: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-5aa5-7410-8523-7c5012f84d43}'::uuid[]))
         ->  Index Scan Backward using run_y2026_m02_pkey on run_y2026_m02 run_8  (cost=0.43..6.07 rows=2 width=493) (actual time=0.018..0.018 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-5aa5-7410-8523-7c5012f84d43}'::uuid[]))
         ->  Index Scan Backward using run_y2026_m03_pkey on run_y2026_m03 run_9  (cost=0.43..6.21 rows=2 width=500) (actual time=0.010..0.010 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-5aa5-7410-8523-7c5012f84d43}'::uuid[]))
         ->  Index Scan Backward using run_y2026_m04_pkey on run_y2026_m04 run_10  (cost=0.43..6.66 rows=2 width=492) (actual time=0.010..0.010 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-5aa5-7410-8523-7c5012f84d43}'::uuid[]))
         ->  Index Scan Backward using run_y2026_m05_pkey on run_y2026_m05 run_11  (cost=0.43..6.70 rows=2 width=502) (actual time=0.010..0.010 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-5aa5-7410-8523-7c5012f84d43}'::uuid[]))
         ->  Index Scan Backward using run_y2026_m06_pkey on run_y2026_m06 run_12  (cost=0.43..6.89 rows=2 width=526) (actual time=0.010..0.010 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-5aa5-7410-8523-7c5012f84d43}'::uuid[]))
         ->  Index Scan Backward using run_y2026_m07_pkey on run_y2026_m07 run_13  (cost=0.42..6.06 rows=2 width=522) (actual time=0.026..0.030 rows=2 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-5aa5-7410-8523-7c5012f84d43}'::uuid[]))
         ->  Seq Scan on run_y2026_m08 run_14  (cost=0.00..0.00 rows=1 width=478) (actual time=0.002..0.002 rows=0 loops=1)
               Filter: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-5aa5-7410-8523-7c5012f84d43}'::uuid[]))
 Planning Time: 0.865 ms
 Execution Time: 0.231 ms

With explicit limit:

explain analyze select * from run where id = any('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-614c-7cae-a98f-8b2a4d5f119f}'::uuid[]) order by id desc, created_at desc limit 2;
                                                                           QUERY PLAN                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3.84..9.62 rows=2 width=510) (actual time=0.130..0.134 rows=2 loops=1)
   ->  Merge Append  (cost=3.84..64.49 rows=21 width=510) (actual time=0.129..0.133 rows=2 loops=1)
         Sort Key: run.id DESC, run.created_at DESC
         ->  Index Scan Backward using run_y2020_pkey on run_y2020 run_1  (cost=0.12..2.61 rows=1 width=508) (actual time=0.005..0.005 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-614c-7cae-a98f-8b2a4d5f119f}'::uuid[]))
         ->  Index Scan Backward using run_y2021_pkey on run_y2021 run_2  (cost=0.12..2.53 rows=1 width=508) (actual time=0.003..0.003 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-614c-7cae-a98f-8b2a4d5f119f}'::uuid[]))
         ->  Index Scan Backward using run_y2022_pkey on run_y2022 run_3  (cost=0.12..2.77 rows=1 width=538) (actual time=0.002..0.002 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-614c-7cae-a98f-8b2a4d5f119f}'::uuid[]))
         ->  Index Scan Backward using run_y2023_pkey on run_y2023 run_4  (cost=0.12..2.50 rows=1 width=537) (actual time=0.002..0.002 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-614c-7cae-a98f-8b2a4d5f119f}'::uuid[]))
         ->  Index Scan Backward using run_y2024_pkey on run_y2024 run_5  (cost=0.12..2.45 rows=1 width=537) (actual time=0.003..0.003 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-614c-7cae-a98f-8b2a4d5f119f}'::uuid[]))
         ->  Index Scan Backward using run_y2025_pkey on run_y2025 run_6  (cost=0.14..5.85 rows=2 width=505) (actual time=0.004..0.004 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-614c-7cae-a98f-8b2a4d5f119f}'::uuid[]))
         ->  Index Scan Backward using run_y2026_m01_pkey on run_y2026_m01 run_7  (cost=0.12..3.14 rows=1 width=525) (actual time=0.002..0.002 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-614c-7cae-a98f-8b2a4d5f119f}'::uuid[]))
         ->  Index Scan Backward using run_y2026_m02_pkey on run_y2026_m02 run_8  (cost=0.43..6.07 rows=2 width=493) (actual time=0.019..0.019 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-614c-7cae-a98f-8b2a4d5f119f}'::uuid[]))
         ->  Index Scan Backward using run_y2026_m03_pkey on run_y2026_m03 run_9  (cost=0.43..6.21 rows=2 width=500) (actual time=0.015..0.015 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-614c-7cae-a98f-8b2a4d5f119f}'::uuid[]))
         ->  Index Scan Backward using run_y2026_m04_pkey on run_y2026_m04 run_10  (cost=0.43..6.66 rows=2 width=492) (actual time=0.010..0.010 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-614c-7cae-a98f-8b2a4d5f119f}'::uuid[]))
         ->  Index Scan Backward using run_y2026_m05_pkey on run_y2026_m05 run_11  (cost=0.43..6.70 rows=2 width=502) (actual time=0.016..0.016 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-614c-7cae-a98f-8b2a4d5f119f}'::uuid[]))
         ->  Index Scan Backward using run_y2026_m06_pkey on run_y2026_m06 run_12  (cost=0.43..6.89 rows=2 width=526) (actual time=0.013..0.013 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-614c-7cae-a98f-8b2a4d5f119f}'::uuid[]))
         ->  Index Scan Backward using run_y2026_m07_pkey on run_y2026_m07 run_13  (cost=0.42..6.06 rows=2 width=522) (actual time=0.026..0.029 rows=2 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-614c-7cae-a98f-8b2a4d5f119f}'::uuid[]))
         ->  Index Scan Backward using run_y2026_m08_pkey on run_y2026_m08 run_14  (cost=0.12..3.27 rows=1 width=478) (actual time=0.006..0.006 rows=0 loops=1)
               Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99,019f65b9-614c-7cae-a98f-8b2a4d5f119f}'::uuid[]))
 Planning Time: 0.799 ms
 Execution Time: 0.206 ms

This doesn't make queries faster, but replaces seq scans with index scans.

Related issue number

Checklist

Commit message and PR title is comprehensive
Keep the change as small as possible
Unit and integration tests for the changes exist
Tests pass on CI and coverage does not decrease
Documentation reflects the changes where applicable
mddocs/docs/changelog/next_release/<pull request or issue id>.<change type>.md file added describing change
(see CONTRIBUTING.rst for details.)
My PR is ready to review.

github-actions · 2026-07-15T12:37:24Z

Coverage Report •

File	Stmts	Miss	Branch	BrPart	Cover	Missing
data_rentgen/db/repositories
dataset.py	77	0	20	1	99%	117->119
job.py	99	0	32	1	99%	251->253
operation.py	50	2	14	3	92%	123->133, 133–134
run.py	124	29	48	13	76%	185->191, 282–283, 285–286, 288, 290, 298, 301–302, 304–305, 314–315, 318–321, 324–325, 328, 345–347, 355, 374–376, 378–379
user.py	32	1	4	0	97%	73
TOTAL	7710	518	1326	216	92%

[DOP-26672] Hint number of rows returned by fetch_bulk queries

1865a48

dolfinus force-pushed the improvement/DOP-26672-bulk-limit branch from c6481b5 to 1865a48 Compare July 15, 2026 12:33

dolfinus self-assigned this Jul 15, 2026

dolfinus force-pushed the improvement/DOP-26672-bulk-limit branch 2 times, most recently from d5b4c7a to 1865a48 Compare July 15, 2026 12:54

dolfinus marked this pull request as ready for review July 15, 2026 13:00

dolfinus requested review from IlyasDevelopment and TiGrib July 15, 2026 13:00

IlyasDevelopment approved these changes Jul 15, 2026

View reviewed changes

dolfinus merged commit b5b54ac into develop Jul 15, 2026
18 checks passed

dolfinus deleted the improvement/DOP-26672-bulk-limit branch July 15, 2026 16:27

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

[DOP-26672] Hint number of rows returned by fetch_bulk queries#497

[DOP-26672] Hint number of rows returned by fetch_bulk queries#497
dolfinus merged 1 commit into
developfrom
improvement/DOP-26672-bulk-limit

dolfinus commented Jul 15, 2026 •

edited

Loading

Uh oh!

github-actions Bot commented Jul 15, 2026 •

edited

Loading

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Uh oh!

Conversation

dolfinus commented Jul 15, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Change Summary

Related issue number

Checklist

Uh oh!

github-actions Bot commented Jul 15, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

dolfinus commented Jul 15, 2026 •

edited

Loading

github-actions Bot commented Jul 15, 2026 •

edited

Loading