Scope ML metric ordering to the experiment (!263900) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Ordering the runs of an ML experiment by a metric (MLflow runs/search with order_by=metrics.<name>, and the experiment page with order_by_type=metric) now reads only the metrics of that experiment's candidates. Today the query computes the latest value of the metric for every candidate in ml_candidate_metrics before the join, so it scans the whole table for any experiment. The new query is behind the ml_candidate_metric_order_lateral feature flag. Experiments with more than 50,000 candidates keep the current query, because the new query looks up every candidate before the sort. The index that makes each lookup a single index read comes in a follow-up, after the flag is at 100% on GitLab.com.

Design notes

Ml::Candidate.order_by_metric (app/models/ml/candidate.rb) takes a new lateral: keyword. With lateral: true it joins INNER JOIN LATERAL (SELECT candidate_id, value ... WHERE name = ? AND candidate_id = ml_candidates.id ORDER BY id DESC LIMIT 1) latest. The lookup is correlated to the outer relation, so it is scoped to whatever relation the caller built (experiment, name filter), and it works the same for the web finder and the MLflow API, which both go through Projects::Ml::CandidateFinder.
Why LATERAL and not candidate_id IN (SELECT id FROM ml_candidates WHERE experiment_id = ?) inside the existing DISTINCT ON subquery: the IN version reads every row of that metric for each candidate (the whole metric history) even with an index; the LATERAL version reads one index entry per candidate once the follow-up index exists. Both are bounded by the experiment.
The join keeps ON latest.candidate_id = ml_candidates.id (redundant with the correlation). Rails decides between preloading and eager loading includes by looking for the referenced table latest in the join string. With ON TRUE it eager loads every association of including_relationships in one big join (the cross-database join checks in specs caught it).
latest.value and metric_value keep their names, so the keyset cursor (metric value + id) is unchanged and old page tokens keep working when the flag changes.
Same results with the flag on and off: candidates without the metric are excluded (inner join), the latest row by id wins, and ties on the value are ordered by id.
Projects::Ml::CandidateFinder#lateral_metric_order? checks the flag with the project as actor, then Ml::Experiment#more_candidates_than?(LATERAL_METRIC_ORDER_MAX_CANDIDATES) (50,000). That check is LIMIT 1 OFFSET 50000 on the experiment_id index, and it runs only for metric-ordered requests with the flag on. Above the limit, the flag-on query costs about as much as the current table scan, and its first run is slower (experiment C in the database review).
Why the index is not in this MR: with the index in place, PostgreSQL plans the flag-off query as a full scan of the new index (no sort for DISTINCT ON), which is 2 to 2.5 times slower than its current parallel sequential scan. So the index comes after the flag is at 100% on GitLab.com, in the MR that enables the flag by default or removes it: #635116.
Not changed here: the latest_metrics preload (Ml::CandidateMetric.latest).
The sibling MR !263896 also edits app/models/ml/candidate.rb (next to including_relationships), spec/models/ml/candidate_spec.rb and spec/requests/api/ml/mlflow/runs_spec.rb. Whoever lands second rebases.
Pre-existing and not changed: the keyset definition says nulls_last, but the ORDER BY has no NULLS LAST. MLflow requires a metric value, so value is not NULL for rows written through the API.

References

Related to https://gitlab.com/gitlab-org/gitlab/-/work_items/635038
Related to https://gitlab.com/gitlab-org/gitlab/-/work_items/635040 (the index follow-up delivers its index part)
Feature flag rollout: https://gitlab.com/gitlab-org/gitlab/-/work_items/635115
Index follow-up, after the flag is at 100% on GitLab.com: #635116
Sibling MR that also edits app/models/ml/candidate.rb: !263896
Follow-up: the cost of a metric-ordered page still grows with the number of candidates in the experiment. A query that is fast for any experiment size needs stored latest values per candidate and metric, or a cap on metric ordering for large experiments.

Feature flag and rollback

ml_candidate_metric_order_lateral (gitlab_com_derisk, default off) switches the query. Experiments with more than 50,000 candidates keep the current query with the flag on. Rollback: disable the flag.
This MR has no migration. The order is: merge this MR, enable the flag to 100% on GitLab.com, then add the index in #635116. The rollout issue has the same sequence.

Screenshots or screen recordings

No UI change.

How to set up and validate locally

In the rails console, create an experiment whose candidates have several values of one metric:

project = Project.find_by_full_path('flightjs/Flight'); user = User.find_by_username('root')
exp = Ml::Experiment.create!(name: 'metric-order', project: project, user: user)
[3, 1, 2].each { |v| c = exp.candidates.create!(project: project, user: user); c.metrics.create!(name: 'loss', value: 10, step: 0); c.metrics.create!(name: 'loss', value: v, step: 1) }
exp.iid

Visit http://gdk.test:3000/flightjs/Flight/-/ml/experiments/<iid>?order_by=loss&order_by_type=metric&sort=desc. Expect the runs in the order of their last loss value: 3, 2, 1.
Enable the flag: Feature.enable(:ml_candidate_metric_order_lateral).
Reload the page. Expect the same order. log/development.log shows INNER JOIN LATERAL in the candidates query.

Call the MLflow API with a page size of 2, then the next page:

curl --request POST --header "PRIVATE-TOKEN: <token>" --header "Content-Type: application/json" \
  --data '{"experiment_ids": ["<iid>"], "order_by": "metrics.loss DESC", "max_results": 2}' \
  "http://gdk.test:3000/api/v4/projects/<project_id>/ml/mlflow/api/2.0/mlflow/runs/search"

