Optimize only one Schedule_a query

[PaulClark2]

Look at Rohan's research here:
#3739
#3697

Completion criteria:

• Pick one schedule_a query to optimize and begin optimizing.
• Check if expression-based-sorts will work? See comment below.
• Document improvements or challenges after work is completed
• Open up follow-up ticket for additional slow queries to optimize

[lbeaufort]

Carlo recently asked about expression-based sorts which we may want to look into for improving Schedule A performance.

[fecjjeng]

Reviewed pgBadger output from 2019 September. The following one is one of the slow queries that involved fec_fitem_sched_a. This is a simple query but took avg of 8m1s to execute.
min max avg
5s176ms 19m52s 8m1s

SELECT count(*) AS count_1 FROM
(
SELECT disclosure.fec_fitem_sched_a.cmte_id AS disclosure_fec_fitem_sched_a_cmte_id, disclosure.fec_fitem_sched_a.rpt_yr AS disclosure_fec_fitem_sched_a_rpt_yr, disclosure.fec_fitem_sched_a.rpt_tp AS disclosure_fec_fitem_sched_a_rpt_tp, disclosure.fec_fitem_sched_a.image_num AS disclosure_fec_fitem_sched_a_image_num, disclosure.fec_fitem_sched_a.line_num AS disclosure_fec_fitem_sched_a_line_num, disclosure.fec_fitem_sched_a.tran_id AS disclosure_fec_fitem_sched_a_tran_id, disclosure.fec_fitem_sched_a.file_num AS disclosure_fec_fitem_sched_a_file_num, disclosure.fec_fitem_sched_a.cmte_nm AS disclosure_fec_fitem_sched_a_cmte_nm, disclosure.fec_fitem_sched_a.entity_tp AS disclosure_fec_fitem_sched_a_entity_tp, disclosure.fec_fitem_sched_a.entity_tp_desc AS disclosure_fec_fitem_sched_a_entity_tp_desc, disclosure.fec_fitem_sched_a.contbr_id AS disclosure_fec_fitem_sched_a_contbr_id, disclosure.fec_fitem_sched_a.contbr_prefix AS disclosure_fec_fitem_sched_a_contbr_prefix, disclosure.fec_fitem_sched_a.contbr_nm AS disclosure_fec_fitem_sched_a_contbr_nm, disclosure.fec_fitem_sched_a.cmte_tp AS disclosure_fec_fitem_sched_a_cmte_tp, disclosure.fec_fitem_sched_a.org_tp AS disclosure_fec_fitem_sched_a_org_tp, disclosure.fec_fitem_sched_a.cmte_dsgn AS disclosure_fec_fitem_sched_a_cmte_dsgn, disclosure.fec_fitem_sched_a.contbr_nm_first AS disclosure_fec_fitem_sched_a_contbr_nm_first, disclosure.fec_fitem_sched_a.contbr_m_nm AS disclosure_fec_fitem_sched_a_contbr_m_nm, disclosure.fec_fitem_sched_a.contbr_nm_last AS disclosure_fec_fitem_sched_a_contbr_nm_last, disclosure.fec_fitem_sched_a.contbr_suffix AS disclosure_fec_fitem_sched_a_contbr_suffix, disclosure.fec_fitem_sched_a.contbr_st1 AS disclosure_fec_fitem_sched_a_contbr_st1, disclosure.fec_fitem_sched_a.contbr_st2 AS disclosure_fec_fitem_sched_a_contbr_st2, disclosure.fec_fitem_sched_a.contbr_city AS disclosure_fec_fitem_sched_a_contbr_city, disclosure.fec_fitem_sched_a.contbr_st AS disclosure_fec_fitem_sched_a_contbr_st, disclosure.fec_fitem_sched_a.contbr_zip AS disclosure_fec_fitem_sched_a_contbr_zip, disclosure.fec_fitem_sched_a.contbr_employer AS disclosure_fec_fitem_sched_a_contbr_employer, disclosure.fec_fitem_sched_a.contbr_occupation AS disclosure_fec_fitem_sched_a_contbr_occupation, disclosure.fec_fitem_sched_a.clean_contbr_id AS disclosure_fec_fitem_sched_a_clean_contbr_id, disclosure.fec_fitem_sched_a.receipt_tp AS disclosure_fec_fitem_sched_a_receipt_tp, disclosure.fec_fitem_sched_a.receipt_tp_desc AS disclosure_fec_fitem_sched_a_receipt_tp_desc, disclosure.fec_fitem_sched_a.receipt_desc AS disclosure_fec_fitem_sched_a_receipt_desc, disclosure.fec_fitem_sched_a.memo_cd AS disclosure_fec_fitem_sched_a_memo_cd, disclosure.fec_fitem_sched_a.memo_cd_desc AS disclosure_fec_fitem_sched_a_memo_cd_desc, disclosure.fec_fitem_sched_a.contb_receipt_dt AS disclosure_fec_fitem_sched_a_contb_receipt_dt, disclosure.fec_fitem_sched_a.contb_receipt_amt AS disclosure_fec_fitem_sched_a_contb_receipt_amt, disclosure.fec_fitem_sched_a.contb_aggregate_ytd AS disclosure_fec_fitem_sched_a_contb_aggregate_ytd, disclosure.fec_fitem_sched_a.cand_id AS disclosure_fec_fitem_sched_a_cand_id, disclosure.fec_fitem_sched_a.cand_nm AS disclosure_fec_fitem_sched_a_cand_nm, disclosure.fec_fitem_sched_a.cand_nm_first AS disclosure_fec_fitem_sched_a_cand_nm_first, disclosure.fec_fitem_sched_a.cand_nm_last AS disclosure_fec_fitem_sched_a_cand_nm_last, disclosure.fec_fitem_sched_a.cand_m_nm AS disclosure_fec_fitem_sched_a_cand_m_nm, disclosure.fec_fitem_sched_a.cand_prefix AS disclosure_fec_fitem_sched_a_cand_prefix, disclosure.fec_fitem_sched_a.cand_suffix AS disclosure_fec_fitem_sched_a_cand_suffix, disclosure.fec_fitem_sched_a.cand_office AS disclosure_fec_fitem_sched_a_cand_office, disclosure.fec_fitem_sched_a.cand_office_desc AS disclosure_fec_fitem_sched_a_cand_office_desc, disclosure.fec_fitem_sched_a.cand_office_st AS disclosure_fec_fitem_sched_a_cand_office_st, disclosure.fec_fitem_sched_a.cand_office_st_desc AS disclosure_fec_fitem_sched_a_cand_office_st_desc, disclosure.fec_fitem_sched_a.cand_office_district AS disclosure_fec_fitem_sched_a_cand_office_district, disclosure.fec_fitem_sched_a.conduit_cmte_id AS disclosure_fec_fitem_sched_a_conduit_cmte_id, disclosure.fec_fitem_sched_a.conduit_cmte_nm AS disclosure_fec_fitem_sched_a_conduit_cmte_nm, disclosure.fec_fitem_sched_a.conduit_cmte_st1 AS disclosure_fec_fitem_sched_a_conduit_cmte_st1, disclosure.fec_fitem_sched_a.conduit_cmte_st2 AS disclosure_fec_fitem_sched_a_conduit_cmte_st2, disclosure.fec_fitem_sched_a.conduit_cmte_city AS disclosure_fec_fitem_sched_a_conduit_cmte_city, disclosure.fec_fitem_sched_a.conduit_cmte_st AS disclosure_fec_fitem_sched_a_conduit_cmte_st, disclosure.fec_fitem_sched_a.conduit_cmte_zip AS disclosure_fec_fitem_sched_a_conduit_cmte_zip, disclosure.fec_fitem_sched_a.donor_cmte_nm AS disclosure_fec_fitem_sched_a_donor_cmte_nm, disclosure.fec_fitem_sched_a.national_cmte_nonfed_acct AS disclosure_fec_fitem_sched_a_national_cmte_nonfed_acct, disclosure.fec_fitem_sched_a.election_tp AS disclosure_fec_fitem_sched_a_election_tp, disclosure.fec_fitem_sched_a.election_tp_desc AS disclosure_fec_fitem_sched_a_election_tp_desc, disclosure.fec_fitem_sched_a.fec_election_tp_desc AS disclosure_fec_fitem_sched_a_fec_election_tp_desc, disclosure.fec_fitem_sched_a.fec_election_yr AS disclosure_fec_fitem_sched_a_fec_election_yr, disclosure.fec_fitem_sched_a.action_cd AS disclosure_fec_fitem_sched_a_action_cd, disclosure.fec_fitem_sched_a.action_cd_desc AS disclosure_fec_fitem_sched_a_action_cd_desc, disclosure.fec_fitem_sched_a.schedule_type_desc AS disclosure_fec_fitem_sched_a_schedule_type_desc, disclosure.fec_fitem_sched_a.pg_date AS disclosure_fec_fitem_sched_a_pg_date, disclosure.fec_fitem_sched_a.orig_sub_id AS disclosure_fec_fitem_sched_a_orig_sub_id, disclosure.fec_fitem_sched_a.back_ref_tran_id AS disclosure_fec_fitem_sched_a_back_ref_tran_id, disclosure.fec_fitem_sched_a.back_ref_sched_nm AS disclosure_fec_fitem_sched_a_back_ref_sched_nm, disclosure.fec_fitem_sched_a.filing_form AS disclosure_fec_fitem_sched_a_filing_form, disclosure.fec_fitem_sched_a.link_id AS disclosure_fec_fitem_sched_a_link_id, disclosure.fec_fitem_sched_a.contributor_name_text AS disclosure_fec_fitem_sched_a_contributor_name_text, disclosure.fec_fitem_sched_a.contributor_employer_text AS disclosure_fec_fitem_sched_a_contributor_employer_text, disclosure.fec_fitem_sched_a.contributor_occupation_text AS disclosure_fec_fitem_sched_a_contributor_occupation_text, disclosure.fec_fitem_sched_a.is_individual AS disclosure_fec_fitem_sched_a_is_individual, disclosure.fec_fitem_sched_a.memo_text AS disclosure_fec_fitem_sched_a_memo_text, disclosure.fec_fitem_sched_a.two_year_transaction_period AS disclosure_fec_fitem_sched_a_two_year_transaction_period, disclosure.fec_fitem_sched_a.schedule_type AS disclosure_fec_fitem_sched_a_schedule_type, disclosure.fec_fitem_sched_a.increased_limit AS disclosure_fec_fitem_sched_a_increased_limit, disclosure.fec_fitem_sched_a.sub_id AS disclosure_fec_fitem_sched_a_sub_id, disclosure.fec_fitem_sched_a.pdf_url AS disclosure_fec_fitem_sched_a_pdf_url, disclosure.fec_fitem_sched_a.line_number_label AS disclosure_fec_fitem_sched_a_line_number_label
FROM disclosure.fec_fitem_sched_a
WHERE disclosure.fec_fitem_sched_a.is_individual = TRUE
AND disclosure.fec_fitem_sched_a.contbr_city IN (...) AND disclosure.fec_fitem_sched_a.contbr_st IN (...)
) AS anon_1;

[fecjjeng]

To simplified the tests, I only used one field (sub_id) to replace to lengthy list of selection.
SELECT sub_id
FROM disclosure.fec_fitem_sched_a
WHERE disclosure.fec_fitem_sched_a.is_individual = TRUE
AND AND disclosure.fec_fitem_sched_a.contbr_city IN (...) AND disclosure.fec_fitem_sched_a.contbr_st IN (...):

The drill down from pgBadger, the top 3 example from this queries use the combination of NY/NEW YORK,

explain analyze
SELECT sub_id
FROM disclosure.fec_fitem_sched_a
WHERE disclosure.fec_fitem_sched_a.is_individual = TRUE
AND disclosure.fec_fitem_sched_a.contbr_city IN ('NEW YORK') AND disclosure.fec_fitem_sched_a.contbr_st IN ('NY');

When tested in STG database at a low traffic time. It actually took 29 minutes to run, with following execution plan:
"Append (cost=0.00..3065156.96 rows=456275 width=12) (actual time=0.024..1748119.447 rows=6137810 loops=1)"
" -> Seq Scan on fec_fitem_sched_a (cost=0.00..0.00 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=1)"
" Filter: (is_individual AND ((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" -> Index Scan using idx_sched_a_1975_1976_contbr_st_dt_sub_id on fec_fitem_sched_a_1975_1976 (cost=0.41..8.43 rows=1 width=12) (actual time=0.020..0.021 rows=1 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" Filter: (is_individual AND ((contbr_city)::text = 'NEW YORK'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1977_1978 (cost=1162.36..2301.39 rows=210 width=12) (actual time=4.861..14.416 rows=6504 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 637"
" Heap Blocks: exact=4894"
" -> BitmapAnd (cost=1162.36..1162.36 rows=309 width=0) (actual time=4.164..4.164 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1977_1978_contbr_city_amt_sub_id (cost=0.00..393.02 rows=7013 width=0) (actual time=1.247..1.247 rows=7145 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_1977_1978_contbr_st_amt_sub_id (cost=0.00..768.98 rows=15541 width=0) (actual time=2.486..2.486 rows=15721 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1979_1980 (cost=2166.16..5143.60 rows=581 width=12) (actual time=9.054..28.228 rows=14210 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 544"
" Heap Blocks: exact=9434"
" -> BitmapAnd (cost=2166.16..2166.16 rows=845 width=0) (actual time=7.639..7.639 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1979_1980_contbr_city_amt_sub_id (cost=0.00..808.21 rows=14371 width=0) (actual time=2.241..2.241 rows=14805 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_1979_1980_contbr_st_amt_sub_id (cost=0.00..1357.41 rows=27332 width=0) (actual time=4.564..4.564 rows=28056 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1981_1982 (cost=1179.84..3112.95 rows=440 width=12) (actual time=4.258..14.868 rows=8187 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 100"
" Heap Blocks: exact=5103"
" -> BitmapAnd (cost=1179.84..1179.84 rows=568 width=0) (actual time=3.540..3.540 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1981_1982_contbr_city_amt_sub_id (cost=0.00..471.41 rows=8398 width=0) (actual time=1.148..1.148 rows=8293 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_1981_1982_contbr_st_amt_sub_id (cost=0.00..707.96 rows=14339 width=0) (actual time=1.977..1.977 rows=14391 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1983_1984 (cost=2145.90..6760.57 rows=1325 width=12) (actual time=8.330..26.328 rows=14327 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 84"
" Heap Blocks: exact=8249"
" -> BitmapAnd (cost=2145.90..2145.90 rows=1465 width=0) (actual time=7.100..7.100 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1983_1984_contbr_city_amt_sub_id (cost=0.00..827.00 rows=14744 width=0) (actual time=2.161..2.161 rows=14431 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_1983_1984_contbr_st_amt_sub_id (cost=0.00..1317.99 rows=27409 width=0) (actual time=4.219..4.219 rows=27636 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1985_1986 (cost=2134.31..6472.95 rows=1262 width=12) (actual time=8.363..25.502 rows=13876 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 31"
" Heap Blocks: exact=8074"
" -> BitmapAnd (cost=2134.31..2134.31 rows=1354 width=0) (actual time=7.142..7.142 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1985_1986_contbr_city_amt_sub_id (cost=0.00..762.29 rows=13582 width=0) (actual time=2.086..2.086 rows=13954 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_1985_1986_contbr_st_amt_sub_id (cost=0.00..1371.14 rows=28629 width=0) (actual time=4.345..4.346 rows=28835 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1987_1988 (cost=3692.32..12129.97 rows=2631 width=12) (actual time=15.117..47.214 rows=25339 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 10"
" Heap Blocks: exact=14588"
" -> BitmapAnd (cost=3692.32..3692.32 rows=2770 width=0) (actual time=12.767..12.767 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1987_1988_contbr_city_amt_sub_id (cost=0.00..1413.02 rows=25147 width=0) (actual time=3.975..3.975 rows=25399 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_1987_1988_contbr_st_amt_sub_id (cost=0.00..2277.73 rows=47641 width=0) (actual time=7.507..7.507 rows=47947 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1989_1990 (cost=3304.71..9174.21 rows=1692 width=12) (actual time=13.806..41.229 rows=22010 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Heap Blocks: exact=12910"
" -> BitmapAnd (cost=3304.71..3304.71 rows=1760 width=0) (actual time=11.742..11.742 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1989_1990_contbr_city_amt_sub_id (cost=0.00..1219.34 rows=21722 width=0) (actual time=3.508..3.508 rows=22092 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_1989_1990_contbr_st_amt_sub_id (cost=0.00..2084.27 rows=43713 width=0) (actual time=7.054..7.054 rows=44672 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1991_1992 (cost=6443.13..19447.94 rows=3927 width=12) (actual time=27.928..81.782 rows=43253 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 5"
" Heap Blocks: exact=25208"
" -> BitmapAnd (cost=6443.13..6443.13 rows=4051 width=0) (actual time=23.634..23.634 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1991_1992_contbr_city_amt_sub_id (cost=0.00..2332.17 rows=41566 width=0) (actual time=7.277..7.278 rows=43309 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_1991_1992_contbr_st_amt_sub_id (cost=0.00..4108.75 rows=86443 width=0) (actual time=14.065..14.065 rows=86031 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1993_1994 (cost=4688.09..12467.36 rows=2222 width=12) (actual time=19.349..57.846 rows=29577 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 7"
" Heap Blocks: exact=18617"
" -> BitmapAnd (cost=4688.09..4688.09 rows=2289 width=0) (actual time=16.224..16.224 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1993_1994_contbr_city_amt_sub_id (cost=0.00..1736.63 rows=30961 width=0) (actual time=4.779..4.779 rows=29615 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_1993_1994_contbr_st_amt_sub_id (cost=0.00..2950.10 rows=62090 width=0) (actual time=9.776..9.776 rows=60729 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1995_1996 (cost=6942.25..18442.46 rows=3294 width=12) (actual time=30.021..85.883 rows=42192 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 6"
" Heap Blocks: exact=27580"
" -> BitmapAnd (cost=6942.25..6942.25 rows=3380 width=0) (actual time=25.277..25.277 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1995_1996_contbr_city_amt_sub_id (cost=0.00..2503.71 rows=44705 width=0) (actual time=6.941..6.941 rows=42223 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_1995_1996_contbr_st_amt_sub_id (cost=0.00..4436.64 rows=93362 width=0) (actual time=15.760..15.760 rows=90787 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1997_1998 (cost=6456.20..17915.33 rows=3347 width=12) (actual time=26.582..76.020 rows=38822 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 2"
" Heap Blocks: exact=22874"
" -> BitmapAnd (cost=6456.20..6456.20 rows=3448 width=0) (actual time=22.723..22.723 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1997_1998_contbr_city_amt_sub_id (cost=0.00..2193.61 rows=39091 width=0) (actual time=6.310..6.310 rows=38846 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_1997_1998_contbr_st_amt_sub_id (cost=0.00..4260.66 rows=89632 width=0) (actual time=14.146..14.146 rows=88676 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1999_2000 (cost=10211.14..26527.68 rows=4698 width=12) (actual time=44.868..118.311 rows=56094 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 25"
" Heap Blocks: exact=34998"
" -> BitmapAnd (cost=10211.14..10211.14 rows=4801 width=0) (actual time=38.699..38.699 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1999_2000_contbr_city_amt_sub_id (cost=0.00..3024.14 rows=53828 width=0) (actual time=9.345..9.346 rows=56152 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_1999_2000_contbr_st_amt_sub_id (cost=0.00..7184.41 rows=151464 width=0) (actual time=25.810..25.810 rows=152639 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2001_2002 (cost=5602.24..10489.94 rows=1288 width=12) (actual time=22.888..51.566 rows=20609 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 158"
" Heap Blocks: exact=14177"
" -> BitmapAnd (cost=5602.24..5602.24 rows=1323 width=0) (actual time=20.613..20.613 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_2001_2002_contbr_city_amt_sub_id (cost=0.00..1149.04 rows=20348 width=0) (actual time=3.359..3.359 rows=20776 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_2001_2002_contbr_st_amt_sub_id (cost=0.00..4452.31 rows=93851 width=0) (actual time=15.777..15.777 rows=95158 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2003_2004 (cost=30876.45..72256.95 rows=10742 width=12) (actual time=164.789..401.896 rows=160174 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 11978"
" Heap Blocks: exact=109524"
" -> BitmapAnd (cost=30876.45..30876.45 rows=11339 width=0) (actual time=142.606..142.606 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_2003_2004_contbr_city_amt_sub_id (cost=0.00..10091.85 rows=178572 width=0) (actual time=40.006..40.007 rows=172430 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_2003_2004_contbr_st_amt_sub_id (cost=0.00..20778.98 rows=434457 width=0) (actual time=82.624..82.624 rows=428794 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2005_2006 (cost=29095.09..65288.44 rows=9015 width=12) (actual time=145.917..9203.180 rows=151238 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 14665"
" Heap Blocks: exact=101903"
" -> BitmapAnd (cost=29095.09..29095.09 rows=9851 width=0) (actual time=125.719..125.719 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_2005_2006_contbr_city_amt_sub_id (cost=0.00..8906.29 rows=157031 width=0) (actual time=30.901..30.901 rows=166203 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_2005_2006_contbr_st_amt_sub_id (cost=0.00..20184.04 rows=423398 width=0) (actual time=79.506..79.507 rows=430116 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2007_2008 (cost=63288.02..156196.94 rows=24767 width=12) (actual time=3543.308..122023.913 rows=343262 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 20030"
" Heap Blocks: exact=280245"
" -> BitmapAnd (cost=63288.02..63288.02 rows=26216 width=0) (actual time=3466.122..3466.122 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_2007_2008_contbr_city_amt_sub_id (cost=0.00..20663.46 rows=367853 width=0) (actual time=1049.110..1049.110 rows=363923 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_2007_2008_contbr_st_amt_sub_id (cost=0.00..42611.93 rows=907116 width=0) (actual time=2341.706..2341.706 rows=907180 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2009_2010 (cost=40523.75..90672.16 rows=12752 width=12) (actual time=2210.023..72634.385 rows=198498 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 15921"
" Heap Blocks: exact=164002"
" -> BitmapAnd (cost=40523.75..40523.75 rows=13806 width=0) (actual time=2172.206..2172.206 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_2009_2010_contbr_city_amt_sub_id (cost=0.00..12589.31 rows=223034 width=0) (actual time=623.013..623.014 rows=214678 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_2009_2010_contbr_st_amt_sub_id (cost=0.00..27927.81 rows=590833 width=0) (actual time=1509.888..1509.888 rows=582054 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2011_2012 (cost=99278.73..230579.75 rows=34364 width=12) (actual time=6021.648..179026.601 rows=478924 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Rows Removed by Index Recheck: 253"
" Filter: is_individual"
" Rows Removed by Filter: 28856"
" Heap Blocks: exact=415488"
" -> BitmapAnd (cost=99278.73..99278.73 rows=36595 width=0) (actual time=5894.809..5894.809 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_2011_2012_contbr_city_amt_sub_id (cost=0.00..29083.69 rows=516151 width=0) (actual time=1624.618..1624.618 rows=508902 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_2011_2012_contbr_st_amt_sub_id (cost=0.00..70177.61 rows=1487606 width=0) (actual time=4130.251..4130.251 rows=1469060 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2013_2014 (cost=93573.88..203570.40 rows=27443 width=12) (actual time=5732.123..166623.744 rows=452180 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Rows Removed by Index Recheck: 256"
" Filter: is_individual"
" Rows Removed by Filter: 22173"
" Heap Blocks: exact=388881"
" -> BitmapAnd (cost=93573.88..93573.88 rows=30248 width=0) (actual time=5619.090..5619.090 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_2013_2014_contbr_city_amt_sub_id (cost=0.00..25666.72 rows=465355 width=0) (actual time=1449.830..1449.830 rows=475399 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_2013_2014_contbr_st_amt_sub_id (cost=0.00..67893.18 rows=1482749 width=0) (actual time=4044.288..4044.288 rows=1539896 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2015_2016 (cost=268798.24..614719.31 rows=85955 width=12) (actual time=15437.098..417997.915 rows=1188949 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Rows Removed by Index Recheck: 4216921"
" Filter: is_individual"
" Rows Removed by Filter: 33866"
" Heap Blocks: exact=546293 lossy=441542"
" -> BitmapAnd (cost=268798.24..268798.24 rows=95758 width=0) (actual time=15257.517..15257.517 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_2015_2016_contbr_city_amt_sub_id (cost=0.00..71038.40 rows=1272244 width=0) (actual time=3828.951..3828.951 rows=1226511 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_2015_2016_contbr_st_amt_sub_id (cost=0.00..197716.62 rows=4175474 width=0) (actual time=11259.222..11259.222 rows=4109320 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2017_2018 (cost=533626.88..1291314.26 rows=195176 width=12) (actual time=32163.079..655491.807 rows=2413320 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Rows Removed by Index Recheck: 8286280"
" Filter: is_individual"
" Rows Removed by Filter: 68645"
" Heap Blocks: exact=648210 lossy=876941"
" -> BitmapAnd (cost=533626.88..533626.88 rows=212125 width=0) (actual time=31925.900..31925.900 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_2017_2018_contbr_city_amt_sub_id (cost=0.00..150261.50 rows=2495591 width=0) (actual time=10030.708..10030.708 rows=2549097 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_2017_2018_contbr_st_amt_sub_id (cost=0.00..383267.54 rows=7584930 width=0) (actual time=21689.482..21689.482 rows=7789964 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2019_2020 (cost=77958.77..190163.97 rows=29142 width=12) (actual time=7603.208..120346.387 rows=416264 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 14067"
" Heap Blocks: exact=278271"
" -> BitmapAnd (cost=77958.77..77958.77 rows=31435 width=0) (actual time=7534.300..7534.300 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_2019_2020_contbr_city_amt_sub_id (cost=0.00..19014.82 rows=425368 width=0) (actual time=1979.646..1979.647 rows=452768 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_2019_2020_contbr_st_amt_sub_id (cost=0.00..58929.13 rows=1475809 width=0) (actual time=5477.856..5477.856 rows=1593491 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
"Planning time: 4.440 ms"
"Execution time: 1750113.631 ms"

[fecjjeng]

As a comparison, MD/POTOMAC combination is also tested. It only took 1 min 08 sec to execute, with all other condition the same:
"Append (cost=0.00..122572.85 rows=1111 width=12) (actual time=5.050..67743.278 rows=191049 loops=1)"
" -> Seq Scan on fec_fitem_sched_a (cost=0.00..0.00 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=1)"
" Filter: (is_individual AND ((contbr_city)::text = 'POTOMAC'::text) AND ((contbr_st)::text = 'MD'::text))"
" -> Index Scan using idx_sched_a_1975_1976_contbr_st_dt_sub_id on fec_fitem_sched_a_1975_1976 (cost=0.41..8.43 rows=1 width=12) (actual time=1.389..1.389 rows=0 loops=1)"
" Index Cond: ((contbr_st)::text = 'MD'::text)"
" Filter: (is_individual AND ((contbr_city)::text = 'POTOMAC'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1977_1978 (cost=4.66..129.87 rows=1 width=12) (actual time=3.658..111.494 rows=262 loops=1)"
" Recheck Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" Filter: (is_individual AND ((contbr_st)::text = 'MD'::text))"
" Rows Removed by Filter: 19"
" Heap Blocks: exact=267"
" -> Bitmap Index Scan on idx_sched_a_1977_1978_contbr_city_dt_sub_id (cost=0.00..4.66 rows=32 width=0) (actual time=3.088..3.088 rows=281 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1979_1980 (cost=4.73..165.23 rows=1 width=12) (actual time=4.741..248.629 rows=495 loops=1)"
" Recheck Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" Filter: (is_individual AND ((contbr_st)::text = 'MD'::text))"
" Rows Removed by Filter: 15"
" Heap Blocks: exact=483"
" -> Bitmap Index Scan on idx_sched_a_1979_1980_contbr_city_dt_sub_id (cost=0.00..4.73 rows=41 width=0) (actual time=4.024..4.024 rows=510 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1981_1982 (cost=4.59..94.34 rows=1 width=12) (actual time=3.722..126.062 rows=316 loops=1)"
" Recheck Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" Filter: (is_individual AND ((contbr_st)::text = 'MD'::text))"
" Rows Removed by Filter: 1"
" Heap Blocks: exact=296"
" -> Bitmap Index Scan on idx_sched_a_1981_1982_contbr_city_dt_sub_id (cost=0.00..4.59 rows=23 width=0) (actual time=3.212..3.212 rows=317 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1983_1984 (cost=246.21..277.87 rows=7 width=12) (actual time=17.513..215.710 rows=519 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'POTOMAC'::text) AND ((contbr_st)::text = 'MD'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 1"
" Heap Blocks: exact=493"
" -> BitmapAnd (cost=246.21..246.21 rows=8 width=0) (actual time=16.979..16.979 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1983_1984_contbr_city_amt_sub_id (cost=0.00..28.07 rows=487 width=0) (actual time=3.834..3.834 rows=521 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Index Scan on idx_sched_a_1983_1984_contbr_st_amt_sub_id (cost=0.00..217.88 rows=4461 width=0) (actual time=13.058..13.058 rows=4446 loops=1)"
" Index Cond: ((contbr_st)::text = 'MD'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1985_1986 (cost=378.90..445.76 rows=16 width=12) (actual time=24.917..287.972 rows=734 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'POTOMAC'::text) AND ((contbr_st)::text = 'MD'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 7"
" Heap Blocks: exact=648"
" -> BitmapAnd (cost=378.90..378.90 rows=17 width=0) (actual time=24.358..24.358 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1985_1986_contbr_city_amt_sub_id (cost=0.00..41.52 rows=680 width=0) (actual time=4.672..4.673 rows=741 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Index Scan on idx_sched_a_1985_1986_contbr_st_amt_sub_id (cost=0.00..337.12 rows=7026 width=0) (actual time=19.572..19.572 rows=7215 loops=1)"
" Index Cond: ((contbr_st)::text = 'MD'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1987_1988 (cost=506.22..608.43 rows=25 width=12) (actual time=29.121..458.682 rows=1092 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'POTOMAC'::text) AND ((contbr_st)::text = 'MD'::text))"
" Filter: is_individual"
" Heap Blocks: exact=1009"
" -> BitmapAnd (cost=506.22..506.22 rows=26 width=0) (actual time=28.630..28.630 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1987_1988_contbr_city_amt_sub_id (cost=0.00..73.94 rows=1269 width=0) (actual time=5.531..5.531 rows=1097 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Index Scan on idx_sched_a_1987_1988_contbr_st_amt_sub_id (cost=0.00..432.01 rows=9012 width=0) (actual time=22.997..22.997 rows=8889 loops=1)"
" Index Cond: ((contbr_st)::text = 'MD'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1989_1990 (cost=686.98..805.02 rows=29 width=12) (actual time=41.155..529.684 rows=1153 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'POTOMAC'::text) AND ((contbr_st)::text = 'MD'::text))"
" Filter: is_individual"
" Heap Blocks: exact=1076"
" -> BitmapAnd (cost=686.98..686.98 rows=30 width=0) (actual time=40.672..40.672 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1989_1990_contbr_city_amt_sub_id (cost=0.00..73.87 rows=1259 width=0) (actual time=4.941..4.941 rows=1161 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Index Scan on idx_sched_a_1989_1990_contbr_st_amt_sub_id (cost=0.00..612.85 rows=12857 width=0) (actual time=35.625..35.625 rows=12246 loops=1)"
" Index Cond: ((contbr_st)::text = 'MD'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1991_1992 (cost=1132.50..1309.75 rows=44 width=12) (actual time=58.762..755.357 rows=1716 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'POTOMAC'::text) AND ((contbr_st)::text = 'MD'::text))"
" Filter: is_individual"
" Heap Blocks: exact=1613"
" -> BitmapAnd (cost=1132.50..1132.50 rows=45 width=0) (actual time=58.164..58.165 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1991_1992_contbr_city_amt_sub_id (cost=0.00..106.39 rows=1862 width=0) (actual time=7.638..7.638 rows=1724 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Index Scan on idx_sched_a_1991_1992_contbr_st_amt_sub_id (cost=0.00..1025.84 rows=21522 width=0) (actual time=50.265..50.265 rows=20928 loops=1)"
" Index Cond: ((contbr_st)::text = 'MD'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1993_1994 (cost=961.56..1095.74 rows=33 width=12) (actual time=53.781..742.439 rows=1792 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'POTOMAC'::text) AND ((contbr_st)::text = 'MD'::text))"
" Filter: is_individual"
" Heap Blocks: exact=1577"
" -> BitmapAnd (cost=961.56..961.56 rows=34 width=0) (actual time=53.189..53.189 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1993_1994_contbr_city_amt_sub_id (cost=0.00..92.19 rows=1568 width=0) (actual time=8.428..8.428 rows=1800 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Index Scan on idx_sched_a_1993_1994_contbr_st_amt_sub_id (cost=0.00..869.11 rows=18224 width=0) (actual time=44.609..44.609 rows=18787 loops=1)"
" Index Cond: ((contbr_st)::text = 'MD'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1995_1996 (cost=1312.59..1494.28 rows=44 width=12) (actual time=77.605..1057.316 rows=2538 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'POTOMAC'::text) AND ((contbr_st)::text = 'MD'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 1"
" Heap Blocks: exact=2383"
" -> BitmapAnd (cost=1312.59..1312.59 rows=46 width=0) (actual time=76.932..76.932 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1995_1996_contbr_city_amt_sub_id (cost=0.00..129.41 rows=2264 width=0) (actual time=9.807..9.807 rows=2544 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Index Scan on idx_sched_a_1995_1996_contbr_st_amt_sub_id (cost=0.00..1182.91 rows=24864 width=0) (actual time=66.670..66.670 rows=26434 loops=1)"
" Index Cond: ((contbr_st)::text = 'MD'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1997_1998 (cost=1079.78..1237.68 rows=39 width=12) (actual time=49.188..737.377 rows=1822 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'POTOMAC'::text) AND ((contbr_st)::text = 'MD'::text))"
" Filter: is_individual"
" Heap Blocks: exact=1638"
" -> BitmapAnd (cost=1079.78..1079.78 rows=40 width=0) (actual time=48.653..48.654 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1997_1998_contbr_city_amt_sub_id (cost=0.00..115.42 rows=1999 width=0) (actual time=7.132..7.132 rows=1828 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Index Scan on idx_sched_a_1997_1998_contbr_st_amt_sub_id (cost=0.00..964.10 rows=20223 width=0) (actual time=41.335..41.335 rows=19378 loops=1)"
" Index Cond: ((contbr_st)::text = 'MD'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1999_2000 (cost=1844.53..2065.96 rows=55 width=12) (actual time=101.607..1251.289 rows=3089 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'POTOMAC'::text) AND ((contbr_st)::text = 'MD'::text))"
" Filter: is_individual"
" Heap Blocks: exact=2881"
" -> BitmapAnd (cost=1844.53..1844.53 rows=56 width=0) (actual time=100.807..100.807 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_1999_2000_contbr_city_amt_sub_id (cost=0.00..152.38 rows=2660 width=0) (actual time=11.293..11.293 rows=3096 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Index Scan on idx_sched_a_1999_2000_contbr_st_amt_sub_id (cost=0.00..1691.87 rows=35659 width=0) (actual time=89.147..89.148 rows=38128 loops=1)"
" Index Cond: ((contbr_st)::text = 'MD'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2001_2002 (cost=9.39..511.18 rows=3 width=12) (actual time=7.608..397.209 rows=1023 loops=1)"
" Recheck Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" Filter: (is_individual AND ((contbr_st)::text = 'MD'::text))"
" Rows Removed by Filter: 2"
" Heap Blocks: exact=975"
" -> Bitmap Index Scan on idx_sched_a_2001_2002_contbr_city_amt_sub_id (cost=0.00..9.39 rows=128 width=0) (actual time=7.095..7.096 rows=1025 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2003_2004 (cost=8856.70..9820.13 rows=230 width=12) (actual time=442.489..3654.409 rows=9385 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'POTOMAC'::text) AND ((contbr_st)::text = 'MD'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 381"
" Heap Blocks: exact=8424"
" -> BitmapAnd (cost=8856.70..8856.70 rows=243 width=0) (actual time=440.759..440.759 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_2003_2004_contbr_city_amt_sub_id (cost=0.00..544.40 rows=9579 width=0) (actual time=28.798..28.798 rows=9798 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Index Scan on idx_sched_a_2003_2004_contbr_st_amt_sub_id (cost=0.00..8311.93 rows=173783 width=0) (actual time=410.329..410.329 rows=174489 loops=1)"
" Index Cond: ((contbr_st)::text = 'MD'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2005_2006 (cost=46.33..3064.91 rows=19 width=12) (actual time=28.723..2669.559 rows=8351 loops=1)"
" Recheck Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" Filter: (is_individual AND ((contbr_st)::text = 'MD'::text))"
" Rows Removed by Filter: 588"
" Heap Blocks: exact=7665"
" -> Bitmap Index Scan on idx_sched_a_2005_2006_contbr_city_amt_sub_id (cost=0.00..46.32 rows=769 width=0) (actual time=26.952..26.952 rows=8939 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2007_2008 (cost=83.08..5566.87 rows=36 width=12) (actual time=49.181..6134.141 rows=14457 loops=1)"
" Recheck Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" Filter: (is_individual AND ((contbr_st)::text = 'MD'::text))"
" Rows Removed by Filter: 669"
" Heap Blocks: exact=14761"
" -> Bitmap Index Scan on idx_sched_a_2007_2008_contbr_city_amt_sub_id (cost=0.00..83.07 rows=1401 width=0) (actual time=46.076..46.077 rows=15126 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2009_2010 (cost=60.40..4149.19 rows=22 width=12) (actual time=33.361..3938.921 rows=9010 loops=1)"
" Recheck Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" Filter: (is_individual AND ((contbr_st)::text = 'MD'::text))"
" Rows Removed by Filter: 748"
" Heap Blocks: exact=9366"
" -> Bitmap Index Scan on idx_sched_a_2009_2010_contbr_city_amt_sub_id (cost=0.00..60.39 rows=1044 width=0) (actual time=31.037..31.038 rows=9758 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2011_2012 (cost=129.33..8875.60 rows=54 width=12) (actual time=63.237..7999.271 rows=17573 loops=1)"
" Recheck Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" Filter: (is_individual AND ((contbr_st)::text = 'MD'::text))"
" Rows Removed by Filter: 1176"
" Heap Blocks: exact=18205"
" -> Bitmap Index Scan on idx_sched_a_2011_2012_contbr_city_amt_sub_id (cost=0.00..129.32 rows=2234 width=0) (actual time=58.743..58.743 rows=18749 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2013_2014 (cost=130.06..9255.75 rows=45 width=12) (actual time=59.264..6728.969 rows=14422 loops=1)"
" Recheck Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" Filter: (is_individual AND ((contbr_st)::text = 'MD'::text))"
" Rows Removed by Filter: 1229"
" Heap Blocks: exact=14968"
" -> Bitmap Index Scan on idx_sched_a_2013_2014_contbr_city_amt_sub_id (cost=0.00..130.04 rows=2331 width=0) (actual time=56.122..56.122 rows=15651 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2015_2016 (cost=323.76..22896.03 rows=106 width=12) (actual time=110.724..13288.513 rows=31684 loops=1)"
" Recheck Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" Filter: (is_individual AND ((contbr_st)::text = 'MD'::text))"
" Rows Removed by Filter: 1374"
" Heap Blocks: exact=30845"
" -> Bitmap Index Scan on idx_sched_a_2015_2016_contbr_city_amt_sub_id (cost=0.00..323.74 rows=5756 width=0) (actual time=104.769..104.769 rows=33110 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2017_2018 (cost=608.26..40175.17 rows=251 width=12) (actual time=253.576..13482.236 rows=57249 loops=1)"
" Recheck Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" Filter: (is_individual AND ((contbr_st)::text = 'MD'::text))"
" Rows Removed by Filter: 1807"
" Heap Blocks: exact=38942"
" -> Bitmap Index Scan on idx_sched_a_2017_2018_contbr_city_amt_sub_id (cost=0.00..608.20 rows=10084 width=0) (actual time=246.268..246.268 rows=63055 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2019_2020 (cost=100.74..8519.64 rows=48 width=12) (actual time=68.710..2795.650 rows=12367 loops=1)"
" Recheck Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" Filter: (is_individual AND ((contbr_st)::text = 'MD'::text))"
" Rows Removed by Filter: 535"
" Heap Blocks: exact=8007"
" -> Bitmap Index Scan on idx_sched_a_2019_2020_contbr_city_amt_sub_id (cost=0.00..100.73 rows=2156 width=0) (actual time=67.114..67.114 rows=14221 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
"Planning time: 4.890 ms"
"Execution time: 67814.773 ms"

[fecjjeng]

Both execution reveals that even with the same parameters input, with different cycles, database optimizer choose slightly different execution plan, depending on the size of the partitioned table and the data distribution. And with the same cycle partition table, different input parameters would resulted in different execution plan.

[fecjjeng]

To further simplified the testing condition, only 2019_2020 partition is demonstrated:
The data distribution for these two parameter is examined, the data distribution is normal, not seriously screwed.
select contbr_st, contbr_city, count(), (count()* 100::numeric/20019540)
from disclosure.fec_fitem_sched_a_2019_2020
group by contbr_st, contbr_city
order by count(*) desc;

"MA";"SOMERVILLE";489036;2.4427933908571326
"MA";"WEST SOMERVILLE";466143;2.3284401140086136
"NY";"NEW YORK";432581;2.1607939043554447
......
"OR";"CORVALLIS";12963;0.06475173755241129417
"MD";"POTOMAC";12877;0.06432215725236443994
"NY";"QUEENS";12871;0.06429218653375651988
.......

[fecjjeng]

The statistics of this table had been collected via autoanalysis/autovacuum by database engine. It is not stale.

[fecjjeng]

"Bitmap Heap Scan on fec_fitem_sched_a_2019_2020 (cost=78764.27..193026.87 rows=29676 width=12) (actual time=8089.746..121598.635 rows=418497 loops=1)"
" Recheck Cond: (((contbr_city)::text = 'NEW YORK'::text) AND ((contbr_st)::text = 'NY'::text))"
" Filter: is_individual"
" Rows Removed by Filter: 14084"
" Heap Blocks: exact=280695"
" -> BitmapAnd (cost=78764.27..78764.27 rows=32011 width=0) (actual time=8020.610..8020.610 rows=0 loops=1)"
" -> Bitmap Index Scan on idx_sched_a_2019_2020_contbr_city_amt_sub_id (cost=0.00..19245.27 rows=433161 width=0) (actual time=2096.886..2096.887 rows=458115 loops=1)"
" Index Cond: ((contbr_city)::text = 'NEW YORK'::text)"
" -> Bitmap Index Scan on idx_sched_a_2019_2020_contbr_st_amt_sub_id (cost=0.00..59503.91 rows=1502846 width=0) (actual time=5843.144..5843.144 rows=1611063 loops=1)"
" Index Cond: ((contbr_st)::text = 'NY'::text)"
"Planning time: 31.159 ms"
"Execution time: 121718.507 ms"
-- 2'01"

[fecjjeng]

"Bitmap Heap Scan on fec_fitem_sched_a_2019_2020 (cost=101.04..8672.32 rows=49 width=12) (actual time=3.168..21.151 rows=12421 loops=1)"
" Recheck Cond: ((contbr_city)::text = 'POTOMAC'::text)"
" Filter: (is_individual AND ((contbr_st)::text = 'MD'::text))"
" Rows Removed by Filter: 535"
" Heap Blocks: exact=8070"
" -> Bitmap Index Scan on idx_sched_a_2019_2020_contbr_city_amt_sub_id (cost=0.00..101.03 rows=2195 width=0) (actual time=1.939..1.939 rows=14316 loops=1)"
" Index Cond: ((contbr_city)::text = 'POTOMAC'::text)"
"Planning time: 0.227 ms"
"Execution time: 23.622 ms"
-- 122msec

Both columns contbr_city, contbr_st are indexed. depends on the input parameters, optimizer choose at least one of the indexes is used, as expected.

[fecjjeng]

Also considered if work_mem limit the speed.
Bitmaps can either store a bitmap of rows, or if that becomes too large to fit in work_mem it can "go lossy" by storing a bitmap of blocks. If it goes lossy, then the Heap Scan must recheck every row in every lossy block which it visits. However, the plan in this case shows "Heap Blocks: exact" indicated work_mem is not the limit factor. Also, our local postgresql database in DC4, which has much smaller work_mem, indeed shows both exact/lossy, does not slowdown the query return.

Expression-based index is not relevant for this query.

[fecjjeng]

@hcaofec had reviewed the comments listed above. We agreed we can close this ticket.