[DOP-26672] Optimize paginate queries with id filters by dolfinus · Pull Request #499 · MTSWebServices/data-rentgen

dolfinus · 2026-07-15T13:00:25Z

Change Summary

Calling GET /v1/runs?run_id=019f65b9-9925-709b-bbd2-a2fb181b7c99 performs query which looks like this:

select * from run where id = any('{019f65b9-9925-709b-bbd2-a2fb181b7c99}'::uuid[]) order by id desc, created_at

With limit = page size (usually 20) execution plan looks like this:

explain analyze select * from run where id = any('{019f65b9-9925-709b-bbd2-a2fb181b7c99}'::uuid[]) order by id desc, created_at desc limit 20;
                                                                              QUERY PLAN                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=23.20..23.23 rows=14 width=512) (actual time=0.077..0.080 rows=1 loops=1)
   ->  Sort  (cost=23.20..23.23 rows=14 width=512) (actual time=0.077..0.078 rows=1 loops=1)
         Sort Key: run.id DESC, run.created_at DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Append  (cost=0.00..22.93 rows=14 width=512) (actual time=0.069..0.072 rows=1 loops=1)
               ->  Seq Scan on run_y2020 run_1  (cost=0.00..0.00 rows=1 width=508) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99}'::uuid[]))
               ->  Seq Scan on run_y2021 run_2  (cost=0.00..0.00 rows=1 width=508) (actual time=0.001..0.001 rows=0 loops=1)
                     Filter: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99}'::uuid[]))
               ->  Seq Scan on run_y2022 run_3  (cost=0.00..0.00 rows=1 width=538) (actual time=0.001..0.001 rows=0 loops=1)
                     Filter: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99}'::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}'::uuid[]))
               ->  Seq Scan on run_y2024 run_5  (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}'::uuid[]))
               ->  Seq Scan on run_y2025 run_6  (cost=0.00..2.19 rows=1 width=505) (actual time=0.012..0.012 rows=0 loops=1)
                     Filter: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99}'::uuid[]))
                     Rows Removed by Filter: 22
               ->  Seq Scan on run_y2026_m01 run_7  (cost=0.00..0.00 rows=1 width=525) (actual time=0.001..0.001 rows=0 loops=1)
                     Filter: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99}'::uuid[]))
               ->  Index Scan Backward using run_y2026_m02_pkey on run_y2026_m02 run_8  (cost=0.43..3.45 rows=1 width=493) (actual time=0.012..0.012 rows=0 loops=1)
                     Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99}'::uuid[]))
               ->  Index Scan Backward using run_y2026_m03_pkey on run_y2026_m03 run_9  (cost=0.43..3.45 rows=1 width=500) (actual time=0.007..0.007 rows=0 loops=1)
                     Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99}'::uuid[]))
               ->  Index Scan Backward using run_y2026_m04_pkey on run_y2026_m04 run_10  (cost=0.43..3.45 rows=1 width=492) (actual time=0.006..0.006 rows=0 loops=1)
                     Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99}'::uuid[]))
               ->  Index Scan Backward using run_y2026_m05_pkey on run_y2026_m05 run_11  (cost=0.43..3.45 rows=1 width=502) (actual time=0.006..0.006 rows=0 loops=1)
                     Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99}'::uuid[]))
               ->  Index Scan Backward using run_y2026_m06_pkey on run_y2026_m06 run_12  (cost=0.43..3.45 rows=1 width=526) (actual time=0.007..0.007 rows=0 loops=1)
                     Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99}'::uuid[]))
               ->  Index Scan Backward using run_y2026_m07_pkey on run_y2026_m07 run_13  (cost=0.42..3.44 rows=1 width=522) (actual time=0.008..0.009 rows=1 loops=1)
                     Index Cond: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99}'::uuid[]))
               ->  Seq Scan on run_y2026_m08 run_14  (cost=0.00..0.00 rows=1 width=478) (actual time=0.001..0.001 rows=0 loops=1)
                     Filter: (id = ANY ('{019f65b9-9925-709b-bbd2-a2fb181b7c99}'::uuid[]))
 Planning Time: 0.590 ms
 Execution Time: 0.149 ms

With explicit LIMIT 1 PostgreSQL can skip scanning other partitions if result is already found in the first one:

