Created
September 10, 2025 22:22
-
-
Save marcbowes/33392d75d757385312d92bda95b7897f to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| create index async endpoint_by_region_name on endpoint(region_name) include (service_name, partition_name, region_name, endpoint_default_hostname, endpoint_default_has_ipv4, endpoint_default_has_ipv6, endpoint_dualstack_hostname, endpoint_dualstack_has_ipv4, endpoint_dualstack_has_ipv6); | |
| call sys.wait_for_job('xvxi2itb7vg3xgtfajip43d3iy'); | |
| # long region list | |
| # this shows how the index on region name + include columns avoids a roundtrip | |
| postgres=> explain (analyze, verbose) SELECT * | |
| FROM endpoint | |
| WHERE region_name = 'ap-northeast-1' | |
| AND (endpoint_default_hostname IS NOT NULL OR endpoint_dualstack_hostname IS NOT NULL) AND endpoint_default_has_ipv6; | |
| QUERY PLAN | |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| Index Only Scan using endpoint_by_region_name on public.endpoint (cost=151.79..171.03 rows=1 width=100) (actual time=3.766..3.766 rows=0 loops=1) | |
| Output: service_name, partition_name, region_name, endpoint_default_hostname, endpoint_default_has_ipv4, endpoint_default_has_ipv6, endpoint_dualstack_hostname, endpoint_dualstack_has_ipv4, endpoint_dualstack_has_ipv6 | |
| Index Cond: (endpoint.region_name = 'ap-northeast-1'::text) | |
| Filter: ((endpoint.endpoint_default_hostname IS NOT NULL) OR (endpoint.endpoint_dualstack_hostname IS NOT NULL)) | |
| -> Storage Scan on endpoint_by_region_name (cost=151.79..171.03 rows=0 width=100) (actual rows=0 loops=1) | |
| Projections: region_name, service_name, partition_name, endpoint_default_hostname, endpoint_default_has_ipv4, endpoint_default_has_ipv6, endpoint_dualstack_hostname, endpoint_dualstack_has_ipv4, endpoint_dualstack_has_ipv6 | |
| Filters: endpoint.endpoint_default_has_ipv6 | |
| Rows Filtered: 414 | |
| -> B-Tree Scan on endpoint_by_region_name (cost=151.79..171.03 rows=414 width=100) (actual rows=414 loops=1) | |
| Index Cond: (endpoint.region_name = 'ap-northeast-1'::text) | |
| Query Identifier: kiaihaxbbor7j | |
| Planning Time: 12.052 ms | |
| Execution Time: 3.800 ms | |
| (13 rows) | |
| # however, adding more regions breaks this optimization | |
| # after ~6 regions things still work (but, slower) | |
| postgres=> explain (analyze, verbose) SELECT * | |
| FROM endpoint | |
| WHERE region_name = ANY(array['ap-northeast-1', 'ap-southeast-1', 'ca-central-1', 'cn-north-1', 'eu-central-1', 'eu-west-1']) | |
| AND (endpoint_default_hostname IS NOT NULL OR endpoint_dualstack_hostname IS NOT NULL) AND endpoint_default_has_ipv6; | |
| QUERY PLAN | |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| Index Only Scan using endpoint_by_region_name on public.endpoint (cost=2463.21..2558.40 rows=1 width=100) (actual time=21.803..21.804 rows=0 loops=1) | |
| Output: service_name, partition_name, region_name, endpoint_default_hostname, endpoint_default_has_ipv4, endpoint_default_has_ipv6, endpoint_dualstack_hostname, endpoint_dualstack_has_ipv4, endpoint_dualstack_has_ipv6 | |
| Index Cond: (endpoint.region_name = ANY ('{ap-northeast-1,ap-southeast-1,ca-central-1,cn-north-1,eu-central-1,eu-west-1}'::text[])) | |
| Filter: ((endpoint.endpoint_default_hostname IS NOT NULL) OR (endpoint.endpoint_dualstack_hostname IS NOT NULL)) | |
| -> Storage Scan on endpoint_by_region_name (cost=2463.21..2558.40 rows=0 width=100) (actual rows=0 loops=1) | |
| Projections: region_name, service_name, partition_name, endpoint_default_hostname, endpoint_default_has_ipv4, endpoint_default_has_ipv6, endpoint_dualstack_hostname, endpoint_dualstack_has_ipv4, endpoint_dualstack_has_ipv6 | |
| Filters: endpoint.endpoint_default_has_ipv6 | |
| Rows Filtered: 414 | |
| -> B-Tree Scan on endpoint_by_region_name (cost=2463.21..2558.40 rows=2484 width=100) (actual rows=414 loops=1) | |
| Index Cond: (endpoint.region_name = ANY ('{ap-northeast-1,ap-southeast-1,ca-central-1,cn-north-1,eu-central-1,eu-west-1}'::text[])) | |
| Query Identifier: y5p5zxzujjq3u | |
| Planning Time: 0.158 ms | |
| Execution Time: 21.838 ms | |
| (13 rows) | |
| # but going all the way will do a full scan | |
| postgres=> explain (analyze, verbose) SELECT * | |
| FROM endpoint | |
| WHERE region_name = ANY(array['ap-northeast-1', 'ap-southeast-1', 'ca-central-1', 'cn-north-1', 'eu-central-1', 'eu-west-1', 'eusc-de-east-1', 'us-east-1', 'us-east-2', 'us-gov-west-1']) | |
| AND (endpoint_default_hostname IS NOT NULL OR endpoint_dualstack_hostname IS NOT NULL) AND endpoint_default_has_ipv6; | |
| QUERY PLAN | |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| Full Scan (btree-table) on public.endpoint (cost=2118.28..2643.74 rows=0 width=100) (actual time=85.058..85.059 rows=0 loops=1) | |
| Output: service_name, partition_name, region_name, endpoint_default_hostname, endpoint_default_has_ipv4, endpoint_default_has_ipv6, endpoint_dualstack_hostname, endpoint_dualstack_has_ipv4, endpoint_dualstack_has_ipv6 | |
| Filter: (((endpoint.endpoint_default_hostname IS NOT NULL) OR (endpoint.endpoint_dualstack_hostname IS NOT NULL)) AND (endpoint.region_name = ANY ('{ap-northeast-1,ap-southeast-1,ca-central-1,cn-north-1,eu-central-1,eu-west-1,eusc-de-east-1,us-east-1,us-east-2,us-gov-west-1}'::text[]))) | |
| -> Storage Scan on endpoint (cost=2118.28..2643.74 rows=0 width=100) (actual rows=0 loops=1) | |
| Projections: service_name, partition_name, region_name, endpoint_default_hostname, endpoint_default_has_ipv4, endpoint_default_has_ipv6, endpoint_dualstack_hostname, endpoint_dualstack_has_ipv4, endpoint_dualstack_has_ipv6 | |
| Filters: endpoint_default_has_ipv6 | |
| Rows Filtered: 16146 | |
| -> B-Tree Scan on endpoint (cost=2118.28..2643.74 rows=16146 width=100) (actual rows=16146 loops=1) | |
| Query Identifier: mo7kjkca485kd | |
| Planning Time: 0.153 ms | |
| Execution Time: 85.093 ms | |
| (11 rows) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment