Check slow queries (sprint 8.5)

[JonellaCulmer]

What we're after: This is a regular ticket to check slow database query performance.

• Identify and document slow queries and add logs to the following two locations:
  PGbadger: https://drive.google.com/drive/folders/1z57CHj33ib77SfR-oC0lzZcIQZfD0W6h
  AWS_rds_logs: https://drive.google.com/drive/folders/1weq69OrC47x_4tmlg099FeEpOH-oQCEN
• Open up a new ticket if any issues are identified from this log review
• Open new ticket for next sprint

[rjayasekera]

			RDS logs	KIBANA logs
Day	Date	Selects	Downloads	Downloads
Mon	4/1/19	57,541	480	3,832
Tue	4/2/19	26,233	600	993
Wed	4/3/19	34,893	248	936
Thu	4/4/19	50,770	300	876
Fri	4/5/19	43,365	278	777
Sat	4/6/19	10,850	74	120
Sun	4/7/19	36,926	22	452
Mon	4/8/19	37,361	1,555	3,491
Tue	4/9/19	35,758	840	1,521
Wed	4/10/19	41,319	1,194	2,169
Thu	4/11/19	18,063	293	967
Fri	4/12/19	17,967	346	952
Sat	4/13/19	13,088	14	163
Sun	4/14/19	25,472	228	455

[rjayasekera]

Slowest Query
26m25s	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 = 2018 AND disclosure.fec_fitem_sched_a.contb_receipt_dt >= '2017-01-01'::date AND disclosure.fec_fitem_sched_a.contb_receipt_dt <= '2018-12-31'::date AND (disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('hannity:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('spmk:')) ORDER BY disclosure.fec_fitem_sched_a.contb_receipt_dt DESC, disclosure.fec_fitem_sched_a.sub_id DESC LIMIT 30;
17m31s	copy (select * FROM real_efile_sb4, real_efile_se_f57_vw JOIN ofec_committee_history_mv ON real_efile_se_f57_vw.comid = ofec_committee_history_mv.committee_id AND extract (YEAR FROM real_efile_se_f57_vw.create_dt) + CAST (extract (YEAR FROM real_efile_se_f57_vw.create_dt) AS INTEGER) % 2 = ofec_committee_history_mv.cycle WHERE real_efile_sb4.comid IN ('C00429001')) TO STDOUT (FORMAT 'csv', HEADER TRUE);
20m32s	select * 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 ('C00511915') ORDER BY real_efile.sa7.amount DESC LIMIT 30 OFFSET 0;
14m25s	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_ [ Your log message was truncated ] WHERE disclosure.fec_fitem_sched_a.is_individual = TRUE AND disclosure.fec_fitem_sched_a.two_year_transaction_period = 2018 AND disclosure.fec_fitem_sched_a.contb_receipt_dt >= '2017-01-01'::date AND disclosure.fec_fitem_sched_a.contb_receipt_dt <= '2018-12-31'::date AND (disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('Constellis:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('academi:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('triple:* & canopy:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('International: & Development:* & Solutions:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('Centerra:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('AMK9:*')) ORDER BY disclosure.fec_fitem_sched_a.contb_receipt_dt DESC, disclosure.fec_fitem_sched_a.sub_id DESC LIMIT 30;
26m43s	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.two_year_transaction_period = 2018 AND disclosure.fec_fitem_sched_a.contb_receipt_dt >= '2017-01-01'::date AND disclosure.fec_fitem_sched_a.contb_receipt_dt <= '2018-12-31'::date AND (disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('Bhatt:* & drumi:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('Srivastava: & neha:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('Yatnatti:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('Vibhuti:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('karadigudda:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('sen:* & judhajit:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('dey: & joydeep:*')) ORDER BY disclosure.fec_fitem_sched_a.contb_receipt_dt DESC, disclosure.fec_fitem_sched_a.sub_id DESC LIMIT 30;
9h42m32s	copy (select * FROM real_efile_sb4, real_efile_se_f57_vw JOIN ofec_committee_history_mv ON real_efile_se_f57_vw.comid = ofec_committee_history_mv.committee_id AND extract (YEAR FROM real_efile_se_f57_vw.create_dt) + CAST (extract (YEAR FROM real_efile_se_f57_vw.create_dt) AS INTEGER) % 2 = ofec_committee_history_mv.cycle WHERE real_efile_sb4.comid IN ('C00501197')) TO STDOUT (FORMAT 'csv', HEADER TRUE);
30m4s	select * 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.contributor_name_text @@ to_tsquery ('Zechory:*') ORDER BY real_efile.sa7.date_con DESC LIMIT 30 OFFSET 0;
26m1s	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.two_year_transaction_period = 2016 AND disclosure.fec_fitem_sched_a.contb_receipt_dt >= '2015-01-01'::date AND disclosure.fec_fitem_sched_a.contb_receipt_dt <= '2016-12-31'::date AND (disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('gratitude:* & america:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('southern: & trust:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('financial: & trust:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('rdk:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('hbrk:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('arc: & holding:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('arkay:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('rdk:* & asset:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('COUQ:')) ORDER BY disclosure.fec_fitem_sched_a.contb_receipt_dt DESC, disclosure.fec_fitem_sched_a.sub_id DESC LIMIT 30;
30m42s	select * 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.contributor_name_text @@ to_tsquery ('C00507517:*') ORDER BY real_efile.sa7.date_con DESC LIMIT 30 OFFSET 0;
30m56s	select * 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.contributor_name_text @@ to_tsquery ('danzik:*') ORDER BY real_efile.sa7.date_con DESC LIMIT 30 OFFSET 0;
8m49s	select * FROM real_efile_sb4 LEFT OUTER JOIN real_efile.reps AS reps_1 ON real_efile_sb4.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 LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_1 ON real_efile_sb4.comid = ofec_committee_history_mv_1.committee_id AND extract (YEAR FROM real_efile_sb4.create_dt) + CAST (extract (YEAR FROM real_efile_sb4.create_dt) AS INTEGER) % 2 = ofec_committee_history_mv_1.cycle WHERE real_efile_sb4.transdesc @@ to_tsquery ('Henry:* & Alan:*') ORDER BY real_efile_sb4.date_dis DESC LIMIT 30 OFFSET 0;
25m49s	select * 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 ('C90017815') ORDER BY real_efile.sa7.date_con DESC LIMIT 30 OFFSET 0;
39m27s	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.two_year_transaction_period = 2018 AND disclosure.fec_fitem_sched_a.contb_receipt_dt >= '2017-01-01'::date AND disclosure.fec_fitem_sched_a.contb_receipt_dt <= '2018-12-31'::date AND (disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('opreah:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('oprea:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('Oprah:') OR disclosure.fec_fitem_sched_a.contributor_name_text @@ to_tsquery ('Oprah: & Winfrey:*')) ORDER BY disclosure.fec_fitem_sched_a.contb_receipt_dt DESC, disclosure.fec_fitem_sched_a.sub_id DESC LIMIT 30;
18m56s	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.two_year_transaction_period = 2018 AND disclosure.fec_fitem_sched_b.disb_dt >= '2017-01-01'::date AND disclosure.fec_fitem_sched_b.disb_dt <= '2018-12-31'::date AND (disclosure.fec_fitem_sched_b.recipient_name_text @@ to_tsquery ('rainmakers:') OR disclosure.fec_fitem_sched_b.recipient_name_text @@ to_tsquery ('chenard:') OR disclosure.fec_fitem_sched_b.recipient_name_text @@ to_tsquery ('righters:*')) 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;

[rjayasekera]

New issue open for next sprint (8.6)
#3697