Expect two runs and a next_page_token. Repeat with "page_token": "<token>". Expect the third run and no token.

Specs that cover the change

spec/models/ml/candidate_spec.rb: ordering by the latest value, both directions, relation scoping, and the lateral SQL shape; the same examples with lateral: false.
spec/finders/projects/ml/candidate_finder_spec.rb: keyset pagination forward and back over every page, with tied values, both directions, page sizes 1 to 3, with the flag on and off; the flag selects the join, and an experiment above the candidate limit keeps the subquery.
spec/models/ml/experiment_spec.rb: more_candidates_than? counts only the candidates of the experiment.
spec/requests/api/ml/mlflow/runs_spec.rb: runs/search with metrics.metric1 and metrics.metric1 ASC across two pages, with the flag on and off.
spec/requests/projects/ml/experiments_controller_spec.rb: HTML pagination by metric, and the CSV export with order_by_type=metric (it lists only the candidates that have the metric), with the flag on and off.

Database review

This MR changes queries only, behind the flag, and adds one bounded check of the experiment size; it has no migration. ml_candidate_metrics is 7.5 GB on GitLab.com and has indexes on id, candidate_id and project_id.

Database Lab (gitlab-production-main), current indexes only. LIMIT 1001 is the MLflow maximum page (the web page uses LIMIT 31). Experiment A has 88 candidates with very long metric histories, B has 15,200 candidates, and C, the largest experiment, has about 911,000 candidates. "First" runs after other queries on the clone are partly cached.

Query	Flag off (today)	Flag on (this MR)
A, first page	22.6 s cold, 3.45 s warm, 709 k buffers (parallel seq scan of the table)	5.78 s first, 2.60 s warm, 145 k buffers
B, first page	23.1 s cold, 3.23 s warm, 725 k buffers (parallel seq scan of the table)	1.35 s first, 163 ms warm, 249 k buffers
B, second page (keyset)		160 ms warm, 249 k buffers
B, CSV relation (no `LIMIT`)	same table scan as the first page	8.5 s cold, 178 ms warm, 249 k buffers
B, CSV `each_batch` boundary	23.1 s cold, 725 k buffers (table scan)	23 ms, 30.5 k buffers
B, CSV batch rows (1,000 candidates)		16 ms, 15.8 k buffers
C, first page	7.83 s first, 6.54 s warm, 4.35 M buffers	keeps the flag-off query (above the limit). The lateral query would take 13.6 s first, 5.08 s warm, 4.56 M buffers
Candidate limit check, C		13 ms first, 8.5 ms warm, 803 buffers
Candidate limit check, B		3.9 ms first, 3.0 ms warm, 104 buffers

