Slow query: Optimize real_efile.sa7 query

[pkfec]

From Sep 26th, pgbadger most time consuming queries report, this query is run often. This query on an average takes 5 seconds to execute. However, there was time when it took more than 9 minutes to execute. Below are two example of the actual queries. Addressing this will help to improve performance.

Day	Hour	Count	Duration	Avg duration
Sep 26	00	3	28m52s	9m37s
	01	5	1h38m1s	19m36s
	13	2	30s742ms	15s371ms
	14	2	22s785ms	11s392ms
	15	3	16s451ms	5s483ms
	16	2	12s752ms	6s376ms
	17	2	1m2s	31s2ms
	19	4	5s70ms	1s267ms
	20	2	18s997ms	9s498ms
	21	2	22s521ms	11s260ms
	23	1	19s312ms	19s312ms

Example 1:
SELECT real_efile.sa7.line_num AS real_efile_sa7_line_num, real_efile.sa7.tran_id AS real_efile_sa7_tran_id, real_efile.sa7.imageno AS real_efile_sa7_imageno, real_efile.sa7.entity AS real_efile_sa7_entity, real_efile.sa7.amend AS real_efile_sa7_amend, real_efile.sa7.br_tran_id AS real_efile_sa7_br_tran_id, real_efile.sa7.br_sname AS real_efile_sa7_br_sname, real_efile.sa7.create_dt AS real_efile_sa7_create_dt, real_efile.sa7.repid AS real_efile_sa7_repid, real_efile.sa7.rel_lineno AS real_efile_sa7_rel_lineno, real_efile.sa7.comid AS real_efile_sa7_comid, real_efile.sa7.prefix AS real_efile_sa7_prefix, real_efile.sa7.fname AS real_efile_sa7_fname, real_efile.sa7.mname AS real_efile_sa7_mname, real_efile.sa7.name AS real_efile_sa7_name, real_efile.sa7.suffix AS real_efile_sa7_suffix, real_efile.sa7.city AS real_efile_sa7_city, real_efile.sa7.state AS real_efile_sa7_state, real_efile.sa7.zip AS real_efile_sa7_zip, real_efile.sa7.indemp AS real_efile_sa7_indemp, real_efile.sa7.indocc AS real_efile_sa7_indocc, real_efile.sa7.ytd AS real_efile_sa7_ytd, real_efile.sa7.amount AS real_efile_sa7_amount, real_efile.sa7.date_con AS real_efile_sa7_date_con, real_efile.sa7.other_comid AS real_efile_sa7_other_comid, real_efile.sa7.donor_comname AS real_efile_sa7_donor_comname, real_efile.sa7.other_str1 AS real_efile_sa7_other_str1, real_efile.sa7.other_str2 AS real_efile_sa7_other_str2, real_efile.sa7.other_city AS real_efile_sa7_other_city, real_efile.sa7.other_state AS real_efile_sa7_other_state, real_efile.sa7.other_zip AS real_efile_sa7_other_zip, real_efile.sa7.memo_code AS real_efile_sa7_memo_code, real_efile.sa7.memo_text AS real_efile_sa7_memo_text, real_efile.sa7.contributor_name_text AS real_efile_sa7_contributor_name_text, real_efile.sa7.contributor_employer_text AS real_efile_sa7_contributor_employer_text, real_efile.sa7.contributor_occupation_text AS real_efile_sa7_contributor_occupation_text, real_efile.sa7.pgo AS real_efile_sa7_pgo, ofec_committee_history_mv_1.idx AS ofec_committee_history_mv_1_idx, ofec_committee_history_mv_1.name AS ofec_committee_history_mv_1_name, ofec_committee_history_mv_1.committee_id AS ofec_committee_history_mv_1_committee_id, ofec_committee_history_mv_1.cycles AS ofec_committee_history_mv_1_cycles, ofec_committee_history_mv_1.treasurer_name AS ofec_committee_history_mv_1_treasurer_name, ofec_committee_history_mv_1.treasurer_text AS ofec_committee_history_mv_1_treasurer_text, ofec_committee_history_mv_1.committee_type AS ofec_committee_history_mv_1_committee_type, ofec_committee_history_mv_1.committee_type_full AS ofec_committee_history_mv_1_committee_type_full, ofec_committee_history_mv_1.filing_frequency AS ofec_committee_history_mv_1_filing_frequency, ofec_committee_history_mv_1.designation AS ofec_committee_history_mv_1_designation, ofec_committee_history_mv_1.designation_full AS ofec_committee_history_mv_1_designation_full, ofec_committee_history_mv_1.organization_type AS ofec_committee_history_mv_1_organization_type, ofec_committee_history_mv_1.organization_type_full AS ofec_committee_history_mv_1_organization_type_full, ofec_committee_history_mv_1.party AS ofec_committee_history_mv_1_party, ofec_committee_history_mv_1.party_full AS ofec_committee_history_mv_1_party_full, ofec_committee_history_mv_1.state AS ofec_committee_history_mv_1_state, ofec_committee_history_mv_1.street_1 AS ofec_committee_history_mv_1_street_1, ofec_committee_history_mv_1.street_2 AS ofec_committee_history_mv_1_street_2, ofec_committee_history_mv_1.city AS ofec_committee_history_mv_1_city, ofec_committee_history_mv_1.state_full AS ofec_committee_history_mv_1_state_full, ofec_committee_history_mv_1.zip AS ofec_committee_history_mv_1_zip, ofec_committee_history_mv_1.candidate_ids AS ofec_committee_history_mv_1_candidate_ids, ofec_committee_history_mv_1.cycle AS ofec_committee_history_mv_1_cycle, reps_1.repid AS reps_1_repid, reps_1.form AS reps_1_form, reps_1.comid AS reps_1_comid, reps_1.com_name AS reps_1_com_name, reps_1.timestamp AS reps_1_timestamp, reps_1.create_dt AS reps_1_create_dt, reps_1.from_date AS reps_1_from_date, reps_1.through_date AS reps_1_through_date, reps_1.starting AS reps_1_starting, reps_1.ending AS reps_1_ending, reps_1.rptcode AS reps_1_rptcode, reps_1.previd AS reps_1_previd, reps_1.rptnum AS reps_1_rptnum, efiling_amendment_chain_vw_1.repid AS efiling_amendment_chain_vw_1_repid, efiling_amendment_chain_vw_1.previd AS efiling_amendment_chain_vw_1_previd, efiling_amendment_chain_vw_1.amendment_chain AS efiling_amendment_chain_vw_1_amendment_chain, efiling_amendment_chain_vw_1.longest_chain AS efiling_amendment_chain_vw_1_longest_chain, efiling_amendment_chain_vw_1.most_recent_filing AS efiling_amendment_chain_vw_1_most_recent_filing, efiling_amendment_chain_vw_1.depth AS efiling_amendment_chain_vw_1_depth, efiling_amendment_chain_vw_1.last AS efiling_amendment_chain_vw_1_last, reps_1.superceded AS reps_1_superceded FROM real_efile.sa7 LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_1 ON real_efile.sa7.comid = ofec_committee_history_mv_1.committee_id AND extract (YEAR FROM real_efile.sa7.create_dt) + CAST (extract (YEAR FROM real_efile.sa7.create_dt) AS INTEGER) % 2 = ofec_committee_history_mv_1.cycle LEFT OUTER JOIN real_efile.reps AS reps_1 ON real_efile.sa7.repid = reps_1.repid LEFT OUTER JOIN efiling_amendment_chain_vw AS efiling_amendment_chain_vw_1 ON efiling_amendment_chain_vw_1.repid = reps_1.repid WHERE real_efile.sa7.comid IN ('C00135475') AND real_efile.sa7.date_con IS NOT NULL ORDER BY real_efile.sa7.date_con DESC LIMIT 30 OFFSET 0;

Example2:

SELECT real_efile.sa7.line_num AS real_efile_sa7_line_num, real_efile.sa7.tran_id AS real_efile_sa7_tran_id, real_efile.sa7.imageno AS real_efile_sa7_imageno, real_efile.sa7.entity AS real_efile_sa7_entity, real_efile.sa7.amend AS real_efile_sa7_amend, real_efile.sa7.br_tran_id AS real_efile_sa7_br_tran_id, real_efile.sa7.br_sname AS real_efile_sa7_br_sname, real_efile.sa7.create_dt AS real_efile_sa7_create_dt, real_efile.sa7.repid AS real_efile_sa7_repid, real_efile.sa7.rel_lineno AS real_efile_sa7_rel_lineno, real_efile.sa7.comid AS real_efile_sa7_comid, real_efile.sa7.prefix AS real_efile_sa7_prefix, real_efile.sa7.fname AS real_efile_sa7_fname, real_efile.sa7.mname AS real_efile_sa7_mname, real_efile.sa7.name AS real_efile_sa7_name, real_efile.sa7.suffix AS real_efile_sa7_suffix, real_efile.sa7.city AS real_efile_sa7_city, real_efile.sa7.state AS real_efile_sa7_state, real_efile.sa7.zip AS real_efile_sa7_zip, real_efile.sa7.indemp AS real_efile_sa7_indemp, real_efile.sa7.indocc AS real_efile_sa7_indocc, real_efile.sa7.ytd AS real_efile_sa7_ytd, real_efile.sa7.amount AS real_efile_sa7_amount, real_efile.sa7.date_con AS real_efile_sa7_date_con, real_efile.sa7.other_comid AS real_efile_sa7_other_comid, real_efile.sa7.donor_comname AS real_efile_sa7_donor_comname, real_efile.sa7.other_str1 AS real_efile_sa7_other_str1, real_efile.sa7.other_str2 AS real_efile_sa7_other_str2, real_efile.sa7.other_city AS real_efile_sa7_other_city, real_efile.sa7.other_state AS real_efile_sa7_other_state, real_efile.sa7.other_zip AS real_efile_sa7_other_zip, real_efile.sa7.memo_code AS real_efile_sa7_memo_code, real_efile.sa7.memo_text AS real_efile_sa7_memo_text, real_efile.sa7.contributor_name_text AS real_efile_sa7_contributor_name_text, real_efile.sa7.contributor_employer_text AS real_efile_sa7_contributor_employer_text, real_efile.sa7.contributor_occupation_text AS real_efile_sa7_contributor_occupation_text, real_efile.sa7.pgo AS real_efile_sa7_pgo, ofec_committee_history_mv_1.idx AS ofec_committee_history_mv_1_idx, ofec_committee_history_mv_1.name AS ofec_committee_history_mv_1_name, ofec_committee_history_mv_1.committee_id AS ofec_committee_history_mv_1_committee_id, ofec_committee_history_mv_1.cycles AS ofec_committee_history_mv_1_cycles, ofec_committee_history_mv_1.treasurer_name AS ofec_committee_history_mv_1_treasurer_name, ofec_committee_history_mv_1.treasurer_text AS ofec_committee_history_mv_1_treasurer_text, ofec_committee_history_mv_1.committee_type AS ofec_committee_history_mv_1_committee_type, ofec_committee_history_mv_1.committee_type_full AS ofec_committee_history_mv_1_committee_type_full, ofec_committee_history_mv_1.filing_frequency AS ofec_committee_history_mv_1_filing_frequency, ofec_committee_history_mv_1.designation AS ofec_committee_history_mv_1_designation, ofec_committee_history_mv_1.designation_full AS ofec_committee_history_mv_1_designation_full, ofec_committee_history_mv_1.organization_type AS ofec_committee_history_mv_1_organization_type, ofec_committee_history_mv_1.organization_type_full AS ofec_committee_history_mv_1_organization_type_full, ofec_committee_history_mv_1.party AS ofec_committee_history_mv_1_party, ofec_committee_history_mv_1.party_full AS ofec_committee_history_mv_1_party_full, ofec_committee_history_mv_1.state AS ofec_committee_history_mv_1_state, ofec_committee_history_mv_1.street_1 AS ofec_committee_history_mv_1_street_1, ofec_committee_history_mv_1.street_2 AS ofec_committee_history_mv_1_street_2, ofec_committee_history_mv_1.city AS ofec_committee_history_mv_1_city, ofec_committee_history_mv_1.state_full AS ofec_committee_history_mv_1_state_full, ofec_committee_history_mv_1.zip AS ofec_committee_history_mv_1_zip, ofec_committee_history_mv_1.candidate_ids AS ofec_committee_history_mv_1_candidate_ids, ofec_committee_history_mv_1.cycle AS ofec_committee_history_mv_1_cycle, reps_1.repid AS reps_1_repid, reps_1.form AS reps_1_form, reps_1.comid AS reps_1_comid, reps_1.com_name AS reps_1_com_name, reps_1.timestamp AS reps_1_timestamp, reps_1.create_dt AS reps_1_create_dt, reps_1.from_date AS reps_1_from_date, reps_1.through_date AS reps_1_through_date, reps_1.starting AS reps_1_starting, reps_1.ending AS reps_1_ending, reps_1.rptcode AS reps_1_rptcode, reps_1.previd AS reps_1_previd, reps_1.rptnum AS reps_1_rptnum, efiling_amendment_chain_vw_1.repid AS efiling_amendment_chain_vw_1_repid, efiling_amendment_chain_vw_1.previd AS efiling_amendment_chain_vw_1_previd, efiling_amendment_chain_vw_1.amendment_chain AS efiling_amendment_chain_vw_1_amendment_chain, efiling_amendment_chain_vw_1.longest_chain AS efiling_amendment_chain_vw_1_longest_chain, efiling_amendment_chain_vw_1.most_recent_filing AS efiling_amendment_chain_vw_1_most_recent_filing, efiling_amendment_chain_vw_1.depth AS efiling_amendment_chain_vw_1_depth, efiling_amendment_chain_vw_1.last AS efiling_amendment_chain_vw_1_last, reps_1.superceded AS reps_1_superceded FROM real_efile.sa7 LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_1 ON real_efile.sa7.comid = ofec_committee_history_mv_1.committee_id AND extract (YEAR FROM real_efile.sa7.create_dt) + CAST (extract (YEAR FROM real_efile.sa7.create_dt) AS INTEGER) % 2 = ofec_committee_history_mv_1.cycle LEFT OUTER JOIN real_efile.reps AS reps_1 ON real_efile.sa7.repid = reps_1.repid LEFT OUTER JOIN efiling_amendment_chain_vw AS efiling_amendment_chain_vw_1 ON efiling_amendment_chain_vw_1.repid = reps_1.repid WHERE real_efile.sa7.comid IN ('C00285270') AND real_efile.sa7.date_con IS NOT NULL ORDER BY real_efile.sa7.date_con DESC LIMIT 30 OFFSET 0;

 

[lbeaufort]

Addressed by #4345