Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Change schedule_a​ default sort by contribution_receipt_date DESC (reminder Jul 20) #4402

Closed
5 tasks done
fec-jli opened this issue Jun 5, 2020 · 3 comments · Fixed by #4427 or #4501
Closed
5 tasks done

Change schedule_a​ default sort by contribution_receipt_date DESC (reminder Jul 20) #4402

fec-jli opened this issue Jun 5, 2020 · 3 comments · Fixed by #4427 or #4501
Assignees
@fec-jli
Labels
Technical debt Work: Back-end
Milestone
Sprint 12.6

Comments

@fec-jli
Copy link
Contributor

fec-jli commented Jun 5, 2020 •
edited

Currently for /schedules​/schedule_a​/ endpoint, the default sorting "contribution_receipt_date ASC and NULLS last". But SB and SE are sorted DESC by default. Website always has DESC by default for SA, SB and SE.

Completion criteria:

Screen Shot 2020-06-05 at 5 15 25 PM
@fec-jli fec-jli added this to the Sprint 12.6 milestone Jun 5, 2020
@dzhang-fec
Copy link
Contributor

dzhang-fec commented Jun 17, 2020 •
edited

Per @fec-jli I did the following research for your review

Summary:

1)Probelem: the DESC soring is too expensive to our sites (Major performance bottleneck) i.e., it is fast enoughwithout "ORDER BY" sorting
2)I have tried to have added a DESC index CREATE INDEX idx_sched_a_2019_2020_test_rcpt_dt_sub_id, but the DB is still "Not" use DESC index in our real sql by tests.
3)even I changed the ORDER by to "ASC" in real sql, it is still super slow.

4)for non-real pure sql, ASC index still has "Index Scan Backward" for DESC sorting but not in our complex sql (so index recreation is nessary but it needs more efforts
5)Currently, the postgres 11.6 opmizor isn't smart enough for our complex sql cases (too many indexex and complex sql), so we will have to change the query to get it to work faster, as in other ticket, e.g., use union all and add partitions filter, etc
6)To change indexes from ASC to DESC will work for simpler sql, but not good enough to the complex real-sql.

Test 1 group (for "simple" one table one sql on sorting):
Create table a1 a select * from disclosure.fec_fitem_sched_a_2018_2020
to verify sorting can use index. (result yes, and ASC still working for DESC sorting)
drop index public.a1_ind_desc
create index a1_ind on a1(pg_date)
create index a1_ind_desc2 on a1(pg_date desc)

1.explain (select * from a1 order by pg_date desc limit 10)
"Limit (cost=7273464.34..7273464.36 rows=10 width=1348)"
" -> Sort (cost=7273464.34..7422947.56 rows=59793288 width=1348)"
" Sort Key: pg_date DESC"
" -> Seq Scan on a1 (cost=0.00..5981352.88 rows=59793288 width=1348)"

2.explain (select * from a1 order by pg_date desc limit 10)
"Limit (cost=0.56..1.69 rows=10 width=1348)"
" -> Index Scan Backward using a1_ind on a1 (cost=0.56..6714477.84 rows=59747980 width=1348)"
NOTE: This is ASC index can be used for DESC sorting

  1. explain (select * from a1 order by pg_date desc limit 10)
    "Limit (cost=0.56..1.69 rows=10 width=1348)"
    " -> Index Scan using a1_ind_desc2 on a1 (cost=0.56..6714477.84 rows=59747980 width=1348)"
    NOTE: DESC sorting still use DESC index.

Test2 group: with real sql and indexes we have

----sorting by our real sqls
Sql:
SELECT * FROM disclosure.fec_fitem_sched_a LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_1 ON disclosure.fec_fitem_sched_a.cmte_id = ofec_committee_history_mv_1.committee_id AND disclosure.fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_1.cycle LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_2 ON disclosure.fec_fitem_sched_a.clean_contbr_id = ofec_committee_history_mv_2.committee_id AND disclosure.fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_2.cycle
WHERE disclosure.fec_fitem_sched_a.is_individual = true
AND disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('ActBlue:*')
ORDER BY disclosure.fec_fitem_sched_a.contb_receipt_dt DESC, disclosure.fec_fitem_sched_a.sub_id DESC
LIMIT 30
took 19+mins

Analysis: the DESC soring is too expensive (bottleneck). Sort (cost=31550196.55..31595998.39 rows=18320737 width=2967)"

