More transaction list query tuning #783
Assignees
Comments
|
Example query and explain analyze: Part 1 of 2 comments (split for Exalate sync):
SELECT "transactions_transaction"."deleted",
"transactions_transaction"."committee_account_id",
"transactions_transaction"."report_id",
"transactions_transaction"."id",
"transactions_transaction"."transaction_type_identifier",
"transactions_transaction"."aggregation_group",
"transactions_transaction"."parent_transaction_id",
"transactions_transaction"."debt_id",
"transactions_transaction"."loan_id",
"transactions_transaction"."reatt_redes_id",
"transactions_transaction"."_form_type",
"transactions_transaction"."transaction_id",
"transactions_transaction"."entity_type",
"transactions_transaction"."memo_code",
"transactions_transaction"."force_itemized",
"transactions_transaction"."force_unaggregated",
"transactions_transaction"."created",
"transactions_transaction"."updated",
"transactions_transaction"."contact_1_id",
"transactions_transaction"."contact_2_id",
"transactions_transaction"."contact_3_id",
"transactions_transaction"."memo_text_id",
"transactions_transaction"."schedule_a_id",
"transactions_transaction"."schedule_b_id",
"transactions_transaction"."schedule_c_id",
"transactions_transaction"."schedule_c1_id",
"transactions_transaction"."schedule_c2_id",
"transactions_transaction"."schedule_d_id",
"transactions_transaction"."schedule_e_id",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."transaction_ptr_id",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."parent_transaction_id",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."report_id",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."schedule",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."line_label_order_key",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."_itemized",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."amount",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."date",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."effective_amount",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."aggregate",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."_calendar_ytd_per_election_office",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."back_reference_tran_id_number",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."loan_key",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."loan_payment_to_date",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."incurred_prior",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."payment_prior",
"transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."payment_amount",
CASE
WHEN "transactions_transaction"."schedule_d_id" IS NOT NULL THEN ("transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."incurred_prior" - "transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."payment_prior")
ELSE NULL
END AS "beginning_balance",
CASE
WHEN "transactions_transaction"."schedule_d_id" IS NOT NULL THEN ((CASE
WHEN "transactions_transaction"."schedule_d_id" IS NOT NULL THEN ("transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."incurred_prior" - "transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."payment_prior")
ELSE NULL
END + "transactions_scheduled"."incurred_amount") - "transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."payment_amount")
ELSE NULL
END AS "balance_at_close",
CASE
WHEN "transactions_transaction"."schedule_d_id" IS NOT NULL THEN COALESCE(CASE
WHEN "transactions_transaction"."schedule_d_id" IS NOT NULL THEN ((CASE
WHEN "transactions_transaction"."schedule_d_id" IS NOT NULL THEN ("transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."incurred_prior" - "transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."payment_prior")
ELSE NULL
END + "transactions_scheduled"."incurred_amount") - "transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."payment_amount")
ELSE NULL
END, 0)
ELSE NULL
END AS "balance",
COALESCE(T5."_itemized", T4."_itemized", "transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."_itemized") AS "itemized",
COALESCE(T4."_calendar_ytd_per_election_office", "transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."_calendar_ytd_per_election_office") AS "calendar_ytd_per_election_office"
FROM "transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"
INNER JOIN "transactions_transaction" ON ("transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."transaction_ptr_id" = "transactions_transaction"."id")
LEFT OUTER JOIN "transactions_scheduled" ON ("transactions_transaction"."schedule_d_id" = "transactions_scheduled"."id")
LEFT OUTER JOIN "transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9" T4 ON ("transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."parent_transaction_id" = T4."transaction_ptr_id")
LEFT OUTER JOIN "transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9" T5 ON (T4."parent_transaction_id" = T5."transaction_ptr_id")
WHERE ("transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."report_id" = f871b1b2-166e-4a96-859c-8e225d51dc47
AND "transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9"."schedule" IN (A))
ORDER BY "transactions_transaction"."created" DESC
LIMIT 5
556.9770000000001ms
4 joins
Query PlanLimit (cost=152720.95..152720.95 rows=1 width=897)
-> Sort (cost=152720.95..152720.95 rows=1 width=897)
Sort Key: transactions_transaction.created DESC
-> Nested Loop Left Join (cost=1315.75..152720.94 rows=1 width=897)
Join Filter: (transactions_transaction_2.parent_transaction_id = transactions_transaction_3.id)
-> Nested Loop Left Join (cost=989.89..152184.81 rows=1 width=833)
Join Filter: (transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9.parent_transaction_id = transactions_transaction_2.id)
-> Nested Loop Left Join (cost=596.97..151559.89 rows=1 width=784)
Join Filter: (transactions_transaction.schedule_d_id = transactions_scheduled.id)
-> Nested Loop (cost=596.97..151535.27 rows=1 width=768)
Join Filter: (transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9.transaction_ptr_id = transactions_transaction.id)
-> Index Scan using transactions_transaction_schedule_d_id_08d80917 on transactions_transaction (cost=0.15..91.03 rows=725 width=373)
-> Materialize (cost=596.82..151433.37 rows=1 width=395)
-> Subquery Scan on transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9 (cost=596.82..151433.36 rows=1 width=395)
Filter: ((transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9.report_id = 'f871b1b2-166e-4a96-859c-8e225d51dc47'::uuid) AND (transaction_view__c94c5d1a_9e73_464d_ad72_b73b5d8667a9.schedule = 'A'::text))
-> WindowAgg (cost=596.82..151422.49 rows=725 width=982)
-> Sort (cost=596.77..598.58 rows=725 width=681)
Sort Key: transactions_transaction_1.contact_1_id, (EXTRACT(year FROM COALESCE(transactions_schedulea.contribution_date, transactions_scheduleb.expenditure_date, transactions_schedulec.loan_incurred_date, transactions_schedulee.disbursement_date, transactions_schedulee.dissemination_date))), transactions_transaction_1.aggregation_group, (COALESCE(transactions_schedulea.contribution_date, transactions_scheduleb.expenditure_date, transactions_schedulec.loan_incurred_date, transactions_schedulee.disbursement_date, transactions_schedulee.dissemination_date)), transactions_transaction_1.created
-> WindowAgg (cost=499.79..562.32 rows=725 width=681)
-> Sort (cost=499.79..501.61 rows=725 width=649)
Sort Key: transactions_schedulee.election_code, t15.candidate_office, t15.candidate_state, t15.candidate_district, (EXTRACT(year FROM COALESCE(transactions_schedulea.contribution_date, transactions_scheduleb.expenditure_date, transactions_schedulec.loan_incurred_date, transactions_schedulee.disbursement_date, transactions_schedulee.dissemination_date))), transactions_transaction_1.aggregation_group, (COALESCE(transactions_schedulea.contribution_date, transactions_scheduleb.expenditure_date, transactions_schedulec.loan_incurred_date, transactions_schedulee.disbursement_date, transactions_schedulee.dissemination_date)), transactions_transaction_1.created
-> WindowAgg (cost=413.69..465.35 rows=725 width=649)
-> Sort (cost=413.69..415.51 rows=725 width=617)
Sort Key: transactions_transaction_1.debt_id, (CASE WHEN ((transactions_transaction_1.loan_id IS NOT NULL) AND (CASE WHEN (transactions_transaction_1.schedule_a_id IS NOT NULL) THEN 'A'::text WHEN (transactions_transaction_1.schedule_b_id IS NOT NULL) THEN 'B'::text WHEN (transactions_transaction_1.schedule_c_id IS NOT NULL) THEN 'C'::text WHEN (transactions_transaction_1.schedule_c1_id IS NOT NULL) THEN 'C2'::text WHEN (transactions_transaction_1.schedule_c2_id IS NOT NULL) THEN 'C1'::text WHEN (transactions_transaction_1.schedule_d_id IS NOT NULL) THEN 'D'::text WHEN (transactions_transaction_1.schedule_e_id IS NOT NULL) THEN 'E'::text ELSE NULL::text END = ANY ('{A,B,E}'::text[])) AND (transactions_transaction_1.transaction_type_identifier = ANY ('{''LOAN_REPAYMENT_RECEIVED'',''LOAN_REPAYMENT_MADE''}'::text[]))) THEN concat(t14.transaction_id, (COALESCE(transactions_schedulea.contribution_date, transactions_scheduleb.expenditure_date, transactions_schedulec.loan_incurred_date, transactions_schedulee.disbursement_date, transactions_schedulee.dissemination_date))::text) WHEN (transactions_transaction_1.schedule_c_id IS NOT NULL) THEN concat(transactions_transaction_1.transaction_id, (reports_report.coverage_from_date)::text) ELSE NULL::text END)
-> Hash Left Join (cost=308.60..379.25 rows=725 width=617)
Hash Cond: (transactions_transaction_1.report_id = reports_report.id)
-> Hash Left Join (cost=307.49..357.40 rows=725 width=581)
Hash Cond: (transactions_transaction_1.contact_2_id = t15.id)
-> Hash Left Join (cost=295.01..343.02 rows=725 width=525)
Hash Cond: (transactions_transaction_1.loan_id = t14.id)
-> Hash Left Join (cost=260.70..306.81 rows=725 width=504)
Hash Cond: (transactions_transaction_1.parent_transaction_id = t13.id)
-> Hash Left Join (cost=226.39..270.59 rows=725 width=483)
Hash Cond: (transactions_transaction_1.reatt_redes_id = t12.id)
-> Hash Left Join (cost=192.08..234.38 rows=725 width=478)
Hash Cond: (t9.schedule_d_id = t10.id)
-> Hash Left Join (cost=167.45..207.85 rows=725 width=478)
Hash Cond: (transactions_transaction_1.debt_id = t9.id)
-> Hash Left Join (cost=133.14..171.63 rows=725 width=441)
Hash Cond: (transactions_transaction_1.schedule_e_id = transactions_schedulee.id)
-> Hash Left Join (cost=119.31..155.90 rows=725 width=385)
Hash Cond: (transactions_transaction_1.schedule_d_id = transactions_scheduled_1.id)
-> Hash Left Join (cost=94.69..129.38 rows=725 width=369)
Hash Cond: (transactions_transaction_1.schedule_c2_id = transactions_schedulec2.id)
-> Hash Left Join (cost=54.09..86.87 rows=725 width=353)
Hash Cond: (transactions_transaction_1.schedule_c_id = transactions_schedulec.id)
-> Hash Left Join (cost=37.11..67.99 rows=725 width=333)
Hash Cond: (transactions_transaction_1.schedule_b_id = transactions_scheduleb.id)
-> Hash Left Join (cost=23.29..52.27 rows=725 width=313)
Hash Cond: (transactions_transaction_1.schedule_a_id = transactions_schedulea.id)
-> Seq Scan on transactions_transaction transactions_transaction_1 (cost=0.00..27.06 rows=725 width=304)
Filter: ((deleted IS NULL) AND (committee_account_id = 'c94c5d1a-9e73-464d-ad72-b73b5d8667a9'::uuid))
-> Hash (cost=14.24..14.24 rows=724 width=25)
-> Seq Scan on transactions_schedulea (cost=0.00..14.24 rows=724 width=25)
-> Hash (cost=11.70..11.70 rows=170 width=36)
-> Seq Scan on transactions_scheduleb (cost=0.00..11.70 rows=170 width=36)
-> Hash (cost=13.10..13.10 rows=310 width=36)
-> Seq Scan on transactions_schedulec (cost=0.00..13.10 rows=310 width=36)
-> Hash (cost=23.60..23.60 rows=1360 width=32)
-> Seq Scan on transactions_schedulec2 (cost=0.00..23.60 rows=1360 width=32)
-> Hash (cost=16.50..16.50 rows=650 width=32)
-> Seq Scan on transactions_scheduled transactions_scheduled_1 (cost=0.00..16.50 rows=650 width=32)
-> Hash (cost=11.70..11.70 rows=170 width=72)
-> Seq Scan on transactions_schedulee (cost=0.00..11.70 rows=170 width=72)
-> Hash (cost=25.25..25.25 rows=725 width=53)
-> Seq Scan on transactions_transaction t9 (cost=0.00..25.25 rows=725 width=53)
-> Hash (cost=16.50..16.50 rows=650 width=32)
-> Seq Scan on transactions_scheduled t10 (cost=0.00..16.50 rows=650 width=32)
-> Hash (cost=25.25..25.25 rows=725 width=37)
-> Seq Scan on transactions_transaction t12 (cost=0.00..25.25 rows=725 width=37)
-> Hash (cost=25.25..25.25 rows=725 width=37)
-> Seq Scan on transactions_transaction t13 (cost=0.00..25.25 rows=725 width=37)
-> Hash (cost=25.25..25.25 rows=725 width=37)
-> Seq Scan on transactions_transaction t14 (cost=0.00..25.25 rows=725 width=37)
-> Hash (cost=11.10..11.10 rows=110 width=88)
-> Seq Scan on contacts t15 (cost=0.00..11.10 rows=110 width=88)
-> Hash (cost=1.05..1.05 rows=5 width=20)
-> Seq Scan on reports_report (cost=0.00..1.05 rows=5 width=20) |
12 tasks
|
Part 2 of 2 comments (split for Exalate sync): |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Placeholder ticket
See conversation in Slack: https://fecgov.slack.com/archives/C02TE9RJEBE/p1710974659859459
Business Reason
As a [role], I will be able to [blank] so that I can [business reason]
Acceptance Criteria
If [precedent] When [action] Then [result]
QA Notes
null
DEV Notes
null
Design
null
See full ticket and images here: FECFILE-1405
The text was updated successfully, but these errors were encountered: