Open dzhang-fec opened 4 years ago
Laura Beaufort:slack_call: 1:41 PM Thanks @David Z. that’s excellent research. I’m not sure we can change the ORDER BY or the LIMIT without impacting the results, but it’s interesting to think about how we construct our OR queries. In theory, everything is possibly in SQLALchemy, it’s just not always easy to do. I would imagine this would be a heavy lift on the API side, but we should keep the issue open and at least research the possibility in a future sprint.
Example query when making the following change:
def filter_fulltext(query, kwargs, fields):
for key, column in fields:
if kwargs.get(key):
exclude_list = build_exclude_list(kwargs.get(key))
include_list = build_include_list(kwargs.get(key))
if exclude_list:
filters = [
sa.not_(column.match(utils.parse_fulltext(value)))
for value in exclude_list
]
query = query.filter(sa.and_(*filters))
if include_list:
filters = [
column.match(utils.parse_fulltext(value))
for value in include_list
]
temp_query = query # save this for multi below
# First filter is an AND
query = query.filter(sa.and_(filters[0]))
# Additional filters are UNION
if len(filters) > 1:
# don't actually filter the query or it just builds up
sub_queries = [
temp_query.filter(sa.and_(query_filter))
for query_filter in filters[1:]
]
query = query.union_all(*sub_queries)
return query
output:
SELECT anon_1.disclosure_fec_fitem_sched_a_cmte_id, anon_1.disclosure_fec_fitem_sched_a_rpt_yr, anon_1.disclosure_fec_fitem_sched_a_rpt_tp, anon_1.disclosure_fec_fitem_sched_a_image_num, anon_1.disclosure_fec_fitem_sched_a_line_num, anon_1.disclosure_fec_fitem_sched_a_tran_id, anon_1.disclosure_fec_fitem_sched_a_file_num, anon_1.disclosure_fec_fitem_sched_a_cmte_nm, anon_1.disclosure_fec_fitem_sched_a_entity_tp, anon_1.disclosure_fec_fitem_sched_a_entity_tp_desc, anon_1.disclosure_fec_fitem_sched_a_contbr_id, anon_1.disclosure_fec_fitem_sched_a_contbr_prefix, anon_1.disclosure_fec_fitem_sched_a_contbr_nm, anon_1.disclosure_fec_fitem_sched_a_cmte_tp, anon_1.disclosure_fec_fitem_sched_a_org_tp, anon_1.disclosure_fec_fitem_sched_a_cmte_dsgn, anon_1.disclosure_fec_fitem_sched_a_contbr_nm_first, anon_1.disclosure_fec_fitem_sched_a_contbr_m_nm, anon_1.disclosure_fec_fitem_sched_a_contbr_nm_last, anon_1.disclosure_fec_fitem_sched_a_contbr_suffix, anon_1.disclosure_fec_fitem_sched_a_contbr_st1, anon_1.disclosure_fec_fitem_sched_a_contbr_st2, anon_1.disclosure_fec_fitem_sched_a_contbr_city, anon_1.disclosure_fec_fitem_sched_a_contbr_st, anon_1.disclosure_fec_fitem_sched_a_contbr_zip, anon_1.disclosure_fec_fitem_sched_a_contbr_employer, anon_1.disclosure_fec_fitem_sched_a_contbr_occupation, anon_1.disclosure_fec_fitem_sched_a_clean_contbr_id, anon_1.disclosure_fec_fitem_sched_a_receipt_tp, anon_1.disclosure_fec_fitem_sched_a_receipt_tp_desc, anon_1.disclosure_fec_fitem_sched_a_receipt_desc, anon_1.disclosure_fec_fitem_sched_a_memo_cd, anon_1.disclosure_fec_fitem_sched_a_memo_cd_desc, anon_1.disclosure_fec_fitem_sched_a_contb_receipt_dt, anon_1.disclosure_fec_fitem_sched_a_contb_receipt_amt, anon_1.disclosure_fec_fitem_sched_a_contb_aggregate_ytd, anon_1.disclosure_fec_fitem_sched_a_cand_id, anon_1.disclosure_fec_fitem_sched_a_cand_nm, anon_1.disclosure_fec_fitem_sched_a_cand_nm_first, anon_1.disclosure_fec_fitem_sched_a_cand_nm_last, anon_1.disclosure_fec_fitem_sched_a_cand_m_nm, anon_1.disclosure_fec_fitem_sched_a_cand_prefix, anon_1.disclosure_fec_fitem_sched_a_cand_suffix, anon_1.disclosure_fec_fitem_sched_a_cand_office, anon_1.disclosure_fec_fitem_sched_a_cand_office_desc, anon_1.disclosure_fec_fitem_sched_a_cand_office_st, anon_1.disclosure_fec_fitem_sched_a_cand_office_st_desc, anon_1.disclosure_fec_fitem_sched_a_cand_office_district, anon_1.disclosure_fec_fitem_sched_a_conduit_cmte_id, anon_1.disclosure_fec_fitem_sched_a_conduit_cmte_nm, anon_1.disclosure_fec_fitem_sched_a_conduit_cmte_st1, anon_1.disclosure_fec_fitem_sched_a_conduit_cmte_st2, anon_1.disclosure_fec_fitem_sched_a_conduit_cmte_city, anon_1.disclosure_fec_fitem_sched_a_conduit_cmte_st, anon_1.disclosure_fec_fitem_sched_a_conduit_cmte_zip, anon_1.disclosure_fec_fitem_sched_a_donor_cmte_nm, anon_1.disclosure_fec_fitem_sched_a_national_cmte_nonfed_acct, anon_1.disclosure_fec_fitem_sched_a_election_tp, anon_1.disclosure_fec_fitem_sched_a_election_tp_desc, anon_1.disclosure_fec_fitem_sched_a_fec_election_tp_desc, anon_1.disclosure_fec_fitem_sched_a_fec_election_yr, anon_1.disclosure_fec_fitem_sched_a_action_cd, anon_1.disclosure_fec_fitem_sched_a_action_cd_desc, anon_1.disclosure_fec_fitem_sched_a_schedule_type_desc, anon_1.disclosure_fec_fitem_sched_a_pg_date, anon_1.disclosure_fec_fitem_sched_a_orig_sub_id, anon_1.disclosure_fec_fitem_sched_a_back_ref_tran_id, anon_1.disclosure_fec_fitem_sched_a_back_ref_sched_nm, anon_1.disclosure_fec_fitem_sched_a_filing_form, anon_1.disclosure_fec_fitem_sched_a_link_id, anon_1.disclosure_fec_fitem_sched_a_contributor_name_text, anon_1.disclosure_fec_fitem_sched_a_contributor_employer_text, anon_1.disclosure_fec_fitem_sched_a_contributor_occupation_text, anon_1.disclosure_fec_fitem_sched_a_is_individual, anon_1.disclosure_fec_fitem_sched_a_memo_text, anon_1.disclosure_fec_fitem_sched_a_two_year_transaction_period, anon_1.disclosure_fec_fitem_sched_a_schedule_type, anon_1.disclosure_fec_fitem_sched_a_increased_limit, anon_1.disclosure_fec_fitem_sched_a_sub_id, anon_1.disclosure_fec_fitem_sched_a_pdf_url, anon_1.disclosure_fec_fitem_sched_a_line_number_label, ofec_committee_history_mv_1.idx, ofec_committee_history_mv_1.name, ofec_committee_history_mv_1.committee_id, ofec_committee_history_mv_1.cycles, ofec_committee_history_mv_1.treasurer_name, ofec_committee_history_mv_1.treasurer_text, ofec_committee_history_mv_1.committee_type, ofec_committee_history_mv_1.committee_type_full, ofec_committee_history_mv_1.filing_frequency, ofec_committee_history_mv_1.designation, ofec_committee_history_mv_1.designation_full, ofec_committee_history_mv_1.organization_type, ofec_committee_history_mv_1.organization_type_full, ofec_committee_history_mv_1.affiliated_committee_name, ofec_committee_history_mv_1.party, ofec_committee_history_mv_1.party_full, ofec_committee_history_mv_1.state, ofec_committee_history_mv_1.street_1, ofec_committee_history_mv_1.street_2, ofec_committee_history_mv_1.city, ofec_committee_history_mv_1.state_full, ofec_committee_history_mv_1.zip, ofec_committee_history_mv_1.candidate_ids, ofec_committee_history_mv_1.cycle, ofec_committee_history_mv_1.cycles_has_financial, ofec_committee_history_mv_1.last_cycle_has_financial, ofec_committee_history_mv_1.cycles_has_activity, ofec_committee_history_mv_1.last_cycle_has_activity, ofec_committee_history_mv_1.is_active, ofec_committee_history_mv_2.idx, ofec_committee_history_mv_2.name, ofec_committee_history_mv_2.committee_id, ofec_committee_history_mv_2.cycles, ofec_committee_history_mv_2.treasurer_name, ofec_committee_history_mv_2.treasurer_text, ofec_committee_history_mv_2.committee_type, ofec_committee_history_mv_2.committee_type_full, ofec_committee_history_mv_2.filing_frequency, ofec_committee_history_mv_2.designation, ofec_committee_history_mv_2.designation_full, ofec_committee_history_mv_2.organization_type, ofec_committee_history_mv_2.organization_type_full, ofec_committee_history_mv_2.affiliated_committee_name, ofec_committee_history_mv_2.party, ofec_committee_history_mv_2.party_full, ofec_committee_history_mv_2.state, ofec_committee_history_mv_2.street_1, ofec_committee_history_mv_2.street_2, ofec_committee_history_mv_2.city, ofec_committee_history_mv_2.state_full, ofec_committee_history_mv_2.zip, ofec_committee_history_mv_2.candidate_ids, ofec_committee_history_mv_2.cycle, ofec_committee_history_mv_2.cycles_has_financial, ofec_committee_history_mv_2.last_cycle_has_financial, ofec_committee_history_mv_2.cycles_has_activity, ofec_committee_history_mv_2.last_cycle_has_activity, ofec_committee_history_mv_2.is_active
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.two_year_transaction_period IN (2020)
AND disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('farnsworth:*')
UNION ALL
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.two_year_transaction_period IN (2020)
AND disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('beaufort:*')
)
AS anon_1 LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_1 ON anon_1.disclosure_fec_fitem_sched_a_cmte_id = ofec_committee_history_mv_1.committee_id AND anon_1.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 anon_1.disclosure_fec_fitem_sched_a_clean_contbr_id = ofec_committee_history_mv_2.committee_id AND anon_1.disclosure_fec_fitem_sched_a_two_year_transaction_period = ofec_committee_history_mv_2.cycle
Explain plan:
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Hash Left Join (cost=104314.35..293200.15 rows=29770 width=2375) |
| 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)) |
| -> Hash Left Join (cost=52157.17..207184.02 rows=29770 width=1926) |
| 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)) |
| -> Append (cost=0.00..124423.90 rows=29770 width=1477) |
| -> Append (cost=0.00..62063.10 rows=14885 width=1477) |
| -> Seq Scan on fec_fitem_sched_a (cost=0.00..0.00 rows=1 width=5707) |
| Filter: ((two_year_transaction_period = '2020'::numeric) AND (contributor_name_text @@ to_tsquery('farnsworth:*'::text))) |
| -> Bitmap Heap Scan on fec_fitem_sched_a_2019_2020 (cost=559.60..62063.10 rows=14884 width=1477) |
| Recheck Cond: (contributor_name_text @@ to_tsquery('farnsworth:*'::text)) |
| Filter: (two_year_transaction_period = '2020'::numeric) |
| -> Bitmap Index Scan on idx_sched_a_2019_2020_contrib_name_text_amt_sub_id (cost=0.00..555.88 rows=14884 width=0) |
| Index Cond: (contributor_name_text @@ to_tsquery('farnsworth:*'::text)) |
| -> Append (cost=0.00..62063.10 rows=14885 width=1477) |
| -> Seq Scan on fec_fitem_sched_a fec_fitem_sched_a_1 (cost=0.00..0.00 rows=1 width=5707) |
| Filter: ((two_year_transaction_period = '2020'::numeric) AND (contributor_name_text @@ to_tsquery('beaufort:*'::text))) |
| -> Bitmap Heap Scan on fec_fitem_sched_a_2019_2020 fec_fitem_sched_a_2019_2020_1 (cost=559.60..62063.10 rows=14884 width=1477) |
| Recheck Cond: (contributor_name_text @@ to_tsquery('beaufort:*'::text)) |
| Filter: (two_year_transaction_period = '2020'::numeric) |
| -> Bitmap Index Scan on idx_sched_a_2019_2020_contrib_name_text_amt_sub_id (cost=0.00..555.88 rows=14884 width=0) |
| Index Cond: (contributor_name_text @@ to_tsquery('beaufort:*'::text)) |
| -> Hash (cost=34378.67..34378.67 rows=241567 width=449) |
| -> Seq Scan on ofec_committee_history_mv ofec_committee_history_mv_1 (cost=0.00..34378.67 rows=241567 width=449) |
| -> Hash (cost=34378.67..34378.67 rows=241567 width=449) |
| -> Seq Scan on ofec_committee_history_mv ofec_committee_history_mv_2 (cost=0.00..34378.67 rows=241567 width=449) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN
If we need to keep the left joins with each UNION
-ed query, I think this does it: https://github.com/fecgov/openFEC/pull/4400/files
SELECT anon_1.disclosure_fec_fitem_sched_a_cmte_id, anon_1.disclosure_fec_fitem_sched_a_rpt_yr, anon_1.disclosure_fec_fitem_sched_a_rpt_tp, anon_1.disclosure_fec_fitem_sched_a_image_num, anon_1.disclosure_fec_fitem_sched_a_line_num, anon_1.disclosure_fec_fitem_sched_a_tran_id, anon_1.disclosure_fec_fitem_sched_a_file_num, anon_1.disclosure_fec_fitem_sched_a_cmte_nm, anon_1.disclosure_fec_fitem_sched_a_entity_tp, anon_1.disclosure_fec_fitem_sched_a_entity_tp_desc, anon_1.disclosure_fec_fitem_sched_a_contbr_id, anon_1.disclosure_fec_fitem_sched_a_contbr_prefix, anon_1.disclosure_fec_fitem_sched_a_contbr_nm, anon_1.disclosure_fec_fitem_sched_a_cmte_tp, anon_1.disclosure_fec_fitem_sched_a_org_tp, anon_1.disclosure_fec_fitem_sched_a_cmte_dsgn, anon_1.disclosure_fec_fitem_sched_a_contbr_nm_first, anon_1.disclosure_fec_fitem_sched_a_contbr_m_nm, anon_1.disclosure_fec_fitem_sched_a_contbr_nm_last, anon_1.disclosure_fec_fitem_sched_a_contbr_suffix, anon_1.disclosure_fec_fitem_sched_a_contbr_st1, anon_1.disclosure_fec_fitem_sched_a_contbr_st2, anon_1.disclosure_fec_fitem_sched_a_contbr_city, anon_1.disclosure_fec_fitem_sched_a_contbr_st, anon_1.disclosure_fec_fitem_sched_a_contbr_zip, anon_1.disclosure_fec_fitem_sched_a_contbr_employer, anon_1.disclosure_fec_fitem_sched_a_contbr_occupation, anon_1.disclosure_fec_fitem_sched_a_clean_contbr_id, anon_1.disclosure_fec_fitem_sched_a_receipt_tp, anon_1.disclosure_fec_fitem_sched_a_receipt_tp_desc, anon_1.disclosure_fec_fitem_sched_a_receipt_desc, anon_1.disclosure_fec_fitem_sched_a_memo_cd, anon_1.disclosure_fec_fitem_sched_a_memo_cd_desc, anon_1.disclosure_fec_fitem_sched_a_contb_receipt_dt, anon_1.disclosure_fec_fitem_sched_a_contb_receipt_amt, anon_1.disclosure_fec_fitem_sched_a_contb_aggregate_ytd, anon_1.disclosure_fec_fitem_sched_a_cand_id, anon_1.disclosure_fec_fitem_sched_a_cand_nm, anon_1.disclosure_fec_fitem_sched_a_cand_nm_first, anon_1.disclosure_fec_fitem_sched_a_cand_nm_last, anon_1.disclosure_fec_fitem_sched_a_cand_m_nm, anon_1.disclosure_fec_fitem_sched_a_cand_prefix, anon_1.disclosure_fec_fitem_sched_a_cand_suffix, anon_1.disclosure_fec_fitem_sched_a_cand_office, anon_1.disclosure_fec_fitem_sched_a_cand_office_desc, anon_1.disclosure_fec_fitem_sched_a_cand_office_st, anon_1.disclosure_fec_fitem_sched_a_cand_office_st_desc, anon_1.disclosure_fec_fitem_sched_a_cand_office_district, anon_1.disclosure_fec_fitem_sched_a_conduit_cmte_id, anon_1.disclosure_fec_fitem_sched_a_conduit_cmte_nm, anon_1.disclosure_fec_fitem_sched_a_conduit_cmte_st1, anon_1.disclosure_fec_fitem_sched_a_conduit_cmte_st2, anon_1.disclosure_fec_fitem_sched_a_conduit_cmte_city, anon_1.disclosure_fec_fitem_sched_a_conduit_cmte_st, anon_1.disclosure_fec_fitem_sched_a_conduit_cmte_zip, anon_1.disclosure_fec_fitem_sched_a_donor_cmte_nm, anon_1.disclosure_fec_fitem_sched_a_national_cmte_nonfed_acct, anon_1.disclosure_fec_fitem_sched_a_election_tp, anon_1.disclosure_fec_fitem_sched_a_election_tp_desc, anon_1.disclosure_fec_fitem_sched_a_fec_election_tp_desc, anon_1.disclosure_fec_fitem_sched_a_fec_election_yr, anon_1.disclosure_fec_fitem_sched_a_action_cd, anon_1.disclosure_fec_fitem_sched_a_action_cd_desc, anon_1.disclosure_fec_fitem_sched_a_schedule_type_desc, anon_1.disclosure_fec_fitem_sched_a_pg_date, anon_1.disclosure_fec_fitem_sched_a_orig_sub_id, anon_1.disclosure_fec_fitem_sched_a_back_ref_tran_id, anon_1.disclosure_fec_fitem_sched_a_back_ref_sched_nm, anon_1.disclosure_fec_fitem_sched_a_filing_form, anon_1.disclosure_fec_fitem_sched_a_link_id, anon_1.disclosure_fec_fitem_sched_a_contributor_name_text, anon_1.disclosure_fec_fitem_sched_a_contributor_employer_text, anon_1.disclosure_fec_fitem_sched_a_contributor_occupation_text, anon_1.disclosure_fec_fitem_sched_a_is_individual, anon_1.disclosure_fec_fitem_sched_a_memo_text, anon_1.disclosure_fec_fitem_sched_a_two_year_transaction_period, anon_1.disclosure_fec_fitem_sched_a_schedule_type, anon_1.disclosure_fec_fitem_sched_a_increased_limit, anon_1.disclosure_fec_fitem_sched_a_sub_id, anon_1.disclosure_fec_fitem_sched_a_pdf_url, anon_1.disclosure_fec_fitem_sched_a_line_number_label
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 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.contributor_name_text @@ to_tsquery('C00656314:*')
UNION ALL
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 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.contributor_name_text @@ to_tsquery('C00647701:*')
UNION ALL
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 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.contributor_name_text @@ to_tsquery('C00664938:*')
UNION ALL
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 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.contributor_name_text @@ to_tsquery('C00667865:*')) AS anon_1
06/04/2020 we caught the similar sql again (it was at the top 1 sql), >7 mins on average and we killed the ones >30mins. @lbeaufort @rjayasekera @fecjjeng
6/9 this sql again FEC-CLOUD-AWSAPP 4:15 PM Sample of a long running Query 0:12:39.741541 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('Andrews: & James: & F:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('An:')) ORDER BY disclosure.fec_fitem_sched_a.contb_receipt_dt DESC, disclosure.fec_fitem_sched_a.sub_id DESC LIMIT 100
2nd one within 60mins FEC-CLOUD-AWSAPP 7:30 PM Sample of a long running Query 0:18:25.286322 SELECT * FROM disclosure.fec_fitem_sched_b LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_1 ON disclosure.fec_fitem_sched_b.cmte_id = ofec_committee_history_mv_1.committee_id AND disclosure.fec_fitem_sched_b.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_b.clean_recipient_cmte_id = ofec_committee_history_mv_2.committee_id AND disclosure.fec_fitem_sched_b.two_year_transaction_period = ofec_committee_history_mv_2.cycle WHERE disclosure.fec_fitem_sched_b.recipient_name_text @@ to_tsquery('C00595751:') OR disclosure.fec_fitem_sched_b.recipient_name_text @@ to_tsquery('C00583880:') ORDER BY coalesce(disclosure.fec_fitem_sched_b.disb_dt, CAST('9999-12-31' AS DATE)) DESC, disclosure.fec_fitem_sched_b.sub_id DESC LIMIT 30
For suggestion #3, the change is to use union all to replace or. Since union all does not filter out duplicate rows, it may results in extra data.
Following is my simple tests. Note the second and the third cmte_id is identical. Query#1 returns 18 rows and Query#2 returns 21 rows. Query #1: select count(*) from ( select sub_id from disclosure.fec_fitem_sched_a WHERE disclosure.fec_fitem_sched_a.two_year_transaction_period IN (2020) AND (disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('C00558411:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('C00666123:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('C00666123:')) ) the_query;
Query #2 select count(*) from ( select sub_id from disclosure.fec_fitem_sched_a WHERE disclosure.fec_fitem_sched_a.two_year_transaction_period IN (2020) AND disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('C00558411:') union all select sub_id from disclosure.fec_fitem_sched_a WHERE disclosure.fec_fitem_sched_a.two_year_transaction_period IN (2020) AND disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('C00666123:') union all select sub_id from disclosure.fec_fitem_sched_a WHERE disclosure.fec_fitem_sched_a.two_year_transaction_period IN (2020) AND disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('C00666123:') ) the_query;
In the case of cmte_id, we may be able to programmatically check if the user put in duplicate cmte_id, but for other search such as name, two different string may get the same row, if we use union all, we need to be able to get rid of the duplicate rows. If we use union instead of union all, it will resolve the duplicate rows problem, but will be slower than union all.
@fecjjeng thanks for flagging the duplicate rows issue, I'll take a look at that - it seems straightforward to remove duplicates before the query goes to the database.
The example queries that @dzhang-fec provided can help me get a sense of how these queries might get constructed with the code change. I'll be sure to document the structure to make sure it's being generated properly, and validate the result counts.
@lbeaufort as I mentioned in the above example, in the case of cmte_id, we can programmatically remove duplicate INPUT (if the user put in duplicate cmte_id) BEFORE it reach the database, but for other search such as name, two different string may get the same row, if we use union all, we need to be able to get rid of the resulted duplicate rows. Following is another example that hopefully will explain the situation better. There is a contributor with name jon robertson in 2010. So I used it as an example. I also included the time it takes to run as a comparison (I run it on two different production readers to avoid the cache effect)
Query#1 select count(*) from ( select sub_id from disclosure.fec_fitem_sched_a WHERE disclosure.fec_fitem_sched_a.two_year_transaction_period IN (2010) AND (disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('jon:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('robertson:')) ) the_query; -- 19535 rows (19.720 sec) on prod_inst1
Query#2: select count(*) from ( select sub_id from disclosure.fec_fitem_sched_a WHERE disclosure.fec_fitem_sched_a.two_year_transaction_period IN (2010) AND disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('jon:') union all select sub_id from disclosure.fec_fitem_sched_a WHERE disclosure.fec_fitem_sched_a.two_year_transaction_period IN (2010) AND disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('robertson:') ) the_query; -- 19550 rows (24.496 sec) on prod_inst2
Use union instead of union all will solve the duplicate rows issue. I conducted a simple test similar to described above, the execution time needed for union all vs union is similar. (but OR vs union all returns in similar time in the simple test as well)
If this is a heavy lift, we may want to conduct a larger scale tests, also be careful to avoid cache effect, before dive deeply in.
Summary: @fecjjeng good testing. Thanks. I think we need to use real data (not the edging/dummy data (duplicated cmd_id: C00666123 two times)to test. (Duplicated data is fine because they searched the same data two times. ) . The performance is the key which already contributed the 5/17 app node outage and today's 3rd auto-scaling replica, which is 4000+ times important than the edging case.
1) I have tested the cases without "duplicate" cmte_id all of the rows are same. I used the data of real data we tracked (not the dummy data).
2)If the client used "duplicate" cmte_id they should get the same duplicated records? e.g., If I ordered one item two times at amazon.com, the activity will be 2 times (not distinct). I think the end user will not put duplicate cmte_id per my sql tracing logs. And the return is not count(*), it is the actual data of columns (they can find the duplicate data) (Sure, if @lbeaufort can do it at front side as edging/wise treatment, that is fine if it not take too much time for the key code change for the critical performance issue)
3)Performance. It will be super as we tested in the Production online with "real" data and loading. If we run the sql at the original real sql, we will find the big difference. Real data testing is the key.
For example, today we got the same query again and it is running 15+ mins (see below) AND it triggered a 3rd replica as auto-scaling in production.... I use the union all, it only run 1sec
This is the sqme sqls that triggered the 3rd auto-scaling (cost and hard to management in term of remove it at mid night to kill the connections and sessions to save $) top1.txt
How to check/validate how slow this sql? The original one ^^ of 5/17 and the top1 text of 6/11 in the ticket
Yes, I agree with you absolutely, if the performance is bad and users can not get their result back, the accuracy of the data is a moot point. This is really great observation and suggestion. Great job!
Sorry I did not explain my point well. I was simply using a small, primitive example to illustrate my concern that union all
will result in duplicate rows in general, especially when this is a full text search field. The second example above is a real case, not dummy input, even though the dataset returned is small.
If this is not a heavy lift code change, let's change it and observe.
Using UNION instead of UNION ALL should resolve the duplicate row issue. However, in theory, UNION is slower than UNION ALL. In my small dataset test it make no difference, but again, it is small dataset, larger data set would be different. So if it impact performance too much, that is not important, again, if the performance is bad and users can not get their result back, the accuracy of the data is a moot point.
I don’t know how I would limit the subqueries for results without breaking counts. I’m not sure I can go back and modify the subqueries for pagination after it’s generated. I was able to get offset and order by working, but now the counts are broken.
I don’t think we could decouple the counts from the pagination without significant rewriting, but I’ll think on it. I might need to re-write the query object as an iterable. Take 3: https://github.com/fecgov/openFEC/pull/4422
Per @lbeaufort yesterday, yesterday at union all meeting for related #4389 tickets, I did the research on the data from yesterday logs of different timing: For schedule_a search sql: There are about 80 % are "not" using multiple search of @@ to_tsquery There are about 10 to 20% are using multiple search of @@ to_tsquery, e.g., @@ to_tsquery @@ to_tsquery.:There are about 7 to 10% are using multiple search of @@ to_tsquery (people name) and inside There are about 3~10% are using multiple search of @@ to_tsquery (cmte_id) Query some examples: WHERE ofec_candidate_fulltext_mv.fulltxt @@ to_tsquery('C00300376:') WHERE disclosure.fec_fitem_sched_a.cmte_id IN ('C00401224') AND disclosure.fec_fitem_sched_a.two_year_transaction_period IN (2020) AND (disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('C00595751:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('C00583880:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('C00587451:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('C00401224:')) AND disclosure.fec_fitem_sched_a.filing_form = 'F3X' AND disclosure.fec_fitem_sched_a.line_num = '11AI' ORDER BY disclosure.fec_fitem_sched_a.contb_receipt_dt DESC, disclosure.fec_fitem_sched_a.sub_id DESC WHERE disclosure.fec_fitem_sched_a.is_individual = true AND disclosure.fec_fitem_sched_a.contbr_st IN ('IL') AND (disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('charles: & carey:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('john: & buck:')) ('STIGLER') AND disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('Roy: & Jean: & Bishop:') WHERE disclosure.fec_fitem_sched_a.two_year_transaction_period IN (2020) AND disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('robert: & rob:') WHERE disclosure.fec_fitem_sched_a.two_year_transaction_period IN (2020) AND disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery('lindsay: & mcneil:') disclosure.fec_fitem_sched_a.is_individual = true AND disclosure.fec_fitem_sched_a.cmte_id IN ('C00618389') AND disclosure.fec_fitem_sche
def join_committee_queries(self, kwargs):
"""Build and compose per-committee subqueries using `UNION ALL`.
"""
queries = []
total = 0
for committee_id in kwargs.get('committee_id', []):
query, count = self.build_committee_query(kwargs, committee_id)
queries.append(query.subquery().select())
total += count
query = models.db.session.query(
self.model
).select_entity_from(
sa.union_all(*queries)
)
query = query.options(*self.query_options)
return query, total
Moving to 14.6 as we reconsider union all work
Summary: For performance. we suggest to modify the full text query logic in the API to create
union all
queries instead ofOR
queries. And add "limit" to be wiser to get limited records (<0.001% data) as we need only (not all unnecessary large data one we don't want)Problem: this long sqls were at Prod and showed #Alert @pkfec told me to have to killed them.
Analysis: This is because this long sqls caused the optimizer confused by the multiple special FULLTEXT to_tsquery search "OR" and "ORDER" and "LIMIT" together on "LARGE" recordset. Postgres is not very good like Oracle for the large record with multiple "OR"
Suggestions: 1)~removed the ORDER BY (5 secs) (this may change the result order)~ 2)~removed the LIMIT (3 secs) (this changed the logic), OR~ 3)dynamically change the "OR" on the to_sql to use "union all" (so the record set get small). This has no change on the result and logic but need change the code. This is complex but best to me in term of 1) and 2). But I follow your guys.
Problems:
Completion criteria:
filter_fulltext
(name, employer, occupation with under 500K and cross-compareThen we got the sql 👍
The I tested with this real sql on real Production suggestions 1)removed the ORDER BY (2 secs) (this may change the result order) 2)removed the LIMIT (2 secs) (this changed the logic), OR 3)dynamically change the "OR" on the to_sql to use "union all" (so the record set get small). 0.6 secs This has no change on the result and logic but need change the code. This is complex but best to me in term of 1) and 2). But I follow @lbeaufort and DBA team.