Skip to content

batches: Faster listing of changesets by type

Administrator requested to merge es/faster-changeset-spec-by-type into main

Created by: eseliger

We already added the denormalized column to speed this up, so let's make use of it here:

sg=# explain analyze select * from changeset_specs where batch_spec_id = 2023 AND COALESCE(changeset_specs.spec->>'externalID', NULL) IS NOT NULL;
                                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using changeset_specs_batch_spec_id on changeset_specs  (cost=0.42..13268.33 rows=42151 width=984) (actual time=686.097..686.097 rows=0 loops=1)
   Index Cond: (batch_spec_id = 2023)
   Filter: (COALESCE((spec ->> 'externalID'::text)) IS NOT NULL)
   Rows Removed by Filter: 43899
 Planning Time: 0.075 ms
 Execution Time: 686.117 ms
(6 rows)

sg=# explain analyze select * from changeset_specs where batch_spec_id = 2023 AND external_id IS NOT NULL;
                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using changeset_specs_external_id on changeset_specs  (cost=0.42..138.33 rows=20 width=984) (actual time=0.725..0.725 rows=0 loops=1)
   Index Cond: (external_id IS NOT NULL)
   Filter: (batch_spec_id = 2023)
   Rows Removed by Filter: 136
 Planning Time: 0.093 ms
 Execution Time: 0.747 ms
(6 rows)

Test plan

Tests cover this, also verified manually that the impact is good.

Merge request reports