Skip to content
New issue

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

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

Already on GitHub? Sign in to your account

Optimize real_efile_sb4 query #3384

Open
pkfec opened this issue Sep 14, 2018 · 0 comments
Open

Optimize real_efile_sb4 query #3384

pkfec opened this issue Sep 14, 2018 · 0 comments

Comments

@pkfec
Copy link
Contributor

pkfec commented Sep 14, 2018

From pgbadger most time consuming queries report, this query is run often and takes more than 15 sec to execute. Below are two example of the actual queries.

Day Hour Count Duration Avg duration
Sep 11 03 2 34s491ms 17s245ms
  04 1 4m51s 4m51s
  14 1 15m11s 15m11s
  18 1 13m2s

Example 1:
SELECT
real_efile_sb4.line_num AS real_efile_sb4_line_num,
real_efile_sb4.tran_id AS real_efile_sb4_tran_id,
real_efile_sb4.imageno AS real_efile_sb4_imageno,
real_efile_sb4.entity AS real_efile_sb4_entity,
real_efile_sb4.amend AS real_efile_sb4_amend,
real_efile_sb4.br_tran_id AS real_efile_sb4_br_tran_id,
real_efile_sb4.br_sname AS real_efile_sb4_br_sname,
real_efile_sb4.create_dt AS real_efile_sb4_create_dt,
real_efile_sb4.repid AS real_efile_sb4_repid,
real_efile_sb4.rel_lineno AS real_efile_sb4_rel_lineno,
real_efile_sb4.comid AS real_efile_sb4_comid,
real_efile_sb4.lname AS real_efile_sb4_lname,
real_efile_sb4.city AS real_efile_sb4_city,
real_efile_sb4.state AS real_efile_sb4_state,
real_efile_sb4.zip AS real_efile_sb4_zip,
real_efile_sb4.prefix AS real_efile_sb4_prefix,
real_efile_sb4.suffix AS real_efile_sb4_suffix,
real_efile_sb4.ben_comname AS real_efile_sb4_ben_comname,
real_efile_sb4.dis_code AS real_efile_sb4_dis_code,
real_efile_sb4.transdesc AS real_efile_sb4_transdesc,
real_efile_sb4.date_dis AS real_efile_sb4_date_dis,
real_efile_sb4.amount AS real_efile_sb4_amount,
real_efile_sb4.refund AS real_efile_sb4_refund,
real_efile_sb4.can_off AS real_efile_sb4_can_off,
real_efile_sb4.can_dist AS real_efile_sb4_can_dist,
real_efile_sb4.memo_code AS real_efile_sb4_memo_code,
real_efile_sb4.memo_text AS real_efile_sb4_memo_text,
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,
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
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.comid IN ('C00375998')
AND real_efile_sb4.date_dis IS NOT NULL
ORDER BY
real_efile_sb4.date_dis DESC
LIMIT 30 OFFSET 0;

Example 2:
SELECT
real_efile_sb4.line_num AS real_efile_sb4_line_num,
real_efile_sb4.tran_id AS real_efile_sb4_tran_id,
real_efile_sb4.imageno AS real_efile_sb4_imageno,
real_efile_sb4.entity AS real_efile_sb4_entity,
real_efile_sb4.amend AS real_efile_sb4_amend,
real_efile_sb4.br_tran_id AS real_efile_sb4_br_tran_id,
real_efile_sb4.br_sname AS real_efile_sb4_br_sname,
real_efile_sb4.create_dt AS real_efile_sb4_create_dt,
real_efile_sb4.repid AS real_efile_sb4_repid,
real_efile_sb4.rel_lineno AS real_efile_sb4_rel_lineno,
real_efile_sb4.comid AS real_efile_sb4_comid,
real_efile_sb4.lname AS real_efile_sb4_lname,
real_efile_sb4.city AS real_efile_sb4_city,
real_efile_sb4.state AS real_efile_sb4_state,
real_efile_sb4.zip AS real_efile_sb4_zip,
real_efile_sb4.prefix AS real_efile_sb4_prefix,
real_efile_sb4.suffix AS real_efile_sb4_suffix,
real_efile_sb4.ben_comname AS real_efile_sb4_ben_comname,
real_efile_sb4.dis_code AS real_efile_sb4_dis_code,
real_efile_sb4.transdesc AS real_efile_sb4_transdesc,
real_efile_sb4.date_dis AS real_efile_sb4_date_dis,
real_efile_sb4.amount AS real_efile_sb4_amount,
real_efile_sb4.refund AS real_efile_sb4_refund,
real_efile_sb4.can_off AS real_efile_sb4_can_off,
real_efile_sb4.can_dist AS real_efile_sb4_can_dist,
real_efile_sb4.memo_code AS real_efile_sb4_memo_code,
real_efile_sb4.memo_text AS real_efile_sb4_memo_text,
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,
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
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.comid IN ('C00192427')
AND real_efile_sb4.date_dis IS NOT NULL
ORDER BY
real_efile_sb4.date_dis DESC
LIMIT 30 OFFSET 0;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
Status: ❄️ Icebox
Development

No branches or pull requests

2 participants