The CSV export loads the relation once without a LIMIT, then runs about two join queries per 1,000 candidates (each_batch boundaries and rows). With the flag off, each of these queries carries the table-wide subquery. This MR changes only the join: the header pass of Ml::CandidatesCsvPresenter (each candidate's full metric history and two passes over the relation) is unchanged here and is handled in !263967 (for https://gitlab.com/gitlab-org/gitlab/-/work_items/635040).

Known limit: the flag-on query looks up the latest value of every candidate in the experiment before the sort, and the keyset filter applies after the join, so its cost grows with the experiment size. For C it is not faster than today, and its first run is slower, so experiments with more than 50,000 candidates keep the current query. Extrapolated from B, the lateral query for 50,000 candidates takes about 0.5 s warm and 4.5 s on a first run. The follow-up index (#635116) reduces each lookup to one index read: on the clone, A went to 1 ms warm and B to 88 ms warm, while C stayed at about 5 s. With that index the current query gets slower (C from 6.54 s to 10.4 s warm), so the index MR also decides what happens above the limit. Ordering that many runs by a metric needs stored latest values per candidate and metric, or a cap, which is out of scope here.

Query plans

Flag off (today): first page, experiment A (warm)

SELECT ml_candidates.*, latest.value as metric_value FROM "ml_candidates" INNER JOIN (SELECT DISTINCT ON (candidate_id, name) * FROM "ml_candidate_metrics" WHERE "ml_candidate_metrics"."name" = '<metric_name>' ORDER BY candidate_id, name, id DESC) latest ON latest.candidate_id = ml_candidates.id WHERE "ml_candidates"."experiment_id" = <experiment_id> ORDER BY "latest"."value" DESC, "ml_candidates"."id" DESC LIMIT 1001

 Limit  (cost=3169749.49..3169749.50 rows=4 width=149) (actual time=3444.311..3447.485 rows=64 loops=1)
   Buffers: shared hit=573 read=709266
   I/O Timings: read=6241.276 write=0.000
   ->  Sort  (cost=3169749.49..3169749.50 rows=4 width=149) (actual time=3444.310..3447.479 rows=64 loops=1)
         Sort Key: ml_candidate_metrics.value DESC, ml_candidates.id DESC
         Sort Method: quicksort  Memory: 41kB
         Buffers: shared hit=573 read=709266
         I/O Timings: read=6241.276 write=0.000
         ->  Merge Join  (cost=3103904.17..3169749.45 rows=4 width=149) (actual time=3416.185..3447.415 rows=64 loops=1)
               Merge Cond: (ml_candidate_metrics.candidate_id = ml_candidates.id)
               Buffers: shared hit=570 read=709266
               I/O Timings: read=6241.276 write=0.000
               ->  Unique  (cost=3103813.15..3169086.25 rows=45731 width=114) (actual time=3377.882..3447.074 rows=889 loops=1)
                     Buffers: shared hit=476 read=709266
                     I/O Timings: read=6241.276 write=0.000
                     ->  Gather Merge  (cost=3103813.15..3167714.58 rows=548668 width=114) (actual time=3377.880..3430.793 rows=306135 loops=1)
                           Workers Planned: 2
                           Workers Launched: 2
                           Buffers: shared hit=476 read=709266
                           I/O Timings: read=6241.276 write=0.000
                           ->  Sort  (cost=3102813.12..3103384.65 rows=228612 width=114) (actual time=3372.459..3379.271 rows=102639 loops=3)
                                 Sort Key: ml_candidate_metrics.candidate_id, ml_candidate_metrics.id DESC
                                 Sort Method: quicksort  Memory: 15446kB
                                 Buffers: shared hit=476 read=709266
                                 I/O Timings: read=6241.276 write=0.000
                                 ->  Parallel Seq Scan on public.ml_candidate_metrics  (cost=0.00..3082463.75 rows=228612 width=114) (actual time=2.053..3289.677 rows=184309 loops=3)
                                       Filter: (ml_candidate_metrics.name = '<metric_name>'::text)
                                       Rows Removed by Filter: 15471167
                                       Buffers: shared hit=376 read=709266
                                       I/O Timings: read=6241.276 write=0.000
               ->  Sort  (cost=91.03..91.27 rows=99 width=141) (actual time=0.228..0.239 rows=88 loops=1)
                     Sort Key: ml_candidates.id
                     Sort Method: quicksort  Memory: 37kB
                     Buffers: shared hit=94
                     I/O Timings: read=0.000 write=0.000
                     ->  Index Scan using index_ml_candidates_on_experiment_id_and_eid on public.ml_candidates  (cost=0.43..87.74 rows=99 width=141) (actual time=0.056..0.188 rows=88 loops=1)
                           Index Cond: (ml_candidates.experiment_id = <experiment_a>)
                           Buffers: shared hit=94
                           I/O Timings: read=0.000 write=0.000
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'

Flag on: first page, experiment A (warm)

SELECT ml_candidates.*, latest.value as metric_value FROM "ml_candidates" INNER JOIN LATERAL (SELECT "ml_candidate_metrics"."candidate_id", "ml_candidate_metrics"."value" FROM "ml_candidate_metrics" WHERE "ml_candidate_metrics"."name" = '<metric_name>' AND "ml_candidate_metrics"."candidate_id" = "ml_candidates"."id" ORDER BY "ml_candidate_metrics"."id" DESC LIMIT 1) latest ON latest.candidate_id = ml_candidates.id WHERE "ml_candidates"."experiment_id" = <experiment_id> ORDER BY "latest"."value" DESC, "ml_candidates"."id" DESC LIMIT 1001

 Limit  (cost=9853.81..9853.82 rows=1 width=149) (actual time=2599.243..2599.260 rows=64 loops=1)
   Buffers: shared hit=13165 read=131713 written=7
   I/O Timings: read=1770.743 write=0.144
   ->  Sort  (cost=9853.81..9853.82 rows=1 width=149) (actual time=2599.242..2599.254 rows=64 loops=1)
         Sort Key: latest.value DESC, ml_candidates.id DESC
         Sort Method: quicksort  Memory: 41kB
         Buffers: shared hit=13165 read=131713 written=7
         I/O Timings: read=1770.743 write=0.144
         ->  Nested Loop  (cost=99.05..9853.80 rows=1 width=149) (actual time=1.079..2599.090 rows=64 loops=1)
               Buffers: shared hit=13159 read=131713 written=7
               I/O Timings: read=1770.743 write=0.144
               ->  Index Scan using index_ml_candidates_on_experiment_id_and_eid on public.ml_candidates  (cost=0.43..87.74 rows=99 width=141) (actual time=0.035..0.493 rows=88 loops=1)
                     Index Cond: (ml_candidates.experiment_id = <experiment_a>)
                     Buffers: shared hit=80 read=14
                     I/O Timings: read=0.228 write=0.000
               ->  Subquery Scan on latest  (cost=98.62..98.64 rows=1 width=16) (actual time=29.528..29.528 rows=1 loops=88)
                     Filter: (ml_candidates.id = latest.candidate_id)
                     Buffers: shared hit=13079 read=131699 written=7
                     I/O Timings: read=1770.514 write=0.144
                     ->  Limit  (cost=98.62..98.62 rows=1 width=24) (actual time=29.527..29.527 rows=1 loops=88)
                           Buffers: shared hit=13079 read=131699 written=7
                           I/O Timings: read=1770.514 write=0.144
                           ->  Sort  (cost=98.62..98.65 rows=12 width=24) (actual time=29.526..29.526 rows=1 loops=88)
                                 Sort Key: ml_candidate_metrics.id DESC
                                 Sort Method: top-N heapsort  Memory: 25kB
                                 Buffers: shared hit=13079 read=131699 written=7
                                 I/O Timings: read=1770.514 write=0.144
                                 ->  Index Scan using index_ml_candidate_metrics_on_candidate_id on public.ml_candidate_metrics  (cost=0.44..98.56 rows=12 width=24) (actual time=0.142..29.407 rows=731 loops=88)
                                       Index Cond: (ml_candidate_metrics.candidate_id = ml_candidates.id)
                                       Filter: (ml_candidate_metrics.name = '<metric_name>'::text)
                                       Rows Removed by Filter: 54837
                                       Buffers: shared hit=13079 read=131699 written=7
                                       I/O Timings: read=1770.514 write=0.144
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off'

Flag off (today): first page, experiment B (warm)

Same SQL as the flag-off query above, with experiment B.

 Limit  (cost=3105737.52..3105738.67 rows=461 width=149) (actual time=3214.301..3225.085 rows=1001 loops=1)
   Buffers: shared hit=128878 read=596141
   I/O Timings: read=5670.072 write=0.000
   ->  Sort  (cost=3105737.52..3105738.67 rows=461 width=149) (actual time=3214.300..3225.011 rows=1001 loops=1)
         Sort Key: ml_candidate_metrics.value DESC, ml_candidates.id DESC
         Sort Method: top-N heapsort  Memory: 381kB
         Buffers: shared hit=128878 read=596141
         I/O Timings: read=5670.072 write=0.000
         ->  Hash Join  (cost=3096262.47..3105717.12 rows=461 width=149) (actual time=3182.885..3220.394 rows=15199 loops=1)
               Hash Cond: (ml_candidate_metrics.candidate_id = ml_candidates.id)
               Buffers: shared hit=128875 read=596141
               I/O Timings: read=5670.072 write=0.000
               ->  Unique  (cost=3085815.59..3094803.39 rows=36978 width=114) (actual time=3150.600..3187.096 rows=70310 loops=1)
                     Buffers: shared hit=113601 read=596141
                     I/O Timings: read=5670.072 write=0.000
                     ->  Gather Merge  (cost=3085815.59..3094614.51 rows=75549 width=114) (actual time=3150.597..3175.004 rows=70334 loops=1)
                           Workers Planned: 2
                           Workers Launched: 2
                           Buffers: shared hit=113601 read=596141
                           I/O Timings: read=5670.072 write=0.000
                           ->  Sort  (cost=3084815.56..3084894.26 rows=31479 width=114) (actual time=3147.215..3148.853 rows=23445 loops=3)
                                 Sort Key: ml_candidate_metrics.candidate_id, ml_candidate_metrics.id DESC
                                 Sort Method: quicksort  Memory: 2270kB
                                 Buffers: shared hit=113601 read=596141
                                 I/O Timings: read=5670.072 write=0.000
                                 ->  Parallel Seq Scan on public.ml_candidate_metrics  (cost=0.00..3082463.75 rows=31479 width=114) (actual time=51.789..3140.587 rows=23445 loops=3)
                                       Filter: (ml_candidate_metrics.name = '<metric_name>'::text)
                                       Rows Removed by Filter: 15632031
                                       Buffers: shared hit=113501 read=596141
                                       I/O Timings: read=5670.072 write=0.000
               ->  Hash  (cost=10261.77..10261.77 rows=14809 width=141) (actual time=20.258..20.259 rows=15200 loops=1)
                     Buckets: 16384  Batches: 1  Memory Usage: 2571kB
                     Buffers: shared hit=15274
                     I/O Timings: read=0.000 write=0.000
                     ->  Index Scan using index_ml_candidates_on_experiment_id_and_eid on public.ml_candidates  (cost=0.43..10261.77 rows=14809 width=141) (actual time=0.076..13.641 rows=15200 loops=1)
                           Index Cond: (ml_candidates.experiment_id = <experiment_b>)
                           Buffers: shared hit=15274
                           I/O Timings: read=0.000 write=0.000
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'

Flag on: first page, experiment B (warm)

Same SQL as the flag-on query above, with experiment B.

 Limit  (cost=1470387.66..1470387.85 rows=74 width=149) (actual time=161.156..161.281 rows=1001 loops=1)
   Buffers: shared hit=249034
   I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=1470387.66..1470387.85 rows=74 width=149) (actual time=161.155..161.206 rows=1001 loops=1)
         Sort Key: latest.value DESC, ml_candidates.id DESC
         Sort Method: top-N heapsort  Memory: 490kB
         Buffers: shared hit=249034
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=99.00..1470385.36 rows=74 width=149) (actual time=0.160..153.981 rows=15199 loops=1)
               Buffers: shared hit=249028
               I/O Timings: read=0.000 write=0.000
               ->  Index Scan using index_ml_candidates_on_experiment_id_and_eid on public.ml_candidates  (cost=0.43..10261.77 rows=14809 width=141) (actual time=0.064..11.069 rows=15200 loops=1)
                     Index Cond: (ml_candidates.experiment_id = <experiment_b>)
                     Buffers: shared hit=15274
                     I/O Timings: read=0.000 write=0.000
               ->  Subquery Scan on latest  (cost=98.57..98.59 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=15200)
                     Filter: (ml_candidates.id = latest.candidate_id)
                     Buffers: shared hit=233754
                     I/O Timings: read=0.000 write=0.000
                     ->  Limit  (cost=98.57..98.57 rows=1 width=24) (actual time=0.009..0.009 rows=1 loops=15200)
                           Buffers: shared hit=233754
                           I/O Timings: read=0.000 write=0.000
                           ->  Sort  (cost=98.57..98.58 rows=2 width=24) (actual time=0.009..0.009 rows=1 loops=15200)
                                 Sort Key: ml_candidate_metrics.id DESC
                                 Sort Method: quicksort  Memory: 25kB
                                 Buffers: shared hit=233754
                                 I/O Timings: read=0.000 write=0.000
                                 ->  Index Scan using index_ml_candidate_metrics_on_candidate_id on public.ml_candidate_metrics  (cost=0.44..98.56 rows=2 width=24) (actual time=0.005..0.008 rows=1 loops=15200)
                                       Index Cond: (ml_candidate_metrics.candidate_id = ml_candidates.id)
                                       Filter: (ml_candidate_metrics.name = '<metric_name>'::text)
                                       Rows Removed by Filter: 12
                                       Buffers: shared hit=233754
                                       I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'

