title: Using a Stored Query Result For an "IN" Clause Hits ALL Partitions
body: <p>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:</p>
  <pre><code>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)
  </code></pre>
  <p>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.</p>
  <pre><code>select count(*)
  from ard_accidents_geom_partition
  where year = 2016
  and mun_cty_co in ('01','04','06','08','17')
  </code></pre>
  <p>I have included the query plan below for (hopefully) further insight:</p>
  <pre><code>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
      -&gt;  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
          -&gt;  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
                  -&gt;  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
                      -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                              -&gt;  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
                      -&gt;  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
                              -&gt;  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
                                  -&gt;  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
                                          -&gt;  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 &amp;&amp; '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
  </code></pre>
  <p>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...</p>
  <pre><code>Aggregate  (cost=5402.44..5402.45 rows=1 width=8) (actual time=25.161..25.164 rows=1 loops=1)
  Buffers: shared hit=4477
  -&gt;  Append  (cost=0.00..5314.31 rows=35255 width=0) (actual time=0.025..23.289 rows=35255 loops=1)
          Buffers: shared hit=4477
          -&gt;  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
          -&gt;  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
          -&gt;  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
          -&gt;  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
          -&gt;  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
  </code></pre>
  <p>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.</p>
  <pre><code>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
    -&gt;  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
          -&gt;  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
                -&gt;  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
                      -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                            -&gt;  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
                      -&gt;  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
                            -&gt;  Unique  (cost=313.57..313.63 rows=9 width=3) (actual time=3.375..3.383 rows=5 loops=2)
                                  Buffers: shared hit=368
                                  -&gt;  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
                                        -&gt;  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 &amp;&amp; '0103000020110F00000100000005000000B66B2B14C3F25FC1CC3F3AA2A8365241B66B2B14C3F25FC1EE37C592E05C52419473A0238BCC5FC1EE37C592E05C52419473A0238BCC5FC1CC3F3AA2A8365241B66B2B14C3F25FC1CC3F3AA2A8365241'::geometry)
                                              Filter: st_intersects(wkb_geometry, '0103000020110F00000100000005000000B66B2B14C3F25FC1CC3F3AA2A8365241B66B2B14C3F25FC1EE37C592E05C52419473A0238BCC5FC1EE37C592E05C52419473A0238BCC5FC1CC3F3AA2A8365241B66B2B14C3F25FC1CC3F3AA2A8365241'::geometry)
                                              Buffers: shared hit=357
  Planning Time: 4.253 ms
  Execution Time: 107.782 ms
  </code></pre>
  <p>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.</p>
  <pre><code>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)
  </code></pre>
  <p>The result mirrors what some have mentioned (never executed). How do I achieve this with more than one value?</p>
  <pre><code>-&gt;  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
  -&gt;  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
  </code></pre>