I want to be able to use a stored query result to narrow down the partitions that I use for a subsequent query. The table is partitioned on year, then by mun_cty_co. The query is straightforward and as follows:
with counties as (
select distinct mun_cty_co
from municipal_boundaries_of_nj_3857
where st_intersects(
wkb_geometry,
ST_TileEnvelope(10, 298, 389)
)
)
select count(*)
from ard_accidents_geom_partition
where year = 2016
and mun_cty_co in (select mun_cty_co from counties)
This results in the index for every secondary partition index being scanned. However, if I hard code the results from the stored result in the query as follows, it only uses the pertinent tables.
select count(*)
from ard_accidents_geom_partition
where year = 2016
and mun_cty_co in ('01','04','06','08','17')
I have included the query plan below for (hopefully) further insight:
Finalize Aggregate (cost=38424.49..38424.50 rows=1 width=8) (actual time=2688.778..2787.861 rows=1 loops=1)
Buffers: shared hit=25303 read=9701
-> Gather (cost=38424.27..38424.48 rows=2 width=8) (actual time=2685.598..2787.842 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=25303 read=9701
-> Partial Aggregate (cost=37424.27..37424.28 rows=1 width=8) (actual time=2524.317..2524.335 rows=1 loops=3)
Buffers: shared hit=25303 read=9701
-> Hash Join (cost=313.83..37411.15 rows=5248 width=0) (actual time=1718.983..2523.545 rows=11752 loops=3)
Hash Cond: ((ard_accidents_geom_partition.mun_cty_co)::text = (municipal_boundaries_of_nj_3857.mun_cty_co)::text)
Buffers: shared hit=25303 read=9701
-> Parallel Append (cost=0.00..36784.72 rows=116614 width=3) (actual time=2.294..2421.200 rows=93291 loops=3)
Buffers: shared hit=24755 read=9574
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_bergen ard_accidents_geom_partition_2 (cost=0.00..3772.85 rows=12388 width=3) (actual time=0.487..28.623 rows=29731 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared hit=3618
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_middlesex ard_accidents_geom_partition_12 (cost=0.00..3609.27 rows=11702 width=3) (actual time=0.371..23.792 rows=28084 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared hit=3463
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_essex ard_accidents_geom_partition_7 (cost=0.00..3586.76 rows=11900 width=3) (actual time=0.253..22.558 rows=28561 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared hit=3438
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_union ard_accidents_geom_partition_20 (cost=0.00..2621.31 rows=12105 width=3) (actual time=44.684..2015.981 rows=20578 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared read=2470
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_hudson ard_accidents_geom_partition_9 (cost=0.00..2621.04 rows=12484 width=3) (actual time=0.311..22.098 rows=21222 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared hit=2465
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_monmouth ard_accidents_geom_partition_13 (cost=0.00..2504.88 rows=11191 width=3) (actual time=0.024..19.360 rows=19024 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared hit=2365
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_passaic ard_accidents_geom_partition_16 (cost=0.00..2232.54 rows=10604 width=3) (actual time=19.069..1498.141 rows=18026 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared read=2100
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_ocean ard_accidents_geom_partition_15 (cost=0.00..1964.26 rows=8741 width=3) (actual time=10.490..470.290 rows=4953 loops=3)
Filter: (year = '2016'::numeric)
Buffers: shared read=1855
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_morris ard_accidents_geom_partition_14 (cost=0.00..1919.81 rows=8625 width=3) (actual time=0.387..255.318 rows=7331 loops=2)
Filter: (year = '2016'::numeric)
Buffers: shared hit=1072 read=740
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_camden ard_accidents_geom_partition_4 (cost=0.00..1817.93 rows=8074 width=3) (actual time=0.068..6.901 rows=13726 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared hit=1717
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_burlington ard_accidents_geom_partition_3 (cost=0.00..1758.49 rows=7639 width=3) (actual time=0.029..5.552 rows=12986 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared hit=1663
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_mercer ard_accidents_geom_partition_11 (cost=0.00..1650.51 rows=7401 width=3) (actual time=0.017..6.058 rows=12581 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared hit=1558
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_somerset ard_accidents_geom_partition_18 (cost=0.00..1446.36 rows=6349 width=3) (actual time=13.808..651.948 rows=10793 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared read=1367
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_atlantic ard_accidents_geom_partition_1 (cost=0.00..1073.80 rows=4624 width=3) (actual time=0.005..3.565 rows=7861 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared hit=1016
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_gloucester ard_accidents_geom_partition_8 (cost=0.00..1048.90 rows=4552 width=3) (actual time=0.034..3.300 rows=7738 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared hit=992
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_cumberland ard_accidents_geom_partition_6 (cost=0.00..564.20 rows=2496 width=3) (actual time=0.038..1.754 rows=4243 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared hit=533
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_hunterdon ard_accidents_geom_partition_10 (cost=0.00..514.00 rows=2320 width=3) (actual time=0.031..1.745 rows=3944 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared hit=485
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_warren ard_accidents_geom_partition_21 (cost=0.00..444.85 rows=1988 width=3) (actual time=4.744..512.568 rows=3380 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared read=420
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_sussex ard_accidents_geom_partition_19 (cost=0.00..426.77 rows=1902 width=3) (actual time=10.438..258.951 rows=3233 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared read=403
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_cape_may ard_accidents_geom_partition_5 (cost=0.00..391.72 rows=1738 width=3) (actual time=0.120..1.417 rows=2954 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared hit=370
-> Parallel Seq Scan on ard_accidents_geom_partition_2016_salem ard_accidents_geom_partition_17 (cost=0.00..231.40 rows=992 width=3) (actual time=6.074..237.824 rows=1687 loops=1)
Filter: (year = '2016'::numeric)
Buffers: shared read=219
-> Hash (cost=313.72..313.72 rows=9 width=3) (actual time=89.302..89.304 rows=5 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=520 read=127
-> Unique (cost=313.57..313.63 rows=9 width=3) (actual time=56.201..56.218 rows=5 loops=3)
Buffers: shared hit=520 read=127
-> Sort (cost=313.57..313.60 rows=12 width=3) (actual time=56.200..56.203 rows=20 loops=3)
Sort Key: municipal_boundaries_of_nj_3857.mun_cty_co
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=520 read=127
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Index Scan using municipal_boundaries_of_nj_3857_wkb_geometry_idx on municipal_boundaries_of_nj_3857 (cost=0.14..313.35 rows=12 width=3) (actual time=19.338..55.244 rows=20 loops=3)
Index Cond: (wkb_geometry && '0103000020110F00000100000005000000B66B2B14C3F25FC1CC3F3AA2A8365241B66B2B14C3F25FC1EE37C592E05C52419473A0238BCC5FC1EE37C592E05C52419473A0238BCC5FC1CC3F3AA2A8365241B66B2B14C3F25FC1CC3F3AA2A8365241'::geometry)
Filter: st_intersects(wkb_geometry, '0103000020110F00000100000005000000B66B2B14C3F25FC1CC3F3AA2A8365241B66B2B14C3F25FC1EE37C592E05C52419473A0238BCC5FC1EE37C592E05C52419473A0238BCC5FC1CC3F3AA2A8365241B66B2B14C3F25FC1CC3F3AA2A8365241'::geometry)
Buffers: shared hit=498 read=127
Planning:
Buffers: shared hit=357
Planning Time: 72.631 ms
Execution Time: 2808.158 ms
For comparison I have also included the plan I would hope to get, given the hardcoded query. Or at least seems more intuitive to me...
Aggregate (cost=5402.44..5402.45 rows=1 width=8) (actual time=25.161..25.164 rows=1 loops=1)
Buffers: shared hit=4477
-> Append (cost=0.00..5314.31 rows=35255 width=0) (actual time=0.025..23.289 rows=35255 loops=1)
Buffers: shared hit=4477
-> Seq Scan on ard_accidents_geom_partition_2016_atlantic ard_accidents_geom_partition_1 (cost=0.00..1163.39 rows=7861 width=0) (actual time=0.023..4.500 rows=7861 loops=1)
Filter: ((year = '2016'::numeric) AND ((mun_cty_co)::text = ANY ('{01,04,06,08,17}'::text[])))
Buffers: shared hit=1016
-> Seq Scan on ard_accidents_geom_partition_2016_camden ard_accidents_geom_partition_2 (cost=0.00..1974.36 rows=13726 width=0) (actual time=0.121..7.977 rows=13726 loops=1)
Filter: ((year = '2016'::numeric) AND ((mun_cty_co)::text = ANY ('{01,04,06,08,17}'::text[])))
Buffers: shared hit=1717
-> Seq Scan on ard_accidents_geom_partition_2016_cumberland ard_accidents_geom_partition_3 (cost=0.00..612.56 rows=4243 width=0) (actual time=0.072..2.527 rows=4243 loops=1)
Filter: ((year = '2016'::numeric) AND ((mun_cty_co)::text = ANY ('{01,04,06,08,17}'::text[])))
Buffers: shared hit=533
-> Seq Scan on ard_accidents_geom_partition_2016_gloucester ard_accidents_geom_partition_4 (cost=0.00..1137.09 rows=7738 width=0) (actual time=0.052..4.867 rows=7738 loops=1)
Filter: ((year = '2016'::numeric) AND ((mun_cty_co)::text = ANY ('{01,04,06,08,17}'::text[])))
Buffers: shared hit=992
-> Seq Scan on ard_accidents_geom_partition_2016_salem ard_accidents_geom_partition_5 (cost=0.00..250.63 rows=1687 width=0) (actual time=0.050..1.146 rows=1687 loops=1)
Filter: ((year = '2016'::numeric) AND ((mun_cty_co)::text = ANY ('{01,04,06,08,17}'::text[])))
Buffers: shared hit=219
Planning:
Buffers: shared hit=51
Planning Time: 1.832 ms
Execution Time: 25.254 ms
I added the year,mun_cty_co index per the suggestion from Laurenz and now it is using an indexed scan. However, it still seems to be checking all the partitions.
Finalize Aggregate (cost=7166.30..7166.31 rows=1 width=8) (actual time=93.193..107.194 rows=1 loops=1)
Buffers: shared hit=1202
-> Gather (cost=7166.19..7166.30 rows=1 width=8) (actual time=92.957..107.186 rows=2 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=1202
-> Partial Aggregate (cost=6166.19..6166.20 rows=1 width=8) (actual time=52.912..52.923 rows=1 loops=2)
Buffers: shared hit=1202
-> Hash Join (cost=314.12..6147.67 rows=7408 width=0) (actual time=8.761..51.825 rows=17628 loops=2)
Hash Cond: ((ard_accidents_geom_partition.mun_cty_co)::text = (municipal_boundaries_of_nj_3857.mun_cty_co)::text)
Buffers: shared hit=1202
-> Parallel Append (cost=0.29..5392.52 rows=164635 width=3) (actual time=0.283..33.851 rows=139937 loops=2)
Buffers: shared hit=820
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_bergen_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_bergen ard_accidents_geom_partition_2 (cost=0.29..482.16 rows=17489 width=3) (actual time=0.568..3.314 rows=14866 loops=2)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=86
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_essex_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_essex ard_accidents_geom_partition_7 (cost=0.29..463.50 rows=16801 width=3) (actual time=0.633..5.470 rows=28561 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=81
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_middlesex_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_middlesex ard_accidents_geom_partition_12 (cost=0.29..455.12 rows=16520 width=3) (actual time=0.463..5.309 rows=28084 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=79
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_hudson_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_hudson ard_accidents_geom_partition_9 (cost=0.29..344.29 rows=12484 width=3) (actual time=0.538..4.285 rows=21222 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=60
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_union_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_union ard_accidents_geom_partition_20 (cost=0.29..334.67 rows=12105 width=3) (actual time=0.042..3.395 rows=20578 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=59
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_monmouth_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_monmouth ard_accidents_geom_partition_13 (cost=0.29..308.87 rows=11191 width=3) (actual time=0.043..3.021 rows=19024 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=54
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_passaic_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_passaic ard_accidents_geom_partition_16 (cost=0.29..293.52 rows=10604 width=3) (actual time=0.039..3.040 rows=18026 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=52
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_ocean_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_ocean ard_accidents_geom_partition_15 (cost=0.29..242.15 rows=8741 width=3) (actual time=0.040..2.471 rows=14860 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=43
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_morris_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_morris ard_accidents_geom_partition_14 (cost=0.29..239.50 rows=8625 width=3) (actual time=0.045..2.300 rows=14662 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=43
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_camden_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_camden ard_accidents_geom_partition_4 (cost=0.29..223.97 rows=8074 width=3) (actual time=0.041..2.426 rows=13726 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=40
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_burlingto_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_burlington ard_accidents_geom_partition_3 (cost=0.29..212.07 rows=7639 width=3) (actual time=0.041..2.137 rows=12986 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=38
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_mercer_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_mercer ard_accidents_geom_partition_11 (cost=0.29..205.65 rows=7401 width=3) (actual time=0.042..2.033 rows=12581 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=37
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_somerset_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_somerset ard_accidents_geom_partition_18 (cost=0.29..176.72 rows=6349 width=3) (actual time=0.041..1.842 rows=10793 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=32
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_atlantic_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_atlantic ard_accidents_geom_partition_1 (cost=0.28..129.48 rows=4624 width=3) (actual time=0.040..1.400 rows=7861 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=24
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_glouceste_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_gloucester ard_accidents_geom_partition_8 (cost=0.28..127.84 rows=4552 width=3) (actual time=0.037..1.279 rows=7738 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=24
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_cumberlan_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_cumberland ard_accidents_geom_partition_6 (cost=0.28..71.06 rows=2496 width=3) (actual time=0.038..0.699 rows=4243 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=14
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_hunterdon_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_hunterdon ard_accidents_geom_partition_10 (cost=0.28..66.06 rows=2320 width=3) (actual time=0.040..0.674 rows=3944 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=13
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_warren_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_warren ard_accidents_geom_partition_21 (cost=0.28..57.51 rows=1988 width=3) (actual time=0.040..0.550 rows=3380 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=12
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_sussex_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_sussex ard_accidents_geom_partition_19 (cost=0.28..54.55 rows=1902 width=3) (actual time=0.035..0.553 rows=3233 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=11
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_cape_may_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_cape_may ard_accidents_geom_partition_5 (cost=0.28..50.81 rows=1738 width=3) (actual time=0.040..0.519 rows=2954 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=11
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_salem_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_salem ard_accidents_geom_partition_17 (cost=0.28..29.85 rows=992 width=3) (actual time=0.050..0.307 rows=1687 loops=1)
Index Cond: (year = '2016'::numeric)
Heap Fetches: 0
Buffers: shared hit=7
-> Hash (cost=313.72..313.72 rows=9 width=3) (actual time=3.386..3.388 rows=5 loops=2)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=368
-> Unique (cost=313.57..313.63 rows=9 width=3) (actual time=3.375..3.383 rows=5 loops=2)
Buffers: shared hit=368
-> Sort (cost=313.57..313.60 rows=12 width=3) (actual time=3.374..3.375 rows=20 loops=2)
Sort Key: municipal_boundaries_of_nj_3857.mun_cty_co
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=368
Worker 0: Sort Method: quicksort Memory: 25kB
-> Index Scan using municipal_boundaries_of_nj_3857_wkb_geometry_idx on municipal_boundaries_of_nj_3857 (cost=0.14..313.35 rows=12 width=3) (actual time=0.839..3.295 rows=20 loops=2)
Index Cond: (wkb_geometry && '0103000020110F00000100000005000000B66B2B14C3F25FC1CC3F3AA2A8365241B66B2B14C3F25FC1EE37C592E05C52419473A0238BCC5FC1EE37C592E05C52419473A0238BCC5FC1CC3F3AA2A8365241B66B2B14C3F25FC1CC3F3AA2A8365241'::geometry)
Filter: st_intersects(wkb_geometry, '0103000020110F00000100000005000000B66B2B14C3F25FC1CC3F3AA2A8365241B66B2B14C3F25FC1EE37C592E05C52419473A0238BCC5FC1EE37C592E05C52419473A0238BCC5FC1CC3F3AA2A8365241B66B2B14C3F25FC1CC3F3AA2A8365241'::geometry)
Buffers: shared hit=357
Planning Time: 4.253 ms
Execution Time: 107.782 ms
This is still not working the way that many have described and I continued to investigate and found the following when I limited the subquery result to a single value.
with counties as (
(
select mun_cty_co
from county_boundaries_of_nj_3857
where st_intersects(
wkb_geometry,
ST_TileEnvelope(10, 298, 389)
)
limit 1
))
select count(*)
from ard_accidents_geom_partition
where year = 2016
and mun_cty_co = (select * from counties)
The result mirrors what some have mentioned (never executed). How do I achieve this with more than one value?
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_atlantic_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_atlantic ard_accidents_geom_partition_1 (cost=0.28..149.13 rows=4624 width=0) (actual time=0.080..1.676 rows=7861 loops=1)
Index Cond: ((year = '2016'::numeric) AND (mun_cty_co = ($0)::text))
Heap Fetches: 0
Buffers: shared hit=24
-> Parallel Index Only Scan using ard_accidents_geom_partition_2016_salem_year_mun_cty_co_idx on ard_accidents_geom_partition_2016_salem ard_accidents_geom_partition_17 (cost=0.28..34.07 rows=992 width=0) (never executed)
Index Cond: ((year = '2016'::numeric) AND (mun_cty_co = ($0)::text))
Heap Fetches: 0
Because in the second case the optimizer can already determine which partitions need to be scanned, while in the first case that cannot be determined in advance.
This answer assumes that your PostgreSQL version is v11 or less, because v12 introduced partition pruning at query execution time.
If your PostgreSQL version is v12 or better, you may get partition pruning if the optimizer translates your query into a nested loop join with the partitioned table on the inner side: in that case, EXPLAIN (ANALYZE) for the skipped partitions will look like this:
-> Seq Scan on ard_accidents_geom_partition_p42 tab_2 (cost=0.00..10000 rows=1 width=48) (never executed)
Filter: (mun_cty_co = 42)
In your case, you should have an index on ard_accidents_geom_partition(year, mun_cty_co) to encourage a nested loop join which can benefit.