Flag on: second page, experiment B (warm)

SELECT ml_candidates.*, latest.value as metric_value FROM "ml_candidates" INNER JOIN LATERAL (SELECT "ml_candidate_metrics"."candidate_id", "ml_candidate_metrics"."value" FROM "ml_candidate_metrics" WHERE "ml_candidate_metrics"."name" = '<metric_name>' AND "ml_candidate_metrics"."candidate_id" = "ml_candidates"."id" ORDER BY "ml_candidate_metrics"."id" DESC LIMIT 1) latest ON latest.candidate_id = ml_candidates.id WHERE "ml_candidates"."experiment_id" = <experiment_id> AND ('<cursor_value>' IS NULL AND "latest"."value" IS NULL AND "ml_candidates"."id" < <cursor_id> OR '<cursor_value>' IS NOT NULL AND "latest"."value" IS NULL OR '<cursor_value>' IS NOT NULL AND "latest"."value" < '<cursor_value>' OR '<cursor_value>' IS NOT NULL AND "latest"."value" = '<cursor_value>' AND "ml_candidates"."id" < <cursor_id>) ORDER BY "latest"."value" DESC, "ml_candidates"."id" DESC LIMIT 1001

 Limit  (cost=1468094.70..1468094.88 rows=73 width=149) (actual time=157.732..157.854 rows=1001 loops=1)
   Buffers: shared hit=249034
   I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=1468094.70..1468094.88 rows=73 width=149) (actual time=157.731..157.780 rows=1001 loops=1)
         Sort Key: latest.value DESC, ml_candidates.id DESC
         Sort Method: top-N heapsort  Memory: 484kB
         Buffers: shared hit=249034
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=99.00..1468092.44 rows=73 width=149) (actual time=0.099..150.859 rows=14198 loops=1)
               Buffers: shared hit=249028
               I/O Timings: read=0.000 write=0.000
               ->  Index Scan using index_ml_candidates_on_experiment_id_and_eid on public.ml_candidates  (cost=0.43..10248.97 rows=14784 width=141) (actual time=0.016..10.630 rows=15200 loops=1)
                     Index Cond: (ml_candidates.experiment_id = <experiment_b>)
                     Buffers: shared hit=15274
                     I/O Timings: read=0.000 write=0.000
               ->  Subquery Scan on latest  (cost=98.57..98.60 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=15200)
                     Filter: ((ml_candidates.id = latest.candidate_id) AND ((latest.value IS NULL) OR (latest.value < '<cursor_value>'::double precision) OR (latest.value = '<cursor_value>'::double precision)) AND ((latest.value IS NULL) OR (latest.value < '<cursor_value>'::double precision) OR ((latest.value = '<cursor_value>'::double precision) AND (ml_candidates.id < <cursor_id>))))
                     Buffers: shared hit=233754
                     I/O Timings: read=0.000 write=0.000
                     ->  Limit  (cost=98.57..98.57 rows=1 width=24) (actual time=0.009..0.009 rows=1 loops=15200)
                           Buffers: shared hit=233754
                           I/O Timings: read=0.000 write=0.000
                           ->  Sort  (cost=98.57..98.58 rows=2 width=24) (actual time=0.008..0.008 rows=1 loops=15200)
                                 Sort Key: ml_candidate_metrics.id DESC
                                 Sort Method: quicksort  Memory: 25kB
                                 Buffers: shared hit=233754
                                 I/O Timings: read=0.000 write=0.000
                                 ->  Index Scan using index_ml_candidate_metrics_on_candidate_id on public.ml_candidate_metrics  (cost=0.44..98.56 rows=2 width=24) (actual time=0.005..0.008 rows=1 loops=15200)
                                       Index Cond: (ml_candidate_metrics.candidate_id = ml_candidates.id)
                                       Filter: (ml_candidate_metrics.name = '<metric_name>'::text)
                                       Rows Removed by Filter: 12
                                       Buffers: shared hit=233754
                                       I/O Timings: read=0.000 write=0.000