explain analyze select * from run where id = '019f65b9-9925-709b-bbd2-a2fb181b7c99'::uuid order by id desc, created_at desc limit 1;
                                                                           QUERY PLAN                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3.58..6.34 rows=1 width=512) (actual time=0.025..0.026 rows=1 loops=1)
   ->  Append  (cost=3.58..42.25 rows=14 width=512) (actual time=0.023..0.024 rows=1 loops=1)
         ->  Index Scan Backward using run_y2026_m08_pkey on run_y2026_m08 run_14  (cost=0.12..3.14 rows=1 width=478) (actual time=0.008..0.008 rows=0 loops=1)
               Index Cond: (id = '019f65b9-9925-709b-bbd2-a2fb181b7c99'::uuid)
         ->  Index Scan Backward using run_y2026_m07_pkey on run_y2026_m07 run_13  (cost=0.42..3.44 rows=1 width=522) (actual time=0.015..0.015 rows=1 loops=1)
               Index Cond: (id = '019f65b9-9925-709b-bbd2-a2fb181b7c99'::uuid)
         ->  Index Scan Backward using run_y2026_m06_pkey on run_y2026_m06 run_12  (cost=0.43..3.45 rows=1 width=526) (never executed)
               Index Cond: (id = '019f65b9-9925-709b-bbd2-a2fb181b7c99'::uuid)
         ->  Index Scan Backward using run_y2026_m05_pkey on run_y2026_m05 run_11  (cost=0.43..3.45 rows=1 width=502) (never executed)
               Index Cond: (id = '019f65b9-9925-709b-bbd2-a2fb181b7c99'::uuid)
         ->  Index Scan Backward using run_y2026_m04_pkey on run_y2026_m04 run_10  (cost=0.43..3.45 rows=1 width=492) (never executed)
               Index Cond: (id = '019f65b9-9925-709b-bbd2-a2fb181b7c99'::uuid)
         ->  Index Scan Backward using run_y2026_m03_pkey on run_y2026_m03 run_9  (cost=0.43..3.45 rows=1 width=500) (never executed)
               Index Cond: (id = '019f65b9-9925-709b-bbd2-a2fb181b7c99'::uuid)
         ->  Index Scan Backward using run_y2026_m02_pkey on run_y2026_m02 run_8  (cost=0.43..3.45 rows=1 width=493) (never executed)
               Index Cond: (id = '019f65b9-9925-709b-bbd2-a2fb181b7c99'::uuid)
         ->  Index Scan Backward using run_y2026_m01_pkey on run_y2026_m01 run_7  (cost=0.12..3.01 rows=1 width=525) (never executed)
               Index Cond: (id = '019f65b9-9925-709b-bbd2-a2fb181b7c99'::uuid)
         ->  Index Scan Backward using run_y2025_pkey on run_y2025 run_6  (cost=0.14..3.16 rows=1 width=505) (never executed)
               Index Cond: (id = '019f65b9-9925-709b-bbd2-a2fb181b7c99'::uuid)
         ->  Index Scan Backward using run_y2024_pkey on run_y2024 run_5  (cost=0.12..2.31 rows=1 width=537) (never executed)
               Index Cond: (id = '019f65b9-9925-709b-bbd2-a2fb181b7c99'::uuid)
         ->  Index Scan Backward using run_y2023_pkey on run_y2023 run_4  (cost=0.12..2.37 rows=1 width=537) (never executed)
               Index Cond: (id = '019f65b9-9925-709b-bbd2-a2fb181b7c99'::uuid)
         ->  Index Scan Backward using run_y2022_pkey on run_y2022 run_3  (cost=0.12..2.64 rows=1 width=538) (never executed)
               Index Cond: (id = '019f65b9-9925-709b-bbd2-a2fb181b7c99'::uuid)
         ->  Index Scan Backward using run_y2021_pkey on run_y2021 run_2  (cost=0.12..2.40 rows=1 width=508) (never executed)
               Index Cond: (id = '019f65b9-9925-709b-bbd2-a2fb181b7c99'::uuid)
         ->  Index Scan Backward using run_y2020_pkey on run_y2020 run_1  (cost=0.12..2.47 rows=1 width=508) (never executed)
               Index Cond: (id = '019f65b9-9925-709b-bbd2-a2fb181b7c99'::uuid)
 Planning Time: 0.674 ms
 Execution Time: 0.101 ms

For case with multiple ids passed into filter (GET /v1/run_id=019f65b9-9925-709b-bbd2-a2fb181b7c99&run_id=019f65b9-614c-7cae-a98f-8b2a4d5f119f) we fall back to run.id = ANY(:run_ids) LIMIT :len_run_ids case.

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-15T13:04:31Z

Coverage Report •

File	Stmts	Miss	Branch	BrPart	Cover	Missing
data_rentgen/db/repositories
dataset.py	82	0	22	1	99%	112->114
job.py	104	0	34	1	99%	253->255
operation.py	57	2	16	3	93%	129->139, 139–140
personal_token.py	40	3	8	3	88%	89->91, 91–93
run.py	131	29	50	13	77%	190->196, 287–288, 290–291, 293, 295, 303, 306–307, 309–310, 317–318, 321–324, 327–328, 331, 348–350, 358, 377–379, 381–382
data_rentgen/server/services
dataset.py	20	1	0	0	95%	37
job.py	71	3	14	0	96%	83, 134, 231
location.py	51	1	4	0	98%	58
operation.py	35	1	2	0	97%	55
run.py	44	1	4	0	98%	71
tag.py	19	1	0	0	95%	37
TOTAL	7734	518	1338	216	92%

[DOP-26672] Optimize paginate queries with id filters

f2e24e7

dolfinus force-pushed the improvement/DOP-26672-page-limit branch from cf82f2c to f2e24e7 Compare July 15, 2026 13:00

dolfinus self-assigned this Jul 15, 2026

dolfinus requested review from IlyasDevelopment and TiGrib and removed request for IlyasDevelopment July 15, 2026 13:07

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

IlyasDevelopment approved these changes Jul 15, 2026

View reviewed changes

dolfinus merged commit c5ad1b6 into develop Jul 15, 2026
11 checks passed

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

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

[DOP-26672] Optimize paginate queries with id filters#499

[DOP-26672] Optimize paginate queries with id filters#499
dolfinus merged 1 commit into
developfrom
improvement/DOP-26672-page-limit

dolfinus commented Jul 15, 2026 •

edited

Loading

Uh oh!

github-actions Bot commented Jul 15, 2026

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

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

dolfinus commented Jul 15, 2026 •

edited

Loading