Sql:
explain (SELECT * FROM disclosure.fec_fitem_sched_a LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_1 ON disclosure.fec_fitem_sched_a.cmte_id = ofec_committee_history_mv_1.committee_id AND disclosure.fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_1.cycle LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_2 ON disclosure.fec_fitem_sched_a.clean_contbr_id = ofec_committee_history_mv_2.committee_id AND disclosure.fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_2.cycle
WHERE disclosure.fec_fitem_sched_a.is_individual = true
AND disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('ActBlue:*')
ORDER BY disclosure.fec_fitem_sched_a.contb_receipt_dt DESC, disclosure.fec_fitem_sched_a.sub_id DESC
LIMIT 30)
took 19+mins

"Limit (cost=31550196.55..31550196.62 rows=30 width=2967)"
" -> Sort (cost=31550196.55..31595998.39 rows=18320737 width=2967)"
" Sort Key: fec_fitem_sched_a.contb_receipt_dt DESC, fec_fitem_sched_a.sub_id DESC"
" -> Gather (cost=123449.05..31009103.60 rows=18320737 width=2967)"
" Workers Planned: 2"
" -> Hash Left Join (cost=122449.05..29176029.90 rows=7633640 width=2967)"
" Hash Cond: (((fec_fitem_sched_a.cmte_id)::text = (ofec_committee_history_mv_1.committee_id)::text) AND (fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_1.cycle))"
" -> Hash Left Join (cost=61224.53..23480327.53 rows=7633640 width=2195)"
" Hash Cond: (((fec_fitem_sched_a.clean_contbr_id)::text = (ofec_committee_history_mv_2.committee_id)::text) AND (fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_2.cycle))"
" -> Append (cost=0.00..19214628.89 rows=7633996 width=1445)"
" -> Parallel Seq Scan on fec_fitem_sched_a (cost=0.00..0.00 rows=1 width=5707)"
" Filter: (is_individual AND (contributor_name_text @@ to_tsquery('ActBlue:'::text)))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1975_1976 (cost=10.25..12.41 rows=1 width=3657)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1975_1976_contrib_name_text_amt_sub_id (cost=0.00..10.25 rows=1 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1977_1978 (cost=12.61..111.54 rows=19 width=3540)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1977_1978_contrib_name_text_amt_sub_id (cost=0.00..12.60 rows=47 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1979_1980 (cost=14.73..145.21 rows=25 width=3703)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1979_1980_contrib_name_text_amt_sub_id (cost=0.00..14.71 rows=62 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1981_1982 (cost=12.49..79.75 rows=15 width=3695)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1981_1982_contrib_name_text_amt_sub_id (cost=0.00..12.49 rows=32 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1983_1984 (cost=12.53..90.41 rows=19 width=3695)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1983_1984_contrib_name_text_amt_sub_id (cost=0.00..12.53 rows=37 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1985_1986 (cost=12.55..92.55 rows=21 width=3696)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1985_1986_contrib_name_text_amt_sub_id (cost=0.00..12.54 rows=38 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1987_1988 (cost=14.70..136.77 rows=32 width=3670)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1987_1988_contrib_name_text_amt_sub_id (cost=0.00..14.68 rows=58 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1989_1990 (cost=14.81..166.38 rows=41 width=3695)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1989_1990_contrib_name_text_amt_sub_id (cost=0.00..14.79 rows=72 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1991_1992 (cost=15.17..263.53 rows=68 width=3691)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1991_1992_contrib_name_text_amt_sub_id (cost=0.00..15.14 rows=118 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1993_1994 (cost=15.01..221.60 rows=56 width=3660)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1993_1994_contrib_name_text_amt_sub_id (cost=0.00..14.98 rows=98 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1995_1996 (cost=15.52..362.82 rows=94 width=3660)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1995_1996_contrib_name_text_amt_sub_id (cost=0.00..15.48 rows=165 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1997_1998 (cost=15.30..299.49 rows=78 width=3663)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1997_1998_contrib_name_text_amt_sub_id (cost=0.00..15.27 rows=135 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1999_2000 (cost=16.00..491.79 rows=130 width=3583)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1999_2000_contrib_name_text_amt_sub_id (cost=0.00..15.95 rows=226 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2001_2002 (cost=15.74..420.16 rows=110 width=2795)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2001_2002_contrib_name_text_amt_sub_id (cost=0.00..15.69 rows=192 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2003_2004 (cost=23.31..1948.81 rows=506 width=1618)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2003_2004_contrib_name_text_amt_sub_id (cost=0.00..23.09 rows=912 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2005_2006 (cost=110.16..21668.44 rows=4251 width=1560)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2005_2006_contrib_name_text_amt_sub_id (cost=0.00..107.61 rows=11115 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2007_2008 (cost=552.01..113887.27 rows=24852 width=1522)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2007_2008_contrib_name_text_dt_sub_id (cost=0.00..537.10 rows=63046 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2009_2010 (cost=611.37..121717.07 rows=27117 width=1541)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2009_2010_contrib_name_text_amt_sub_id (cost=0.00..595.10 rows=70246 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2011_2012 (cost=4044.24..637086.08 rows=173936 width=1440)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2011_2012_contrib_name_text_amt_sub_id (cost=0.00..3939.88 rows=443951 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2013_2014 (cost=10105.12..1281861.99 rows=452289 width=1421)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2013_2014_contrib_name_text_amt_sub_id (cost=0.00..9833.75 rows=1191933 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2015_2016 (cost=38600.30..4055314.79 rows=1697608 width=1483)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2015_2016_contrib_name_text_amt_sub_id (cost=0.00..37581.74 rows=4528465 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2017_2018 (cost=46032.20..5527207.32 rows=2068079 width=1474)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2017_2018_contrib_name_text_amt_sub_id (cost=0.00..44791.35 rows=5399880 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2019_2020 (cost=72472.77..7451042.73 rows=3184648 width=1407)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2019_2020_contrib_name_text_amt_sub_id (cost=0.00..70561.98 rows=8489564 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:*'::text))"
" -> Hash (cost=34597.61..34597.61 rows=242661 width=752)"
" -> Seq Scan on ofec_committee_history_mv ofec_committee_history_mv_2 (cost=0.00..34597.61 rows=242661 width=752)"
" -> Hash (cost=34597.61..34597.61 rows=242661 width=752)"
" -> Seq Scan on ofec_committee_history_mv ofec_committee_history_mv_1 (cost=0.00..34597.61 rows=242661 width=752)"