Settings: seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5'

Flag on: CSV export, experiment B

The CSV export loads the relation without a LIMIT, then iterates it with each_batch; CsvBuilder orders each batch by id, so in the CSV the metric order only filters which candidates appear.

Relation without a LIMIT (warm):

SELECT ml_candidates.*, latest.value as metric_value FROM "ml_candidates" INNER JOIN LATERAL (SELECT "ml_candidate_metrics"."candidate_id", "ml_candidate_metrics"."value" FROM "ml_candidate_metrics" WHERE "ml_candidate_metrics"."name" = '<metric_name>' AND "ml_candidate_metrics"."candidate_id" = "ml_candidates"."id" ORDER BY "ml_candidate_metrics"."id" DESC LIMIT 1) latest ON latest.candidate_id = ml_candidates.id WHERE "ml_candidates"."experiment_id" = <experiment_id> ORDER BY "latest"."value" DESC, "ml_candidates"."id" DESC

 Sort  (cost=1467909.93..1467910.12 rows=74 width=149) (actual time=174.488..175.914 rows=15199 loops=1)
   Sort Key: latest.value DESC, ml_candidates.id DESC
   Sort Method: quicksort  Memory: 2708kB
   Buffers: shared hit=249034
   I/O Timings: read=0.000 write=0.000
   ->  Nested Loop  (cost=99.00..1467907.64 rows=74 width=149) (actual time=0.072..162.194 rows=15199 loops=1)
         Buffers: shared hit=249028
         I/O Timings: read=0.000 write=0.000
         ->  Index Scan using index_ml_candidates_on_experiment_id_and_eid on public.ml_candidates  (cost=0.43..10248.97 rows=14784 width=141) (actual time=0.025..11.900 rows=15200 loops=1)
               Index Cond: (ml_candidates.experiment_id = <experiment_b>)
               Buffers: shared hit=15274
               I/O Timings: read=0.000 write=0.000
         ->  Subquery Scan on latest  (cost=98.57..98.59 rows=1 width=16) (actual time=0.009..0.010 rows=1 loops=15200)
               Filter: (ml_candidates.id = latest.candidate_id)
               Buffers: shared hit=233754
               I/O Timings: read=0.000 write=0.000
               ->  Limit  (cost=98.57..98.57 rows=1 width=24) (actual time=0.009..0.009 rows=1 loops=15200)
                     Buffers: shared hit=233754
                     I/O Timings: read=0.000 write=0.000
                     ->  Sort  (cost=98.57..98.58 rows=2 width=24) (actual time=0.009..0.009 rows=1 loops=15200)
                           Sort Key: ml_candidate_metrics.id DESC
                           Sort Method: quicksort  Memory: 25kB
                           Buffers: shared hit=233754
                           I/O Timings: read=0.000 write=0.000
                           ->  Index Scan using index_ml_candidate_metrics_on_candidate_id on public.ml_candidate_metrics  (cost=0.44..98.56 rows=2 width=24) (actual time=0.005..0.009 rows=1 loops=15200)
                                 Index Cond: (ml_candidate_metrics.candidate_id = ml_candidates.id)
                                 Filter: (ml_candidate_metrics.name = '<metric_name>'::text)
                                 Rows Removed by Filter: 12
                                 Buffers: shared hit=233754
                                 I/O Timings: read=0.000 write=0.000
