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.