---without ORDER by super fast (so this is the bottleneck
SELECT * FROM disclosure.fec_fitem_sched_a LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_1 ON disclosure.fec_fitem_sched_a.cmte_id = ofec_committee_history_mv_1.committee_id AND disclosure.fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_1.cycle LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_2 ON disclosure.fec_fitem_sched_a.clean_contbr_id = ofec_committee_history_mv_2.committee_id AND disclosure.fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_2.cycle
WHERE disclosure.fec_fitem_sched_a.is_individual = true
AND disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('ActBlue:*')
LIMIT 30
---super fast: 675 sec
Analysis: ORDER is slowest part

"Limit (cost=0.84..11.31 rows=3 width=2947)"
" -> Nested Loop Left Join (cost=0.84..63935126.61 rows=18320737 width=2947)"
" -> Nested Loop Left Join (cost=0.42..43121420.96 rows=18320737 width=2195)"
" -> Append (cost=0.00..22307715.31 rows=18320737 width=1445)"
" -> Seq Scan on fec_fitem_sched_a (cost=0.00..0.00 rows=1 width=5707)"
" Filter: (is_individual AND (contributor_name_text @@ to_tsquery('ActBlue:'::text)))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1975_1976 (cost=10.25..12.52 rows=1 width=3657)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1975_1976_contrib_name_text_amt_sub_id (cost=0.00..10.25 rows=1 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1977_1978 (cost=12.61..116.62 rows=32 width=3540)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1977_1978_contrib_name_text_amt_sub_id (cost=0.00..12.60 rows=47 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1979_1980 (cost=14.73..151.91 rows=43 width=3703)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1979_1980_contrib_name_text_amt_sub_id (cost=0.00..14.71 rows=62 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1981_1982 (cost=12.49..83.20 rows=25 width=3695)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1981_1982_contrib_name_text_amt_sub_id (cost=0.00..12.49 rows=32 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1983_1984 (cost=12.53..94.40 rows=33 width=3695)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1983_1984_contrib_name_text_amt_sub_id (cost=0.00..12.53 rows=37 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1985_1986 (cost=12.55..96.65 rows=36 width=3696)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1985_1986_contrib_name_text_amt_sub_id (cost=0.00..12.54 rows=38 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1987_1988 (cost=14.70..143.04 rows=55 width=3670)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1987_1988_contrib_name_text_amt_sub_id (cost=0.00..14.68 rows=58 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1989_1990 (cost=14.81..174.16 rows=69 width=3695)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1989_1990_contrib_name_text_amt_sub_id (cost=0.00..14.79 rows=72 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1991_1992 (cost=15.17..276.28 rows=115 width=3691)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1991_1992_contrib_name_text_amt_sub_id (cost=0.00..15.14 rows=118 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1993_1994 (cost=15.01..232.19 rows=95 width=3660)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1993_1994_contrib_name_text_amt_sub_id (cost=0.00..14.98 rows=98 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1995_1996 (cost=15.52..380.65 rows=160 width=3660)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1995_1996_contrib_name_text_amt_sub_id (cost=0.00..15.48 rows=165 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1997_1998 (cost=15.30..314.08 rows=132 width=3663)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1997_1998_contrib_name_text_amt_sub_id (cost=0.00..15.27 rows=135 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_1999_2000 (cost=16.00..516.22 rows=221 width=3583)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1999_2000_contrib_name_text_amt_sub_id (cost=0.00..15.95 rows=226 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2001_2002 (cost=15.74..440.92 rows=187 width=2795)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2001_2002_contrib_name_text_amt_sub_id (cost=0.00..15.69 rows=192 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2003_2004 (cost=23.31..2047.38 rows=860 width=1618)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2003_2004_contrib_name_text_amt_sub_id (cost=0.00..23.09 rows=912 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2005_2006 (cost=110.16..23370.43 rows=10202 width=1560)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2005_2006_contrib_name_text_amt_sub_id (cost=0.00..107.61 rows=11115 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2007_2008 (cost=552.01..123541.19 rows=59644 width=1522)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2007_2008_contrib_name_text_dt_sub_id (cost=0.00..537.10 rows=63046 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2009_2010 (cost=611.37..132473.49 rows=65081 width=1541)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2009_2010_contrib_name_text_amt_sub_id (cost=0.00..595.10 rows=70246 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2011_2012 (cost=4044.24..705066.08 rows=417447 width=1440)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2011_2012_contrib_name_text_amt_sub_id (cost=0.00..3939.88 rows=443951 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2013_2014 (cost=10105.12..1464376.73 rows=1085493 width=1421)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2013_2014_contrib_name_text_amt_sub_id (cost=0.00..9833.75 rows=1191933 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2015_2016 (cost=38600.30..4748735.99 rows=4074260 width=1483)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2015_2016_contrib_name_text_amt_sub_id (cost=0.00..37581.74 rows=4528465 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2017_2018 (cost=46032.20..6354063.95 rows=4963390 width=1474)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2017_2018_contrib_name_text_amt_sub_id (cost=0.00..44791.35 rows=5399880 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Bitmap Heap Scan on fec_fitem_sched_a_2019_2020 (cost=72472.77..8751007.22 rows=7643155 width=1407)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2019_2020_contrib_name_text_amt_sub_id (cost=0.00..70561.98 rows=8489564 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:*'::text))"
" -> Index Scan using idx_ofec_committee_history_mv_cycle_committee_id on ofec_committee_history_mv ofec_committee_history_mv_1 (cost=0.42..1.13 rows=1 width=752)"
" Index Cond: ((fec_fitem_sched_a.two_year_transaction_period = cycle) AND ((fec_fitem_sched_a.cmte_id)::text = (committee_id)::text))"
" -> Index Scan using idx_ofec_committee_history_mv_cycle_committee_id on ofec_committee_history_mv ofec_committee_history_mv_2 (cost=0.42..1.13 rows=1 width=752)"
" Index Cond: ((fec_fitem_sched_a.two_year_transaction_period = cycle) AND ((fec_fitem_sched_a.clean_contbr_id)::text = (committee_id)::text))"

---use ASC in sql (not work
SELECT * FROM disclosure.fec_fitem_sched_a LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_1 ON disclosure.fec_fitem_sched_a.cmte_id = ofec_committee_history_mv_1.committee_id AND disclosure.fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_1.cycle LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_2 ON disclosure.fec_fitem_sched_a.clean_contbr_id = ofec_committee_history_mv_2.committee_id AND disclosure.fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_2.cycle
WHERE disclosure.fec_fitem_sched_a.is_individual = true
AND disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('ActBlue:*')
ORDER BY disclosure.fec_fitem_sched_a.contb_receipt_dt ASC, disclosure.fec_fitem_sched_a.sub_id ASC
LIMIT 30
-------------12+mins very slow (so SORTING is the bottleneck)

"Limit (cost=31550196.55..31550196.62 rows=30 width=2967)"
" -> Sort (cost=31550196.55..31595998.39 rows=18320737 width=2967)"
" Sort Key: fec_fitem_sched_a.contb_receipt_dt, fec_fitem_sched_a.sub_id"
" -> Gather (cost=123449.05..31009103.60 rows=18320737 width=2967)"
" Workers Planned: 2"
" -> Hash Left Join (cost=122449.05..29176029.90 rows=7633640 width=2967)"
" Hash Cond: (((fec_fitem_sched_a.cmte_id)::text = (ofec_committee_history_mv_1.committee_id)::text) AND (fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_1.cycle))"
" -> Hash Left Join (cost=61224.53..23480327.53 rows=7633640 width=2195)"
" Hash Cond: (((fec_fitem_sched_a.clean_contbr_id)::text = (ofec_committee_history_mv_2.committee_id)::text) AND (fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_2.cycle))"
" -> Append (cost=0.00..19214628.89 rows=7633996 width=1445)"
" -> Parallel Seq Scan on fec_fitem_sched_a (cost=0.00..0.00 rows=1 width=5707)"
" Filter: (is_individual AND (contributor_name_text @@ to_tsquery('ActBlue:'::text)))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1975_1976 (cost=10.25..12.41 rows=1 width=3657)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1975_1976_contrib_name_text_amt_sub_id (cost=0.00..10.25 rows=1 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1977_1978 (cost=12.61..111.54 rows=19 width=3540)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1977_1978_contrib_name_text_amt_sub_id (cost=0.00..12.60 rows=47 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1979_1980 (cost=14.73..145.21 rows=25 width=3703)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1979_1980_contrib_name_text_amt_sub_id (cost=0.00..14.71 rows=62 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1981_1982 (cost=12.49..79.75 rows=15 width=3695)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1981_1982_contrib_name_text_amt_sub_id (cost=0.00..12.49 rows=32 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1983_1984 (cost=12.53..90.41 rows=19 width=3695)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1983_1984_contrib_name_text_amt_sub_id (cost=0.00..12.53 rows=37 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1985_1986 (cost=12.55..92.55 rows=21 width=3696)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1985_1986_contrib_name_text_amt_sub_id (cost=0.00..12.54 rows=38 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1987_1988 (cost=14.70..136.77 rows=32 width=3670)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1987_1988_contrib_name_text_amt_sub_id (cost=0.00..14.68 rows=58 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1989_1990 (cost=14.81..166.38 rows=41 width=3695)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1989_1990_contrib_name_text_amt_sub_id (cost=0.00..14.79 rows=72 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1991_1992 (cost=15.17..263.53 rows=68 width=3691)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1991_1992_contrib_name_text_amt_sub_id (cost=0.00..15.14 rows=118 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1993_1994 (cost=15.01..221.60 rows=56 width=3660)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1993_1994_contrib_name_text_amt_sub_id (cost=0.00..14.98 rows=98 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1995_1996 (cost=15.52..362.82 rows=94 width=3660)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1995_1996_contrib_name_text_amt_sub_id (cost=0.00..15.48 rows=165 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1997_1998 (cost=15.30..299.49 rows=78 width=3663)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1997_1998_contrib_name_text_amt_sub_id (cost=0.00..15.27 rows=135 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1999_2000 (cost=16.00..491.79 rows=130 width=3583)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1999_2000_contrib_name_text_amt_sub_id (cost=0.00..15.95 rows=226 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2001_2002 (cost=15.74..420.16 rows=110 width=2795)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2001_2002_contrib_name_text_amt_sub_id (cost=0.00..15.69 rows=192 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2003_2004 (cost=23.31..1948.81 rows=506 width=1618)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2003_2004_contrib_name_text_amt_sub_id (cost=0.00..23.09 rows=912 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2005_2006 (cost=110.16..21668.44 rows=4251 width=1560)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2005_2006_contrib_name_text_amt_sub_id (cost=0.00..107.61 rows=11115 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2007_2008 (cost=552.01..113887.27 rows=24852 width=1522)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2007_2008_contrib_name_text_dt_sub_id (cost=0.00..537.10 rows=63046 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2009_2010 (cost=611.37..121717.07 rows=27117 width=1541)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2009_2010_contrib_name_text_amt_sub_id (cost=0.00..595.10 rows=70246 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2011_2012 (cost=4044.24..637086.08 rows=173936 width=1440)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2011_2012_contrib_name_text_amt_sub_id (cost=0.00..3939.88 rows=443951 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2013_2014 (cost=10105.12..1281861.99 rows=452289 width=1421)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2013_2014_contrib_name_text_amt_sub_id (cost=0.00..9833.75 rows=1191933 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2015_2016 (cost=38600.30..4055314.79 rows=1697608 width=1483)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2015_2016_contrib_name_text_amt_sub_id (cost=0.00..37581.74 rows=4528465 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2017_2018 (cost=46032.20..5527207.32 rows=2068079 width=1474)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2017_2018_contrib_name_text_amt_sub_id (cost=0.00..44791.35 rows=5399880 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2019_2020 (cost=72472.77..7451042.73 rows=3184648 width=1407)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2019_2020_contrib_name_text_amt_sub_id (cost=0.00..70561.98 rows=8489564 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:*'::text))"
" -> Hash (cost=34597.61..34597.61 rows=242661 width=752)"
" -> Seq Scan on ofec_committee_history_mv ofec_committee_history_mv_2 (cost=0.00..34597.61 rows=242661 width=752)"
" -> Hash (cost=34597.61..34597.61 rows=242661 width=752)"
" -> Seq Scan on ofec_committee_history_mv ofec_committee_history_mv_1 (cost=0.00..34597.61 rows=242661 width=752)"

Solutions idea:

----1) add where filter 2019_2020 partitions, see #4414
---Keep DESC
---run 1.897sec, very fast
explain (SELECT * FROM disclosure.fec_fitem_sched_a LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_1 ON disclosure.fec_fitem_sched_a.cmte_id = ofec_committee_history_mv_1.committee_id AND disclosure.fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_1.cycle LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_2 ON disclosure.fec_fitem_sched_a.clean_contbr_id = ofec_committee_history_mv_2.committee_id AND disclosure.fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_2.cycle
WHERE disclosure.fec_fitem_sched_a.two_year_transaction_period IN (2020) AND disclosure.fec_fitem_sched_a.is_individual = true
AND disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('ActBlue:*')
ORDER BY disclosure.fec_fitem_sched_a.contb_receipt_dt DESC, disclosure.fec_fitem_sched_a.sub_id DESC
LIMIT 30)

"Limit (cost=1.43..278.82 rows=30 width=2931)"
" -> Nested Loop Left Join (cost=1.43..70671899.97 rows=7643156 width=2931)"
" -> Nested Loop Left Join (cost=1.01..61946317.88 rows=7643156 width=2159)"
" -> Merge Append (cost=0.59..53220735.79 rows=7643156 width=1407)"
" Sort Key: fec_fitem_sched_a.contb_receipt_dt DESC, fec_fitem_sched_a.sub_id DESC"
" -> Sort (cost=0.01..0.02 rows=1 width=5707)"
" Sort Key: fec_fitem_sched_a.contb_receipt_dt DESC, fec_fitem_sched_a.sub_id DESC"
" -> Seq Scan on fec_fitem_sched_a (cost=0.00..0.00 rows=1 width=5707)"
" Filter: (is_individual AND (two_year_transaction_period = '2020'::numeric) AND (contributor_name_text @@ to_tsquery('ActBlue:'::text)))"
" -> Index Scan Backward using idx_sched_a_2019_2020_two_year_period_dt_sub_id on fec_fitem_sched_a_2019_2020 (cost=0.57..53163412.10 rows=7643155 width=1407)"
" Index Cond: (two_year_transaction_period = '2020'::numeric)"
" Filter: (is_individual AND (contributor_name_text @@ to_tsquery('ActBlue:'::text)))"
" -> Index Scan using idx_ofec_committee_history_mv_cycle_committee_id on ofec_committee_history_mv ofec_committee_history_mv_1 (cost=0.42..1.13 rows=1 width=752)"
" Index Cond: ((fec_fitem_sched_a.two_year_transaction_period = cycle) AND (cycle = '2020'::numeric) AND ((fec_fitem_sched_a.cmte_id)::text = (committee_id)::text))"
" -> Index Scan using idx_ofec_committee_history_mv_cycle_committee_id on ofec_committee_history_mv ofec_committee_history_mv_2 (cost=0.42..1.13 rows=1 width=752)"
" Index Cond: ((fec_fitem_sched_a.two_year_transaction_period = cycle) AND (cycle = '2020'::numeric) AND ((fec_fitem_sched_a.clean_contbr_id)::text = (committee_id)::text))

created an index (not work
CREATE INDEX idx_sched_a_2019_2020_test_rcpt_dt_sub_id
analyze table

ON disclosure.fec_fitem_sched_a_2019_2020 USING btree
( COALESCE(contb_receipt_dt, '9999-12-31'::date::timestamp without time zone) DESC NULLS LAST,
 sub_id DESC  NULLS LAST)
TABLESPACE pg_default;

----not use this index

"Limit (cost=31337107.86..31337107.94 rows=30 width=3013)"
" -> Sort (cost=31337107.86..31382086.36 rows=17991399 width=3013)"
" Sort Key: fec_fitem_sched_a.contb_receipt_dt DESC, fec_fitem_sched_a.sub_id DESC"
" -> Gather (cost=123449.05..30805741.73 rows=17991399 width=3013)"
" Workers Planned: 2"
" -> Hash Left Join (cost=122449.05..29005601.83 rows=7496416 width=3013)"
" Hash Cond: (((fec_fitem_sched_a.cmte_id)::text = (ofec_committee_history_mv_1.committee_id)::text) AND (fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_1.cycle))"
" -> Hash Left Join (cost=61224.53..23322922.52 rows=7496416 width=2241)"
" Hash Cond: (((fec_fitem_sched_a.clean_contbr_id)::text = (ofec_committee_history_mv_2.committee_id)::text) AND (fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_2.cycle))"
" -> Append (cost=0.00..19044534.82 rows=7496772 width=1491)"
" -> Parallel Seq Scan on fec_fitem_sched_a (cost=0.00..0.00 rows=1 width=5707)"
" Filter: (is_individual AND (contributor_name_text @@ to_tsquery('ActBlue:'::text)))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1975_1976 (cost=10.25..12.41 rows=1 width=3657)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1975_1976_contrib_name_text_amt_sub_id (cost=0.00..10.25 rows=1 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1977_1978 (cost=12.61..111.54 rows=19 width=3540)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1977_1978_contrib_name_text_amt_sub_id (cost=0.00..12.60 rows=47 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1979_1980 (cost=14.73..145.21 rows=25 width=3703)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1979_1980_contrib_name_text_amt_sub_id (cost=0.00..14.71 rows=62 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1981_1982 (cost=12.49..79.75 rows=15 width=3695)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1981_1982_contrib_name_text_amt_sub_id (cost=0.00..12.49 rows=32 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1983_1984 (cost=12.53..90.41 rows=19 width=3695)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1983_1984_contrib_name_text_amt_sub_id (cost=0.00..12.53 rows=37 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1985_1986 (cost=12.55..92.55 rows=21 width=3696)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1985_1986_contrib_name_text_amt_sub_id (cost=0.00..12.54 rows=38 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1987_1988 (cost=14.70..136.77 rows=32 width=3670)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1987_1988_contrib_name_text_amt_sub_id (cost=0.00..14.68 rows=58 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1989_1990 (cost=14.81..166.38 rows=41 width=3695)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1989_1990_contrib_name_text_amt_sub_id (cost=0.00..14.79 rows=72 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1991_1992 (cost=15.17..263.53 rows=68 width=3691)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1991_1992_contrib_name_text_amt_sub_id (cost=0.00..15.14 rows=118 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1993_1994 (cost=15.01..221.60 rows=56 width=3660)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1993_1994_contrib_name_text_amt_sub_id (cost=0.00..14.98 rows=98 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1995_1996 (cost=15.52..362.82 rows=94 width=3660)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1995_1996_contrib_name_text_amt_sub_id (cost=0.00..15.48 rows=165 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1997_1998 (cost=15.30..299.49 rows=78 width=3663)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1997_1998_contrib_name_text_amt_sub_id (cost=0.00..15.27 rows=135 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_1999_2000 (cost=16.00..491.79 rows=130 width=3583)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_1999_2000_contrib_name_text_amt_sub_id (cost=0.00..15.95 rows=226 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2001_2002 (cost=15.74..420.16 rows=110 width=2795)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2001_2002_contrib_name_text_amt_sub_id (cost=0.00..15.69 rows=192 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2003_2004 (cost=23.31..1948.81 rows=506 width=1618)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2003_2004_contrib_name_text_amt_sub_id (cost=0.00..23.09 rows=912 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2005_2006 (cost=110.16..21668.44 rows=4251 width=1560)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2005_2006_contrib_name_text_amt_sub_id (cost=0.00..107.61 rows=11115 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2007_2008 (cost=552.01..113887.27 rows=24852 width=1522)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2007_2008_contrib_name_text_dt_sub_id (cost=0.00..537.10 rows=63046 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2009_2010 (cost=611.37..121717.07 rows=27117 width=1541)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2009_2010_contrib_name_text_amt_sub_id (cost=0.00..595.10 rows=70246 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2011_2012 (cost=4044.24..637086.08 rows=173936 width=1440)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2011_2012_contrib_name_text_amt_sub_id (cost=0.00..3939.88 rows=443951 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2013_2014 (cost=10105.12..1281861.99 rows=452289 width=1421)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2013_2014_contrib_name_text_amt_sub_id (cost=0.00..9833.75 rows=1191933 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2015_2016 (cost=38600.30..4055314.79 rows=1697608 width=1483)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2015_2016_contrib_name_text_amt_sub_id (cost=0.00..37581.74 rows=4528465 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2017_2018 (cost=46032.20..5527207.32 rows=2068079 width=1474)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2017_2018_contrib_name_text_amt_sub_id (cost=0.00..44791.35 rows=5399880 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" -> Parallel Bitmap Heap Scan on fec_fitem_sched_a_2019_2020 (cost=69243.05..7280948.66 rows=3047424 width=1520)"
" Recheck Cond: (contributor_name_text @@ to_tsquery('ActBlue:'::text))"
" Filter: is_individual"
" -> Bitmap Index Scan on idx_sched_a_2019_2020_contrib_name_text_amt_sub_id (cost=0.00..67414.60 rows=8107246 width=0)"
" Index Cond: (contributor_name_text @@ to_tsquery('ActBlue:*'::text))"
" -> Hash (cost=34597.61..34597.61 rows=242661 width=752)"
" -> Seq Scan on ofec_committee_history_mv ofec_committee_history_mv_2 (cost=0.00..34597.61 rows=242661 width=752)"
" -> Hash (cost=34597.61..34597.61 rows=242661 width=752)"
" -> Seq Scan on ofec_committee_history_mv ofec_committee_history_mv_1 (cost=0.00..34597.61 rows=242661 width=752)"

@fec-jli
Copy link
Contributor Author

fec-jli commented Jun 19, 2020 •
edited

  1. there are 96 rows(include duplicated rows) who used sort=contribution_receipt_date in 90 days.
    https://api.data.gov/admin/#/stats/users?end_at=2020-06-19&query=%7B%22condition%22%3A%22AND%22%2C%22rules%22%3A%5B%7B%22id%22%3A%22request_url_query%22%2C%22field%22%3A%22request_url_query%22%2C%22type%22%3A%22string%22%2C%22input%22%3A%22text%22%2C%22operator%22%3A%22contains%22%2C%22value%22%3A%22sort%3Dcontribution_receipt_date%22%7D%2C%7B%22id%22%3A%22user_email%22%2C%22field%22%3A%22user_email%22%2C%22type%22%3A%22string%22%2C%22input%22%3A%22text%22%2C%22operator%22%3A%22not_equal%22%2C%22value%22%3A%22WebAppMonitor%40fec.gov%22%7D%2C%7B%22id%22%3A%22user_email%22%2C%22field%22%3A%22user_email%22%2C%22type%22%3A%22string%22%2C%22input%22%3A%22text%22%2C%22operator%22%3A%22not_equal%22%2C%22value%22%3A%22webappalerts%40fec.gov%22%7D%5D%2C%22valid%22%3Atrue%7D&start_at=2020-03-01

Filters:
Screen Shot 2020-06-19 at 5 59 46 PM

  1. there are 53 rows(include duplicated rows) who don't use sort= in 90 days.
    https://api.data.gov/admin/#/stats/users?end_at=2020-06-19&query=%7B%22condition%22%3A%22AND%22%2C%22rules%22%3A%5B%7B%22id%22%3A%22request_url_query%22%2C%22field%22%3A%22request_url_query%22%2C%22type%22%3A%22string%22%2C%22input%22%3A%22text%22%2C%22operator%22%3A%22not_contains%22%2C%22value%22%3A%22sort%3D%22%7D%2C%7B%22id%22%3A%22request_path%22%2C%22field%22%3A%22request_path%22%2C%22type%22%3A%22string%22%2C%22input%22%3A%22text%22%2C%22operator%22%3A%22contains%22%2C%22value%22%3A%22%2Fschedules%2Fschedule_a%2F%22%7D%2C%7B%22id%22%3A%22request_path%22%2C%22field%22%3A%22request_path%22%2C%22type%22%3A%22string%22%2C%22input%22%3A%22text%22%2C%22operator%22%3A%22not_contains%22%2C%22value%22%3A%22by_%22%7D%2C%7B%22id%22%3A%22request_path%22%2C%22field%22%3A%22request_path%22%2C%22type%22%3A%22string%22%2C%22input%22%3A%22text%22%2C%22operator%22%3A%22not_contains%22%2C%22value%22%3A%22efile%22%7D%2C%7B%22id%22%3A%22user_email%22%2C%22field%22%3A%22user_email%22%2C%22type%22%3A%22string%22%2C%22input%22%3A%22text%22%2C%22operator%22%3A%22not_equal%22%2C%22value%22%3A%22webappalerts%40fec.gov%22%7D%5D%2C%22valid%22%3Atrue%7D&start_at=2020-03-01

Filters:
Screen Shot 2020-06-19 at 5 58 46 PM

  1. after removing duplicated user email, we have 112 users need send notification.

@fec-jli
Copy link
Contributor Author

fec-jli commented Jun 19, 2020 •
edited

The email to APIinfo@fec.gov:
Hello API user,
This email provides information about changes to the Federal Election Commission’s (FEC) API, openFEC.
On August 18, 2020, FEC will change the sort feature of ‘/schedules/schedule_a/’ endpoint. By default it was sorted by contribution_receipt_date ASC. It will be changed to DESC.
Should you have any questions, please contact APIinfo@fec.gov.

@fec-jli fec-jli mentioned this issue Jun 23, 2020
Merged
@fec-jli fec-jli changed the title Change schedule_a​ default sort by contribution_receipt_date DESC Change schedule_a​ default sort by contribution_receipt_date DESC (reminder Jul 20) Jun 23, 2020
@fec-jli fec-jli mentioned this issue Jul 22, 2020
Merged
@patphongs patphongs mentioned this issue Feb 29, 2024
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@fec-jli fec-jli

Labels
Technical debt Work: Back-end
Projects
None yet
Milestone
Sprint 12.6
3 participants
@fec-jli @JonellaCulmer @dzhang-fec