Settings: jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB'

Batch boundary:

SELECT "ml_candidates"."id" FROM "ml_candidates" INNER JOIN LATERAL (SELECT "ml_candidate_metrics"."candidate_id", "ml_candidate_metrics"."value" FROM "ml_candidate_metrics" WHERE "ml_candidate_metrics"."name" = '<metric_name>' AND "ml_candidate_metrics"."candidate_id" = "ml_candidates"."id" ORDER BY "ml_candidate_metrics"."id" DESC LIMIT 1) latest ON latest.candidate_id = ml_candidates.id WHERE "ml_candidates"."experiment_id" = <experiment_id> AND "ml_candidates"."id" >= <start_id> ORDER BY "ml_candidates"."id" ASC, "ml_candidates"."created_at" ASC LIMIT 1 OFFSET 1000

 Limit  (cost=1471122.44..1490841.96 rows=1 width=16) (actual time=20.503..23.066 rows=1 loops=1)
   Buffers: shared hit=30538 read=1
   I/O Timings: read=0.666 write=0.000
   ->  Nested Loop  (cost=11877.41..1471122.44 rows=74 width=16) (actual time=12.156..23.023 rows=1001 loops=1)
         Buffers: shared hit=30538 read=1
         I/O Timings: read=0.666 write=0.000
         ->  Gather Merge  (cost=11778.83..13463.77 rows=14784 width=16) (actual time=12.103..14.924 rows=1001 loops=1)
               Workers Planned: 1
               Workers Launched: 1
               Buffers: shared hit=15294 read=1
               I/O Timings: read=0.666 write=0.000
               ->  Sort  (cost=10778.82..10800.56 rows=8696 width=16) (actual time=9.743..9.857 rows=1142 loops=2)
                     Sort Key: ml_candidates.id, ml_candidates.created_at
                     Sort Method: quicksort  Memory: 694kB
                     Buffers: shared hit=15294 read=1
                     I/O Timings: read=0.666 write=0.000
                     ->  Parallel Index Scan using index_ml_candidates_on_experiment_id_and_eid on public.ml_candidates  (cost=0.43..10209.84 rows=8696 width=16) (actual time=0.026..8.052 rows=7600 loops=2)
                           Index Cond: (ml_candidates.experiment_id = <experiment_b>)
                           Filter: (ml_candidates.id >= 0)
                           Buffers: shared hit=15274 read=1
                           I/O Timings: read=0.666 write=0.000
         ->  Subquery Scan on latest  (cost=98.57..98.59 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=1001)
               Filter: (ml_candidates.id = latest.candidate_id)
               Buffers: shared hit=15244
               I/O Timings: read=0.000 write=0.000
               ->  Limit  (cost=98.57..98.57 rows=1 width=24) (actual time=0.008..0.008 rows=1 loops=1001)
                     Buffers: shared hit=15244
                     I/O Timings: read=0.000 write=0.000
                     ->  Sort  (cost=98.57..98.58 rows=2 width=24) (actual time=0.007..0.007 rows=1 loops=1001)
                           Sort Key: ml_candidate_metrics.id DESC
                           Sort Method: quicksort  Memory: 25kB
                           Buffers: shared hit=15244
                           I/O Timings: read=0.000 write=0.000
                           ->  Index Scan using index_ml_candidate_metrics_on_candidate_id on public.ml_candidate_metrics  (cost=0.44..98.56 rows=2 width=24) (actual time=0.004..0.007 rows=1 loops=1001)
                                 Index Cond: (ml_candidate_metrics.candidate_id = ml_candidates.id)
                                 Filter: (ml_candidate_metrics.name = '<metric_name>'::text)
                                 Rows Removed by Filter: 12
                                 Buffers: shared hit=15244
                                 I/O Timings: read=0.000 write=0.000
