[DOP-26646] Reuse the same window clause for IO relations by dolfinus · Pull Request #500 · MTSWebServices/data-rentgen

dolfinus · 2026-07-15T15:48:29Z

Change Summary

Before - multiple WINDOW clauses with different ORDER BY lead to calculating window over window. Also different order of columns in PARTITION BY and GROUP BY clauses lead to re-sorting the result twice.

Details

explain (analyze, buffers)
SELECT max(anon_1.created_at) AS max_created_at, NULL AS operation_id, NULL AS run_id, anon_1.job_id, anon_1.dataset_id, sum(anon_1.num_bytes) AS sum_num_bytes, sum(anon_1.num_rows) AS sum_num_rows, sum(anon_1.num_files) AS sum_num_files, min(anon_1.oldest_schema_id) AS min_schema_id, max(anon_1.newest_schema_id) AS max_schema_id
FROM (
  SELECT DISTINCT ON (job_id, run_id, operation_id, dataset_id)
  job_id AS job_id,
  run_id AS run_id,
  operation_id AS operation_id,
  dataset_id AS dataset_id,
  max(created_at) OVER (PARTITION BY job_id, run_id, operation_id, dataset_id) AS created_at,
  max(num_bytes) OVER (PARTITION BY job_id, run_id, operation_id, dataset_id) AS num_bytes,
  max(num_rows) OVER (PARTITION BY job_id, run_id, operation_id, dataset_id) AS num_rows,
  max(num_files) OVER (PARTITION BY job_id, run_id, operation_id, dataset_id) AS num_files,
  first_value(schema_id) OVER (PARTITION BY job_id, run_id, operation_id, dataset_id ORDER BY created_at, schema_id) AS oldest_schema_id,
  last_value(schema_id) OVER (PARTITION BY job_id, run_id, operation_id, dataset_id ORDER BY created_at, schema_id) AS newest_schema_id
FROM input_y2026_m07
WHERE created_at >= '2026-07-06T21:00:00+00:00'::TIMESTAMP WITH TIME ZONE AND job_id = ANY ('{23639}'::bigint[])) AS anon_1
GROUP BY anon_1.job_id, anon_1.dataset_id;
                                                                                                   QUERY PLAN                                                                                                    
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=529.93..545.68 rows=96 width=200) (actual time=1.058..1.116 rows=8 loops=1)
   Group Key: anon_1.job_id, anon_1.dataset_id
   Buffers: shared hit=44
   ->  Incremental Sort  (cost=529.93..542.08 rows=96 width=64) (actual time=1.035..1.047 rows=81 loops=1)
         Sort Key: anon_1.job_id, anon_1.dataset_id
         Presorted Key: anon_1.job_id
         Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 29kB  Peak Memory: 29kB
         Pre-sorted Groups: 1  Sort Method: quicksort  Average Memory: 30kB  Peak Memory: 30kB
         Buffers: shared hit=44
         ->  Subquery Scan on anon_1  (cost=529.84..537.76 rows=96 width=64) (actual time=0.430..0.922 rows=81 loops=1)
               Buffers: shared hit=44
               ->  Unique  (cost=529.84..536.80 rows=96 width=112) (actual time=0.428..0.859 rows=81 loops=1)
                     Buffers: shared hit=44
                     ->  WindowAgg  (cost=529.84..535.84 rows=96 width=112) (actual time=0.426..0.801 rows=81 loops=1)
                           Buffers: shared hit=44
                           ->  WindowAgg  (cost=529.84..532.96 rows=96 width=104) (actual time=0.410..0.591 rows=81 loops=1)
                                 Buffers: shared hit=44
                                 ->  Sort  (cost=529.84..530.08 rows=96 width=88) (actual time=0.391..0.403 rows=81 loops=1)
                                       Sort Key: input_y2026_m07.job_id, input_y2026_m07.run_id, input_y2026_m07.operation_id, input_y2026_m07.dataset_id, input_y2026_m07.created_at, input_y2026_m07.schema_id
                                       Sort Method: quicksort  Memory: 33kB
                                       Buffers: shared hit=44
                                       ->  Bitmap Heap Scan on input_y2026_m07  (cost=5.50..526.68 rows=96 width=88) (actual time=0.083..0.253 rows=81 loops=1)
                                             Recheck Cond: (job_id = ANY ('{23639}'::bigint[]))
                                             Filter: (created_at >= '2026-07-07 00:00:00+03'::timestamp with time zone)
                                             Rows Removed by Filter: 54
                                             Heap Blocks: exact=41
                                             Buffers: shared hit=44
                                             ->  Bitmap Index Scan on input_y2026_m07_job_id_idx  (cost=0.00..5.47 rows=140 width=0) (actual time=0.042..0.043 rows=135 loops=1)
                                                   Index Cond: (job_id = ANY ('{23639}'::bigint[]))
                                                   Buffers: shared hit=3
 Planning Time: 0.498 ms
 Execution Time: 1.273 ms

After - only one WINDOW clause, the same order of columns in PARTITION BY and GROUP BY sorts data once.

Details