Settings: jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB'

Batch rows (warm):

SELECT ml_candidates.*, latest.value as metric_value FROM "ml_candidates" INNER JOIN LATERAL (SELECT "ml_candidate_metrics"."candidate_id", "ml_candidate_metrics"."value" FROM "ml_candidate_metrics" WHERE "ml_candidate_metrics"."name" = '<metric_name>' AND "ml_candidate_metrics"."candidate_id" = "ml_candidates"."id" ORDER BY "ml_candidate_metrics"."id" DESC LIMIT 1) latest ON latest.candidate_id = ml_candidates.id WHERE "ml_candidates"."experiment_id" = <experiment_id> AND "ml_candidates"."id" >= <start_id> AND "ml_candidates"."id" < <stop_id> ORDER BY "ml_candidates"."id" ASC

 Sort  (cost=3812.03..3812.04 rows=1 width=149) (actual time=14.341..14.424 rows=999 loops=1)
   Sort Key: ml_candidates.id
   Sort Method: quicksort  Memory: 175kB
   Buffers: shared hit=15768
   I/O Timings: read=0.000 write=0.000
   ->  Nested Loop  (cost=403.23..3812.02 rows=1 width=149) (actual time=1.905..13.837 rows=999 loops=1)
         Buffers: shared hit=15765
         I/O Timings: read=0.000 write=0.000
         ->  Bitmap Heap Scan on public.ml_candidates  (cost=304.66..361.13 rows=35 width=141) (actual time=1.808..2.805 rows=1000 loops=1)
               Recheck Cond: ((ml_candidates.id >= <start_id>) AND (ml_candidates.id < <stop_id>) AND (ml_candidates.experiment_id = <experiment_b>))
               Heap Blocks: exact=226
               Buffers: shared hit=342
               I/O Timings: read=0.000 write=0.000
               ->  BitmapAnd  (cost=304.66..304.66 rows=35 width=0) (actual time=1.765..1.767 rows=0 loops=1)
                     Buffers: shared hit=116
                     I/O Timings: read=0.000 write=0.000
                     ->  Bitmap Index Scan on ml_candidates_pkey  (cost=0.00..43.09 rows=2766 width=0) (actual time=0.210..0.211 rows=2731 loops=1)
                           Index Cond: ((ml_candidates.id >= <start_id>) AND (ml_candidates.id < <stop_id>))
                           Buffers: shared hit=19
                           I/O Timings: read=0.000 write=0.000
                     ->  Bitmap Index Scan on index_ml_candidates_on_experiment_id_and_eid  (cost=0.00..261.31 rows=14784 width=0) (actual time=1.527..1.527 rows=15200 loops=1)
                           Index Cond: (ml_candidates.experiment_id = <experiment_b>)
                           Buffers: shared hit=97
                           I/O Timings: read=0.000 write=0.000
         ->  Subquery Scan on latest  (cost=98.57..98.59 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=1000)
               Filter: (ml_candidates.id = latest.candidate_id)
               Buffers: shared hit=15423
               I/O Timings: read=0.000 write=0.000
               ->  Limit  (cost=98.57..98.57 rows=1 width=24) (actual time=0.010..0.010 rows=1 loops=1000)
                     Buffers: shared hit=15423
                     I/O Timings: read=0.000 write=0.000
                     ->  Sort  (cost=98.57..98.58 rows=2 width=24) (actual time=0.010..0.010 rows=1 loops=1000)
                           Sort Key: ml_candidate_metrics.id DESC
                           Sort Method: quicksort  Memory: 25kB
                           Buffers: shared hit=15423
                           I/O Timings: read=0.000 write=0.000
                           ->  Index Scan using index_ml_candidate_metrics_on_candidate_id on public.ml_candidate_metrics  (cost=0.44..98.56 rows=2 width=24) (actual time=0.006..0.010 rows=1 loops=1000)
                                 Index Cond: (ml_candidate_metrics.candidate_id = ml_candidates.id)
                                 Filter: (ml_candidate_metrics.name = '<metric_name>'::text)
                                 Rows Removed by Filter: 12
                                 Buffers: shared hit=15423
                                 I/O Timings: read=0.000 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off'

Flag off (today): first page, experiment C (warm)

Same SQL as the flag-off query above, with experiment C.

 Limit  (cost=3292425.55..3292428.06 rows=1001 width=149) (actual time=6536.195..6537.066 rows=1001 loops=1)
   Buffers: shared hit=3644083 read=709266
   I/O Timings: read=7716.105 write=0.000
   ->  Sort  (cost=3292425.55..3292512.46 rows=34764 width=149) (actual time=6536.194..6536.993 rows=1001 loops=1)
         Sort Key: ml_candidate_metrics.value DESC, ml_candidates.id DESC
         Sort Method: top-N heapsort  Memory: 565kB
         Buffers: shared hit=3644083 read=709266
         I/O Timings: read=7716.105 write=0.000
         ->  Nested Loop  (cost=3119087.13..3290519.23 rows=34764 width=149) (actual time=4151.834..6173.456 rows=910901 loops=1)
               Buffers: shared hit=3644080 read=709266
               I/O Timings: read=7716.105 write=0.000
               ->  Unique  (cost=3119086.71..3228741.80 rows=45731 width=114) (actual time=4151.806..4595.819 rows=910901 loops=1)
                     Buffers: shared hit=476 read=709266
                     I/O Timings: read=7716.105 write=0.000
                     ->  Gather Merge  (cost=3119086.71..3226437.47 rows=921731 width=114) (actual time=4151.804..4379.548 rows=910951 loops=1)
                           Workers Planned: 2
                           Workers Launched: 2
                           Buffers: shared hit=476 read=709266
                           I/O Timings: read=7716.105 write=0.000
                           ->  Sort  (cost=3118086.68..3119046.82 rows=384055 width=114) (actual time=4140.963..4170.056 rows=303650 loops=3)
                                 Sort Key: ml_candidate_metrics.candidate_id, ml_candidate_metrics.id DESC
                                 Sort Method: quicksort  Memory: 41204kB
                                 Buffers: shared hit=476 read=709266
                                 I/O Timings: read=7716.105 write=0.000
                                 ->  Parallel Seq Scan on public.ml_candidate_metrics  (cost=0.00..3082463.75 rows=384055 width=114) (actual time=117.593..4025.114 rows=303650 loops=3)
                                       Filter: (ml_candidate_metrics.name = '<metric_name>'::text)
                                       Rows Removed by Filter: 15351825
                                       Buffers: shared hit=376 read=709266
                                       I/O Timings: read=7716.105 write=0.000
               ->  Index Scan using ml_candidates_pkey on public.ml_candidates  (cost=0.43..1.34 rows=1 width=141) (actual time=0.001..0.001 rows=1 loops=910901)
                     Index Cond: (ml_candidates.id = ml_candidate_metrics.candidate_id)
                     Filter: (ml_candidates.experiment_id = <experiment_c>)
                     Buffers: shared hit=3643604
                     I/O Timings: read=0.000 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off'

Lateral query (not used for C after the candidate limit): first page, experiment C (warm)

Same SQL as the flag-on query above, with experiment C.

 Limit  (cost=89146698.81..89146701.31 rows=1001 width=149) (actual time=5074.623..5074.787 rows=1001 loops=1)
   Buffers: shared hit=4560984
   I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=89146698.81..89146710.09 rows=4513 width=149) (actual time=5074.622..5074.687 rows=1001 loops=1)
         Sort Key: latest.value DESC, ml_candidates.id DESC
         Sort Method: top-N heapsort  Memory: 484kB
         Buffers: shared hit=4560984
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=99.09..89146451.33 rows=4513 width=149) (actual time=0.098..4689.828 rows=910901 loops=1)
               Buffers: shared hit=4560978
               I/O Timings: read=0.000 write=0.000
               ->  Index Scan using index_ml_candidates_on_experiment_id_and_eid on public.ml_candidates  (cost=0.43..77251.41 rows=902542 width=141) (actual time=0.061..936.585 rows=911053 loops=1)
                     Index Cond: (ml_candidates.experiment_id = <experiment_c>)
                     Buffers: shared hit=916725
                     I/O Timings: read=0.000 write=0.000
               ->  Subquery Scan on latest  (cost=98.66..98.68 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=911053)
                     Filter: (ml_candidates.id = latest.candidate_id)
                     Buffers: shared hit=3644253
                     I/O Timings: read=0.000 write=0.000
                     ->  Limit  (cost=98.66..98.66 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=911053)
                           Buffers: shared hit=3644253
                           I/O Timings: read=0.000 write=0.000
                           ->  Sort  (cost=98.66..98.71 rows=20 width=24) (actual time=0.003..0.003 rows=1 loops=911053)
                                 Sort Key: ml_candidate_metrics.id DESC
                                 Sort Method: quicksort  Memory: 25kB
                                 Buffers: shared hit=3644253
                                 I/O Timings: read=0.000 write=0.000
                                 ->  Index Scan using index_ml_candidate_metrics_on_candidate_id on public.ml_candidate_metrics  (cost=0.44..98.56 rows=20 width=24) (actual time=0.003..0.003 rows=1 loops=911053)
                                       Index Cond: (ml_candidate_metrics.candidate_id = ml_candidates.id)
                                       Filter: (ml_candidate_metrics.name = '<metric_name>'::text)
                                       Buffers: shared hit=3644253
                                       I/O Timings: read=0.000 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off'

Flag on: candidate limit check, experiment C (warm)

SELECT 1 AS one FROM "ml_candidates" WHERE "ml_candidates"."experiment_id" = <experiment_id> LIMIT 1 OFFSET 50000

 Limit  (cost=1424.88..1424.91 rows=1 width=4) (actual time=7.813..7.814 rows=1 loops=1)
   Buffers: shared hit=803
   I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using index_ml_candidates_on_experiment_id_and_eid on public.ml_candidates  (cost=0.43..25884.88 rows=908575 width=4) (actual time=0.061..5.912 rows=50001 loops=1)
         Index Cond: (ml_candidates.experiment_id = <experiment_c>)
         Heap Fetches: 489
         Buffers: shared hit=803
         I/O Timings: read=0.000 write=0.000
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited Oct 23, 2026 by Eduardo Bonet

Scope ML metric ordering to the experiment