explain (analyze, buffers)
SELECT max(anon_1.created_at) AS max_created_at, NULL AS operation_id, NULL AS run_id, anon_1.job_id, anon_1.dataset_id, sum(anon_1.num_bytes) AS sum_num_bytes, sum(anon_1.num_rows) AS sum_num_rows, sum(anon_1.num_files) AS sum_num_files, min(anon_1.oldest_schema_id) AS min_schema_id, max(anon_1.newest_schema_id) AS max_schema_id
FROM (
  SELECT DISTINCT ON (job_id, dataset_id, operation_id)
  job_id AS job_id,
  dataset_id AS dataset_id,
  last_value(created_at) OVER (PARTITION BY job_id, dataset_id, operation_id ORDER BY created_at, schema_id RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS created_at,
  last_value(num_bytes) OVER (PARTITION BY job_id, dataset_id, operation_id ORDER BY created_at, schema_id RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS num_bytes,
  last_value(num_rows) OVER (PARTITION BY job_id, dataset_id, operation_id ORDER BY created_at, schema_id RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS num_rows,
  last_value(num_files) OVER (PARTITION BY job_id, dataset_id, operation_id ORDER BY created_at, schema_id RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS num_files,
  first_value(schema_id) OVER (PARTITION BY job_id, dataset_id, operation_id ORDER BY created_at, schema_id RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS oldest_schema_id,
  last_value(schema_id) OVER (PARTITION BY job_id, dataset_id, operation_id ORDER BY created_at,schema_id RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS newest_schema_id
FROM input_y2026_m07
WHERE created_at >= '2026-07-06T21:00:00+00:00'::TIMESTAMP WITH TIME ZONE AND job_id = ANY ('{23639}'::bigint[])) AS anon_1
GROUP BY anon_1.job_id, anon_1.dataset_id;
                                                                              QUERY PLAN                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=529.84..538.96 rows=96 width=200) (actual time=0.458..0.812 rows=8 loops=1)
   Group Key: input_y2026_m07.job_id, input_y2026_m07.dataset_id
   Buffers: shared hit=44
   ->  Unique  (cost=529.84..534.40 rows=96 width=96) (actual time=0.393..0.739 rows=81 loops=1)
         Buffers: shared hit=44
         ->  WindowAgg  (cost=529.84..533.68 rows=96 width=96) (actual time=0.391..0.677 rows=81 loops=1)
               Buffers: shared hit=44
               ->  Sort  (cost=529.84..530.08 rows=96 width=72) (actual time=0.371..0.382 rows=81 loops=1)
                     Sort Key: input_y2026_m07.job_id, input_y2026_m07.dataset_id, input_y2026_m07.operation_id, input_y2026_m07.created_at, input_y2026_m07.schema_id
                     Sort Method: quicksort  Memory: 31kB
                     Buffers: shared hit=44
                     ->  Bitmap Heap Scan on input_y2026_m07  (cost=5.50..526.68 rows=96 width=72) (actual time=0.078..0.247 rows=81 loops=1)
                           Recheck Cond: (job_id = ANY ('{23639}'::bigint[]))
                           Filter: (created_at >= '2026-07-07 00:00:00+03'::timestamp with time zone)
                           Rows Removed by Filter: 54
                           Heap Blocks: exact=41
                           Buffers: shared hit=44
                           ->  Bitmap Index Scan on input_y2026_m07_job_id_idx  (cost=0.00..5.47 rows=140 width=0) (actual time=0.039..0.040 rows=135 loops=1)
                                 Index Cond: (job_id = ANY ('{23639}'::bigint[]))
                                 Buffers: shared hit=3
 Planning Time: 0.412 ms
 Execution Time: 0.973 ms
(22 rows)

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-15T15:57:03Z

Coverage Report •

File	Stmts	Miss	Branch	BrPart	Cover	Missing
data_rentgen/db/repositories
input.py	98	4	30	4	94%	80->81, 81, 100->101, 101, 121->122, 122, 140->141, 141
io_dataset_relation.py	41	1	10	1	96%	98->103, 103
output.py	99	4	30	4	94%	83->84, 84, 103->104, 104, 124->125, 125, 143->144, 144
TOTAL	7754	512	1336	215	92%

dolfinus force-pushed the improvement/DOP-26646 branch 2 times, most recently from 03d7b16 to 1fa8d42 Compare July 15, 2026 15:53

dolfinus force-pushed the improvement/DOP-26646 branch 2 times, most recently from 01bc868 to 7e32380 Compare July 16, 2026 11:41

dolfinus changed the title ~~[DOP-26646] Remove redundant window over window~~ [DOP-26646] Reuse the same window clause for IO relations Jul 16, 2026

dolfinus force-pushed the improvement/DOP-26646 branch 3 times, most recently from c311797 to 1082b62 Compare July 16, 2026 12:12

[DOP-26646] Reuse the same window clause for IO relations

e2de7c1

dolfinus force-pushed the improvement/DOP-26646 branch from 1082b62 to e2de7c1 Compare July 16, 2026 12:31

dolfinus marked this pull request as ready for review July 16, 2026 12:38

dolfinus requested review from IlyasDevelopment and TiGrib July 16, 2026 12:38

IlyasDevelopment reviewed Jul 17, 2026

View reviewed changes

Comment thread data_rentgen/db/repositories/input.py

IlyasDevelopment approved these changes Jul 17, 2026

View reviewed changes

dolfinus merged commit 130af6f into develop Jul 17, 2026
11 checks passed

dolfinus deleted the improvement/DOP-26646 branch July 17, 2026 08:41

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

[DOP-26646] Reuse the same window clause for IO relations#500

[DOP-26646] Reuse the same window clause for IO relations#500
dolfinus merged 1 commit into
developfrom
improvement/DOP-26646

dolfinus commented Jul 15, 2026 •

edited

Loading

Uh oh!

github-actions Bot commented Jul 15, 2026 •

edited

Loading

Uh oh!

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!

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