explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xXMf

Settings
# exclusive inclusive rows x rows loops node
1. 45,872.693 697,976.699 ↓ 1.4 764,817 1

Nested Loop Left Join (cost=17,342,129.56..54,367,500.86 rows=532,273 width=687) (actual time=522,906.016..697,976.699 rows=764,817 loops=1)

  • Output: ca.id, COALESCE((max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), 0), (SubPlan 1), (SubPlan 2), a.status_id, COALESCE((((max(m.occurred_on))::date))::timestamp with time zone, a.created_on), a.status_changed_on, COALESCE(lcc."accountClassification", 'CURRENT'::"LoanAccountClassification"), COALESCE(lcc."isRestructured", false), a."lastActionNo", (la."disbursementOn" < '2019-04-14'::date), COALESCE((ROW((t.overdue_fee)::numeric(22,2), (t.overdue_penalty)::numeric(22,2), (t.overdue_interest)::numeric(22,2), (t.overdue_principal)::numeric(22,2))::analytics."LoanAmounts"), ROW(0.00::numeric(22,2), 0.00::numeric(22,2), 0.00::numeric(22,2), 0.00::numeric(22,2))::analytics."LoanAmounts"), COALESCE((ROW((t.olb_fee)::numeric(22,2), (t.olb_penalty)::numeric(22,2), (t.olb_interest)::numeric(22,2), ((t.olb_principal + COALESCE((sum(laa."capitalizedInterest")), '0'::numeric)))::numeric(22,2))::analytics."LoanAmounts"), ROW(0.00::numeric(22,2), 0.00::numeric(22,2), 0.00::numeric(22,2), 0.00::numeric(22,2))::analytics."LoanAmounts"), COALESCE((ROW((t.paid_fee)::numeric(22,2), (t.paid_penalty)::numeric(22,2), (t.paid_interest)::numeric(22,2), (t.paid_principal)::numeric(22,2))::analytics."LoanAmounts"), ROW(0.00::numeric(22,2), 0.00::numeric(22,2), 0.00::numeric(22,2), 0.00::numeric(22,2))::analytics."LoanAmounts"), COALESCE((ROW((t.paid_late_fee)::numeric(22,2), (t.paid_late_penalty)::numeric(22,2), (t.paid_late_interest)::numeric(22,2), (t.paid_late_principal)::numeric(22,2))::analytics."LoanAmounts"), ROW(0.00::numeric(22,2), 0.00::numeric(22,2), 0.00::numeric(22,2), 0.00::numeric(22,2))::analytics."LoanAmounts"), COALESCE((ROW((COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 1)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 2)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 3)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 4)), '0'::numeric))::numeric(22,2))::analytics."LoanAmounts"), ROW(0.00::numeric(22,2), 0.00::numeric(22,2), 0.00::numeric(22,2), 0.00::numeric(22,2))::analytics."LoanAmounts"), rescheduling.rescheduled_before, ra.rescheduled_after, COALESCE((count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), '0'::bigint), COALESCE((count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), '0'::bigint), COALESCE((count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), '0'::bigint), CASE WHEN ((max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_DISBURSEMENT'::"TransactionType")))) IS NULL) THEN NULL::analytics."TransactionInfo" ELSE ROW(((min(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_DISBURSEMENT'::"TransactionType")))))::date, ((max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_DISBURSEMENT'::"TransactionType")))))::date, ((sum(t_1.amount) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_DISBURSEMENT,LOAN_REVERT_DISBURSEMENT}'::"TransactionType"[])))))::numeric(22,2), (((count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_DISBURSEMENT'::"TransactionType")) - count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_REVERT_DISBURSEMENT'::"TransactionType")))))::integer, (((array_agg(t_1.amount ORDER BY t_1."occurredOn", t_1."ID") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_DISBURSEMENT'::"TransactionType"))))[1]))::numeric(22,2), (((array_agg(t_1.amount ORDER BY t_1."occurredOn" DESC, t_1."ID" DESC) FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_DISBURSEMENT'::"TransactionType"))))[1]))::numeric(22,2))::analytics."TransactionInfo" END, CASE WHEN ((max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT}'::"TransactionType"[]))))) IS NULL) THEN NULL::analytics."TransactionInfo" ELSE ROW(((min(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT}'::"TransactionType"[]))))))::date, ((max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT}'::"TransactionType"[]))))))::date, ((sum(t_1.amount) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT,LOAN_REVERT_REPAYMENT,LOAN_REVERT_AUTOMATIC_REPAYMENT}'::"TransactionType"[])))))::numeric(22,2), (((count(*) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT}'::"TransactionType"[]))) - count(*) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_REVERT_REPAYMENT,LOAN_REVERT_AUTOMATIC_REPAYMENT}'::"TransactionType"[]))))))::integer, (((array_agg(t_1.amount ORDER BY t_1."occurredOn", t_1."ID") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT}'::"TransactionType"[])))))[1]))::numeric(22,2), (((array_agg(t_1.amount ORDER BY t_1."occurredOn" DESC, t_1."ID" DESC) FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT}'::"TransactionType"[])))))[1]))::numeric(22,2))::analytics."TransactionInfo" END, CASE WHEN ((max(t_1."occurredOn") FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType"))) IS NULL) THEN NULL::analytics."TransactionInfo" ELSE ROW(((min(t_1."occurredOn") FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType"))))::date, ((max(t_1."occurredOn") FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType"))))::date, ((sum(t_1.amount) FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType"))))::numeric(22,2), ((count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType"))))::integer, (((array_agg(t_1.amount ORDER BY t_1."occurredOn", t_1."ID") FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType")))[1]))::numeric(22,2), (((array_agg(t_1.amount ORDER BY t_1."occurredOn" DESC, t_1."ID" DESC) FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType")))[1]))::numeric(22,2))::analytics."TransactionInfo" END, CASE WHEN ((max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_RECOVERY'::"TransactionType")))) IS NULL) THEN NULL::analytics."TransactionInfo" ELSE ROW(((min(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_RECOVERY'::"TransactionType")))))::date, ((max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_RECOVERY'::"TransactionType")))))::date, ((sum(t_1.amount) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_RECOVERY,LOAN_REVERT_RECOVERY}'::"TransactionType"[])))))::numeric(22,2), (((count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_RECOVERY'::"TransactionType")) - count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_REVERT_RECOVERY'::"TransactionType")))))::integer, (((array_agg(t_1.amount ORDER BY t_1."occurredOn", t_1."ID") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_RECOVERY'::"TransactionType"))))[1]))::numeric(22,2), (((array_agg(t_1.amount ORDER BY t_1."occurredOn" DESC, t_1."ID" DESC) FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_RECOVERY'::"TransactionType"))))[1]))::numeric(22,2))::analytics."TransactionInfo" END, CASE WHEN ((max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_WRITE_OFF'::"TransactionType")))) IS NULL) THEN NULL::analytics."TransactionInfo" ELSE ROW(((min(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_WRITE_OFF'::"TransactionType")))))::date, ((max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_WRITE_OFF'::"TransactionType")))))::date, ((sum(t_1.amount) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_WRITE_OFF,LOAN_REVERT_WRITE_OFF}'::"TransactionType"[])))))::numeric(22,2), (((count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_WRITE_OFF'::"TransactionType")) - count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_REVERT_WRITE_OFF'::"TransactionType")))))::integer, (((array_agg(t_1.amount ORDER BY t_1."occurredOn", t_1."ID") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_WRITE_OFF'::"TransactionType"))))[1]))::numeric(22,2), (((array_agg(t_1.amount ORDER BY t_1."occurredOn" DESC, t_1."ID" DESC) FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_WRITE_OFF'::"TransactionType"))))[1]))::numeric(22,2))::analytics."TransactionInfo" END, (max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = ANY ('{LOAN_DISBURSEMENT,LOAN_REPAYMENT,LOAN_WRITE_OFF,LOAN_RESCHEDULING,LOAN_TOPUP,LOAN_IMPORT,LOAN_CLIENT_INTERBRANCH_MOVEMENT,LOAN_AUTOMATIC_REPAYMENT,LOAN_RECOVERY}'::"TransactionType"[]))))), (max(f.id)), CASE WHEN (instalments_with_amounts_data.id IS NULL) THEN NULL::analytics."InstalmentInfo" ELSE ROW((min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), instalments_with_amounts_data.last_payment, ROW(((CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_fee ELSE NULL::numeric END))::numeric(22,2), (CASE WHEN ((CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd((CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END)) END)::numeric(22,2), (CASE WHEN ((CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd((CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END)) END)::numeric(22,2), ((CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_principal ELSE NULL::numeric END))::numeric(22,2))::analytics."LoanAmounts", ROW((instalments_with_amounts_data.olb_fee)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data.olb_penalty < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data.olb_penalty) END)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data.olb_interest < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data.olb_interest) END)::numeric(22,2), (instalments_with_amounts_data.olb_principal)::numeric(22,2))::analytics."LoanAmounts", ROW((instalments_with_amounts_data.paid_fee)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data.paid_penalty < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data.paid_penalty) END)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data.paid_interest < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data.paid_interest) END)::numeric(22,2), (instalments_with_amounts_data.paid_principal)::numeric(22,2))::analytics."LoanAmounts")::analytics."InstalmentInfo" END, CASE WHEN (instalments_with_amounts_data_1.id IS NULL) THEN NULL::analytics."InstalmentInfo" ELSE ROW((max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), instalments_with_amounts_data_1.last_payment, ROW(((CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_fee ELSE NULL::numeric END))::numeric(22,2), (CASE WHEN ((CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_penalty ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd((CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_penalty ELSE NULL::numeric END)) END)::numeric(22,2), (CASE WHEN ((CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_interest ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd((CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_interest ELSE NULL::numeric END)) END)::numeric(22,2), ((CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_principal ELSE NULL::numeric END))::numeric(22,2))::analytics."LoanAmounts", ROW((instalments_with_amounts_data_1.olb_fee)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_1.olb_penalty < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_1.olb_penalty) END)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_1.olb_interest < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_1.olb_interest) END)::numeric(22,2), (instalments_with_amounts_data_1.olb_principal)::numeric(22,2))::analytics."LoanAmounts", ROW((instalments_with_amounts_data_1.paid_fee)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_1.paid_penalty < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_1.paid_penalty) END)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_1.paid_interest < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_1.paid_interest) END)::numeric(22,2), (instalments_with_amounts_data_1.paid_principal)::numeric(22,2))::analytics."LoanAmounts")::analytics."InstalmentInfo" END, CASE WHEN (instalments_with_amounts_data_2.id IS NULL) THEN NULL::analytics."InstalmentInfo" ELSE ROW((max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), instalments_with_amounts_data_2.last_payment, ROW(((CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_fee ELSE NULL::numeric END))::numeric(22,2), (CASE WHEN ((CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_penalty ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd((CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_penalty ELSE NULL::numeric END)) END)::numeric(22,2), (CASE WHEN ((CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_interest ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd((CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_interest ELSE NULL::numeric END)) END)::numeric(22,2), ((CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_principal ELSE NULL::numeric END))::numeric(22,2))::analytics."LoanAmounts", ROW((instalments_with_amounts_data_2.olb_fee)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_2.olb_penalty < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_2.olb_penalty) END)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_2.olb_interest < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_2.olb_interest) END)::numeric(22,2), (instalments_with_amounts_data_2.olb_principal)::numeric(22,2))::analytics."LoanAmounts", ROW((instalments_with_amounts_data_2.paid_fee)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_2.paid_penalty < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_2.paid_penalty) END)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_2.paid_interest < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_2.paid_interest) END)::numeric(22,2), (instalments_with_amounts_data_2.paid_principal)::numeric(22,2))::analytics."LoanAmounts")::analytics."InstalmentInfo" END, CASE WHEN (instalments_with_amounts_data_3.id IS NULL) THEN NULL::analytics."InstalmentInfo" ELSE ROW((max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), instalments_with_amounts_data_3.last_payment, ROW(((CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_fee ELSE NULL::numeric END))::numeric(22,2), (CASE WHEN ((CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_penalty ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd((CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_penalty ELSE NULL::numeric END)) END)::numeric(22,2), (CASE WHEN ((CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_interest ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd((CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_interest ELSE NULL::numeric END)) END)::numeric(22,2), ((CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_principal ELSE NULL::numeric END))::numeric(22,2))::analytics."LoanAmounts", ROW((instalments_with_amounts_data_3.olb_fee)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_3.olb_penalty < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_3.olb_penalty) END)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_3.olb_interest < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_3.olb_interest) END)::numeric(22,2), (instalments_with_amounts_data_3.olb_principal)::numeric(22,2))::analytics."LoanAmounts", ROW((instalments_with_amounts_data_3.paid_fee)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_3.paid_penalty < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_3.paid_penalty) END)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_3.paid_interest < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_3.paid_interest) END)::numeric(22,2), (instalments_with_amounts_data_3.paid_principal)::numeric(22,2))::analytics."LoanAmounts")::analytics."InstalmentInfo" END, CASE WHEN (instalments_with_amounts_data_4.id IS NULL) THEN NULL::analytics."InstalmentInfo" ELSE ROW((min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), instalments_with_amounts_data_4.last_payment, ROW(((CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_fee ELSE NULL::numeric END))::numeric(22,2), (CASE WHEN ((CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_penalty ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd((CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_penalty ELSE NULL::numeric END)) END)::numeric(22,2), (CASE WHEN ((CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_interest ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd((CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_interest ELSE NULL::numeric END)) END)::numeric(22,2), ((CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_principal ELSE NULL::numeric END))::numeric(22,2))::analytics."LoanAmounts", ROW((instalments_with_amounts_data_4.olb_fee)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_4.olb_penalty < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_4.olb_penalty) END)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_4.olb_interest < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_4.olb_interest) END)::numeric(22,2), (instalments_with_amounts_data_4.olb_principal)::numeric(22,2))::analytics."LoanAmounts", ROW((instalments_with_amounts_data_4.paid_fee)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_4.paid_penalty < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_4.paid_penalty) END)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_4.paid_interest < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_4.paid_interest) END)::numeric(22,2), (instalments_with_amounts_data_4.paid_principal)::numeric(22,2))::analytics."LoanAmounts")::analytics."InstalmentInfo" END, CASE WHEN (instalments_with_amounts_data_5.id IS NULL) THEN NULL::analytics."InstalmentInfo" ELSE ROW((min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), instalments_with_amounts_data_5.last_payment, ROW(((CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_fee ELSE NULL::numeric END))::numeric(22,2), (CASE WHEN ((CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_penalty ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd((CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_penalty ELSE NULL::numeric END)) END)::numeric(22,2), (CASE WHEN ((CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_interest ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd((CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_interest ELSE NULL::numeric END)) END)::numeric(22,2), ((CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_principal ELSE NULL::numeric END))::numeric(22,2))::analytics."LoanAmounts", ROW((instalments_with_amounts_data_5.olb_fee)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_5.olb_penalty < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_5.olb_penalty) END)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_5.olb_interest < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_5.olb_interest) END)::numeric(22,2), (instalments_with_amounts_data_5.olb_principal)::numeric(22,2))::analytics."LoanAmounts", ROW((instalments_with_amounts_data_5.paid_fee)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_5.paid_penalty < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_5.paid_penalty) END)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_5.paid_interest < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_5.paid_interest) END)::numeric(22,2), (instalments_with_amounts_data_5.paid_principal)::numeric(22,2))::analytics."LoanAmounts")::analytics."InstalmentInfo" END, CASE WHEN (instalments_with_amounts_data_6.id IS NULL) THEN NULL::analytics."InstalmentInfo" ELSE ROW((min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), instalments_with_amounts_data_6.last_payment, ROW(((CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_fee ELSE NULL::numeric END))::numeric(22,2), (CASE WHEN ((CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_penalty ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd((CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_penalty ELSE NULL::numeric END)) END)::numeric(22,2), (CASE WHEN ((CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_interest ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd((CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_interest ELSE NULL::numeric END)) END)::numeric(22,2), ((CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_principal ELSE NULL::numeric END))::numeric(22,2))::analytics."LoanAmounts", ROW((instalments_with_amounts_data_6.olb_fee)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_6.olb_penalty < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_6.olb_penalty) END)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_6.olb_interest < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_6.olb_interest) END)::numeric(22,2), (instalments_with_amounts_data_6.olb_principal)::numeric(22,2))::analytics."LoanAmounts", ROW((instalments_with_amounts_data_6.paid_fee)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_6.paid_penalty < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_6.paid_penalty) END)::numeric(22,2), (CASE WHEN (instalments_with_amounts_data_6.paid_interest < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(instalments_with_amounts_data_6.paid_interest) END)::numeric(22,2), (instalments_with_amounts_data_6.paid_principal)::numeric(22,2))::analytics."LoanAmounts")::analytics."InstalmentInfo" END, ca."isUpdate", (sum(laa."capitalizedInterest"))
  • Buffers: shared hit=28670430 read=825670 dirtied=43 written=39461, local hit=9144215 read=1465019, temp read=487991 written=488682
2. 1,424.363 636,807.666 ↓ 1.4 764,817 1

Merge Right Join (cost=17,342,121.10..29,703,393.65 rows=532,273 width=2,118) (actual time=522,690.212..636,807.666 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (sum(laa."capitalizedInterest")), instalments_with_amounts_data.id, instalments_with_amounts_data.last_payment, instalments_with_amounts_data.olb_fee, instalments_with_amounts_data.olb_penalty, instalments_with_amounts_data.olb_interest, instalments_with_amounts_data.olb_principal, instalments_with_amounts_data.paid_fee, instalments_with_amounts_data.paid_penalty, instalments_with_amounts_data.paid_interest, instalments_with_amounts_data.paid_principal, instalments_with_amounts_data_1.id, instalments_with_amounts_data_1.last_payment, instalments_with_amounts_data_1.olb_fee, instalments_with_amounts_data_1.olb_penalty, instalments_with_amounts_data_1.olb_interest, instalments_with_amounts_data_1.olb_principal, instalments_with_amounts_data_1.paid_fee, instalments_with_amounts_data_1.paid_penalty, instalments_with_amounts_data_1.paid_interest, instalments_with_amounts_data_1.paid_principal, instalments_with_amounts_data_2.id, instalments_with_amounts_data_2.last_payment, instalments_with_amounts_data_2.olb_fee, instalments_with_amounts_data_2.olb_penalty, instalments_with_amounts_data_2.olb_interest, instalments_with_amounts_data_2.olb_principal, instalments_with_amounts_data_2.paid_fee, instalments_with_amounts_data_2.paid_penalty, instalments_with_amounts_data_2.paid_interest, instalments_with_amounts_data_2.paid_principal, instalments_with_amounts_data_3.id, instalments_with_amounts_data_3.last_payment, instalments_with_amounts_data_3.olb_fee, instalments_with_amounts_data_3.olb_penalty, instalments_with_amounts_data_3.olb_interest, instalments_with_amounts_data_3.olb_principal, instalments_with_amounts_data_3.paid_fee, instalments_with_amounts_data_3.paid_penalty, instalments_with_amounts_data_3.paid_interest, instalments_with_amounts_data_3.paid_principal, instalments_with_amounts_data_4.id, instalments_with_amounts_data_4.last_payment, instalments_with_amounts_data_4.olb_fee, instalments_with_amounts_data_4.olb_penalty, instalments_with_amounts_data_4.olb_interest, instalments_with_amounts_data_4.olb_principal, instalments_with_amounts_data_4.paid_fee, instalments_with_amounts_data_4.paid_penalty, instalments_with_amounts_data_4.paid_interest, instalments_with_amounts_data_4.paid_principal, instalments_with_amounts_data_5.id, instalments_with_amounts_data_5.last_payment, instalments_with_amounts_data_5.olb_fee, instalments_with_amounts_data_5.olb_penalty, instalments_with_amounts_data_5.olb_interest, instalments_with_amounts_data_5.olb_principal, instalments_with_amounts_data_5.paid_fee, instalments_with_amounts_data_5.paid_penalty, instalments_with_amounts_data_5.paid_interest, instalments_with_amounts_data_5.paid_principal, instalments_with_amounts_data_6.id, instalments_with_amounts_data_6.last_payment, instalments_with_amounts_data_6.olb_fee, instalments_with_amounts_data_6.olb_penalty, instalments_with_amounts_data_6.olb_interest, instalments_with_amounts_data_6.olb_principal, instalments_with_amounts_data_6.paid_fee, instalments_with_amounts_data_6.paid_penalty, instalments_with_amounts_data_6.paid_interest, instalments_with_amounts_data_6.paid_principal, (max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_DISBURSEMENT'::"TransactionType")))), (min(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_DISBURSEMENT'::"TransactionType")))), (sum(t_1.amount) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_DISBURSEMENT,LOAN_REVERT_DISBURSEMENT}'::"TransactionType"[])))), ((count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_DISBURSEMENT'::"TransactionType")) - count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_REVERT_DISBURSEMENT'::"TransactionType")))), ((array_agg(t_1.amount ORDER BY t_1."occurredOn", t_1."ID") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_DISBURSEMENT'::"TransactionType"))))[1]), ((array_agg(t_1.amount ORDER BY t_1."occurredOn" DESC, t_1."ID" DESC) FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_DISBURSEMENT'::"TransactionType"))))[1]), (max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT}'::"TransactionType"[]))))), (min(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT}'::"TransactionType"[]))))), (sum(t_1.amount) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT,LOAN_REVERT_REPAYMENT,LOAN_REVERT_AUTOMATIC_REPAYMENT}'::"TransactionType"[])))), ((count(*) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT}'::"TransactionType"[]))) - count(*) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_REVERT_REPAYMENT,LOAN_REVERT_AUTOMATIC_REPAYMENT}'::"TransactionType"[]))))), ((array_agg(t_1.amount ORDER BY t_1."occurredOn", t_1."ID") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT}'::"TransactionType"[])))))[1]), ((array_agg(t_1.amount ORDER BY t_1."occurredOn" DESC, t_1."ID" DESC) FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT}'::"TransactionType"[])))))[1]), (max(t_1."occurredOn") FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType"))), (min(t_1."occurredOn") FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType"))), (sum(t_1.amount) FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType"))), (count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType"))), ((array_agg(t_1.amount ORDER BY t_1."occurredOn", t_1."ID") FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType")))[1]), ((array_agg(t_1.amount ORDER BY t_1."occurredOn" DESC, t_1."ID" DESC) FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType")))[1]), (max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_RECOVERY'::"TransactionType")))), (min(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_RECOVERY'::"TransactionType")))), (sum(t_1.amount) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_RECOVERY,LOAN_REVERT_RECOVERY}'::"TransactionType"[])))), ((count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_RECOVERY'::"TransactionType")) - count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_REVERT_RECOVERY'::"TransactionType")))), ((array_agg(t_1.amount ORDER BY t_1."occurredOn", t_1."ID") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_RECOVERY'::"TransactionType"))))[1]), ((array_agg(t_1.amount ORDER BY t_1."occurredOn" DESC, t_1."ID" DESC) FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_RECOVERY'::"TransactionType"))))[1]), (max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_WRITE_OFF'::"TransactionType")))), (min(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_WRITE_OFF'::"TransactionType")))), (sum(t_1.amount) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_WRITE_OFF,LOAN_REVERT_WRITE_OFF}'::"TransactionType"[])))), ((count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_WRITE_OFF'::"TransactionType")) - count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_REVERT_WRITE_OFF'::"TransactionType")))), ((array_agg(t_1.amount ORDER BY t_1."occurredOn", t_1."ID") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_WRITE_OFF'::"TransactionType"))))[1]), ((array_agg(t_1.amount ORDER BY t_1."occurredOn" DESC, t_1."ID" DESC) FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_WRITE_OFF'::"TransactionType"))))[1]), (max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = ANY ('{LOAN_DISBURSEMENT,LOAN_REPAYMENT,LOAN_WRITE_OFF,LOAN_RESCHEDULING,LOAN_TOPUP,LOAN_IMPORT,LOAN_CLIENT_INTERBRANCH_MOVEMENT,LOAN_AUTOMATIC_REPAYMENT,LOAN_RECOVERY}'::"TransactionType"[]))))), (max(f.id)), (ROW((COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 1)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 2)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 3)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 4)), '0'::numeric))::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.overdue_fee)::numeric(22,2), (t.overdue_penalty)::numeric(22,2), (t.overdue_interest)::numeric(22,2), (t.overdue_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.olb_fee)::numeric(22,2), (t.olb_penalty)::numeric(22,2), (t.olb_interest)::numeric(22,2), ((t.olb_principal + COALESCE((sum(laa."capitalizedInterest")), '0'::numeric)))::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_fee)::numeric(22,2), (t.paid_penalty)::numeric(22,2), (t.paid_interest)::numeric(22,2), (t.paid_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_late_fee)::numeric(22,2), (t.paid_late_penalty)::numeric(22,2), (t.paid_late_interest)::numeric(22,2), (t.paid_late_principal)::numeric(22,2))::analytics."LoanAmounts"), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_principal ELSE NULL::numeric END)
  • Merge Cond: (t_1."accountID" = ca.id)
  • Buffers: shared hit=17380130 read=811551 dirtied=28 written=39461, local hit=9144215 read=1465019, temp read=487991 written=488682
3. 40,643.456 95,775.906 ↓ 1.1 732,042 1

GroupAggregate (cost=5,377,184.56..11,281,079.87 rows=690,716 width=624) (actual time=36,367.323..95,775.906 rows=732,042 loops=1)

  • Output: t_1."accountID", max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = ANY ('{LOAN_DISBURSEMENT,LOAN_REPAYMENT,LOAN_WRITE_OFF,LOAN_RESCHEDULING,LOAN_TOPUP,LOAN_IMPORT,LOAN_CLIENT_INTERBRANCH_MOVEMENT,LOAN_AUTOMATIC_REPAYMENT,LOAN_RECOVERY}'::"TransactionType"[])))), min(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_DISBURSEMENT'::"TransactionType"))), max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_DISBURSEMENT'::"TransactionType"))), sum(t_1.amount) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_DISBURSEMENT,LOAN_REVERT_DISBURSEMENT}'::"TransactionType"[]))), (array_agg(t_1.amount ORDER BY t_1."occurredOn", t_1."ID") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_DISBURSEMENT'::"TransactionType"))))[1], (array_agg(t_1.amount ORDER BY t_1."occurredOn" DESC, t_1."ID" DESC) FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_DISBURSEMENT'::"TransactionType"))))[1], (count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_DISBURSEMENT'::"TransactionType")) - count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_REVERT_DISBURSEMENT'::"TransactionType"))), min(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT}'::"TransactionType"[])))), max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT}'::"TransactionType"[])))), sum(t_1.amount) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT,LOAN_REVERT_REPAYMENT,LOAN_REVERT_AUTOMATIC_REPAYMENT}'::"TransactionType"[]))), (array_agg(t_1.amount ORDER BY t_1."occurredOn", t_1."ID") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT}'::"TransactionType"[])))))[1], (array_agg(t_1.amount ORDER BY t_1."occurredOn" DESC, t_1."ID" DESC) FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT}'::"TransactionType"[])))))[1], (count(*) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_REPAYMENT,LOAN_AUTOMATIC_REPAYMENT}'::"TransactionType"[]))) - count(*) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_REVERT_REPAYMENT,LOAN_REVERT_AUTOMATIC_REPAYMENT}'::"TransactionType"[])))), min(t_1."occurredOn") FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType")), max(t_1."occurredOn") FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType")), sum(t_1.amount) FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType")), (array_agg(t_1.amount ORDER BY t_1."occurredOn", t_1."ID") FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType")))[1], (array_agg(t_1.amount ORDER BY t_1."occurredOn" DESC, t_1."ID" DESC) FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType")))[1], count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_TOPUP'::"TransactionType")), min(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_RECOVERY'::"TransactionType"))), max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_RECOVERY'::"TransactionType"))), sum(t_1.amount) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_RECOVERY,LOAN_REVERT_RECOVERY}'::"TransactionType"[]))), (array_agg(t_1.amount ORDER BY t_1."occurredOn", t_1."ID") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_RECOVERY'::"TransactionType"))))[1], (array_agg(t_1.amount ORDER BY t_1."occurredOn" DESC, t_1."ID" DESC) FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_RECOVERY'::"TransactionType"))))[1], (count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_RECOVERY'::"TransactionType")) - count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_REVERT_RECOVERY'::"TransactionType"))), min(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_WRITE_OFF'::"TransactionType"))), max(t_1."occurredOn") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_WRITE_OFF'::"TransactionType"))), sum(t_1.amount) FILTER (WHERE (t_1."transactionType" = ANY ('{LOAN_WRITE_OFF,LOAN_REVERT_WRITE_OFF}'::"TransactionType"[]))), (array_agg(t_1.amount ORDER BY t_1."occurredOn", t_1."ID") FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_WRITE_OFF'::"TransactionType"))))[1], (array_agg(t_1.amount ORDER BY t_1."occurredOn" DESC, t_1."ID" DESC) FILTER (WHERE ((t_1."revertedByID" IS NULL) AND (t_1."transactionType" = 'LOAN_WRITE_OFF'::"TransactionType"))))[1], (count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_WRITE_OFF'::"TransactionType")) - count(*) FILTER (WHERE (t_1."transactionType" = 'LOAN_REVERT_WRITE_OFF'::"TransactionType"))), max(f.id)
  • Group Key: t_1."accountID
  • Buffers: shared hit=1759 read=383814, local hit=6 read=6221, temp read=171644 written=172347
4. 3,806.418 55,132.450 ↑ 2.6 10,778,192 1

Merge Join (cost=5,377,184.56..5,893,171.81 rows=27,964,402 width=49) (actual time=36,367.149..55,132.450 rows=10,778,192 loops=1)

  • Output: t_1."accountID", t_1."occurredOn", t_1."revertedByID", t_1."transactionType", t_1.amount, t_1."ID", f.id
  • Merge Cond: (ca_1.id = t_1."accountID")
  • Buffers: shared hit=1756 read=383814, local hit=6 read=6221, temp read=171644 written=172347
5. 366.901 366.901 ↑ 1.0 764,814 1

Index Only Scan using ix_prepare_changed_accounts on pg_temp_21.prepare_changed_accounts ca_1 (cost=0.42..24,010.68 rows=764,817 width=8) (actual time=0.021..366.901 rows=764,814 loops=1)

  • Output: ca_1.id
  • Heap Fetches: 764814
  • Buffers: local hit=6 read=6221
6. 2,396.214 50,959.131 ↑ 1.0 27,944,820 1

Materialize (cost=5,377,170.25..5,518,423.63 rows=28,250,676 width=49) (actual time=36,367.123..50,959.131 rows=27,944,820 loops=1)

  • Output: t_1."accountID", t_1."occurredOn", t_1."revertedByID", t_1."transactionType", t_1.amount, t_1."ID", f.id
  • Buffers: shared hit=1756 read=383814, temp read=171644 written=172347
7. 25,236.902 48,562.917 ↑ 1.0 27,944,820 1

Sort (cost=5,377,170.25..5,447,796.94 rows=28,250,676 width=49) (actual time=36,367.120..48,562.917 rows=27,944,820 loops=1)

  • Output: t_1."accountID", t_1."occurredOn", t_1."revertedByID", t_1."transactionType", t_1.amount, t_1."ID", f.id
  • Sort Key: t_1."accountID
  • Sort Method: external merge Disk: 1377216kB
  • Buffers: shared hit=1756 read=383814, temp read=171644 written=172347
8. 4,767.148 23,326.015 ↓ 1.0 28,251,513 1

Hash Left Join (cost=23.50..915,296.37 rows=28,250,676 width=49) (actual time=60.204..23,326.015 rows=28,251,513 loops=1)

  • Output: t_1."accountID", t_1."occurredOn", t_1."revertedByID", t_1."transactionType", t_1.amount, t_1."ID", f.id
  • Hash Cond: (t_1."ID" = f.account_transaction_id)
  • Join Filter: (t_1."revertedByID" IS NULL)
  • Buffers: shared hit=1756 read=383814
9. 18,558.861 18,558.861 ↓ 1.0 28,251,513 1

Seq Scan on instafin."Transaction" t_1 (cost=0.00..809,330.14 rows=28,250,676 width=41) (actual time=60.185..18,558.861 rows=28,251,513 loops=1)

  • Output: t_1."ID", t_1."accountID", t_1."userID", t_1."occurredOn", t_1.amount, t_1.notes, t_1."createdOn", t_1."revertedByID", t_1."paymentMethodID", t_1."transactionBranchID", t_1."transactionType", t_1."clientID", t_1."serviceProductID", t_1.status
  • Filter: ((t_1."occurredOn" < '2019-04-14'::date) AND (t_1.status = 'APPROVED'::"TransactionStatus"))
  • Buffers: shared hit=1756 read=383814
10. 0.001 0.006 ↓ 0.0 0 1

Hash (cost=20.12..20.12 rows=270 width=16) (actual time=0.006..0.006 rows=0 loops=1)

  • Output: f.id, f.account_transaction_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
11. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on instafin.funding_source_transactions f (cost=0.00..20.12 rows=270 width=16) (actual time=0.005..0.005 rows=0 loops=1)

  • Output: f.id, f.account_transaction_id
  • Filter: ((f.amount)::numeric > '0'::numeric)
12. 1,015.565 539,607.397 ↓ 1.4 764,817 1

Materialize (cost=11,964,936.55..18,407,542.12 rows=532,273 width=1,502) (actual time=486,322.884..539,607.397 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (sum(laa."capitalizedInterest")), instalments_with_amounts_data.id, instalments_with_amounts_data.last_payment, instalments_with_amounts_data.olb_fee, instalments_with_amounts_data.olb_penalty, instalments_with_amounts_data.olb_interest, instalments_with_amounts_data.olb_principal, instalments_with_amounts_data.paid_fee, instalments_with_amounts_data.paid_penalty, instalments_with_amounts_data.paid_interest, instalments_with_amounts_data.paid_principal, instalments_with_amounts_data_1.id, instalments_with_amounts_data_1.last_payment, instalments_with_amounts_data_1.olb_fee, instalments_with_amounts_data_1.olb_penalty, instalments_with_amounts_data_1.olb_interest, instalments_with_amounts_data_1.olb_principal, instalments_with_amounts_data_1.paid_fee, instalments_with_amounts_data_1.paid_penalty, instalments_with_amounts_data_1.paid_interest, instalments_with_amounts_data_1.paid_principal, instalments_with_amounts_data_2.id, instalments_with_amounts_data_2.last_payment, instalments_with_amounts_data_2.olb_fee, instalments_with_amounts_data_2.olb_penalty, instalments_with_amounts_data_2.olb_interest, instalments_with_amounts_data_2.olb_principal, instalments_with_amounts_data_2.paid_fee, instalments_with_amounts_data_2.paid_penalty, instalments_with_amounts_data_2.paid_interest, instalments_with_amounts_data_2.paid_principal, instalments_with_amounts_data_3.id, instalments_with_amounts_data_3.last_payment, instalments_with_amounts_data_3.olb_fee, instalments_with_amounts_data_3.olb_penalty, instalments_with_amounts_data_3.olb_interest, instalments_with_amounts_data_3.olb_principal, instalments_with_amounts_data_3.paid_fee, instalments_with_amounts_data_3.paid_penalty, instalments_with_amounts_data_3.paid_interest, instalments_with_amounts_data_3.paid_principal, instalments_with_amounts_data_4.id, instalments_with_amounts_data_4.last_payment, instalments_with_amounts_data_4.olb_fee, instalments_with_amounts_data_4.olb_penalty, instalments_with_amounts_data_4.olb_interest, instalments_with_amounts_data_4.olb_principal, instalments_with_amounts_data_4.paid_fee, instalments_with_amounts_data_4.paid_penalty, instalments_with_amounts_data_4.paid_interest, instalments_with_amounts_data_4.paid_principal, instalments_with_amounts_data_5.id, instalments_with_amounts_data_5.last_payment, instalments_with_amounts_data_5.olb_fee, instalments_with_amounts_data_5.olb_penalty, instalments_with_amounts_data_5.olb_interest, instalments_with_amounts_data_5.olb_principal, instalments_with_amounts_data_5.paid_fee, instalments_with_amounts_data_5.paid_penalty, instalments_with_amounts_data_5.paid_interest, instalments_with_amounts_data_5.paid_principal, instalments_with_amounts_data_6.id, instalments_with_amounts_data_6.last_payment, instalments_with_amounts_data_6.olb_fee, instalments_with_amounts_data_6.olb_penalty, instalments_with_amounts_data_6.olb_interest, instalments_with_amounts_data_6.olb_principal, instalments_with_amounts_data_6.paid_fee, instalments_with_amounts_data_6.paid_penalty, instalments_with_amounts_data_6.paid_interest, instalments_with_amounts_data_6.paid_principal, (ROW((COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 1)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 2)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 3)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 4)), '0'::numeric))::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.overdue_fee)::numeric(22,2), (t.overdue_penalty)::numeric(22,2), (t.overdue_interest)::numeric(22,2), (t.overdue_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.olb_fee)::numeric(22,2), (t.olb_penalty)::numeric(22,2), (t.olb_interest)::numeric(22,2), ((t.olb_principal + COALESCE((sum(laa."capitalizedInterest")), '0'::numeric)))::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_fee)::numeric(22,2), (t.paid_penalty)::numeric(22,2), (t.paid_interest)::numeric(22,2), (t.paid_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_late_fee)::numeric(22,2), (t.paid_late_penalty)::numeric(22,2), (t.paid_late_interest)::numeric(22,2), (t.paid_late_principal)::numeric(22,2))::analytics."LoanAmounts"), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_principal ELSE NULL::numeric END)
  • Buffers: shared hit=17378371 read=427737 dirtied=28 written=39461, local hit=9144209 read=1458798, temp read=316347 written=316335
13. 328.382 538,591.832 ↓ 1.4 764,817 1

Merge Left Join (cost=11,964,936.55..18,406,211.44 rows=532,273 width=1,502) (actual time=486,322.881..538,591.832 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (sum(laa."capitalizedInterest")), instalments_with_amounts_data.id, instalments_with_amounts_data.last_payment, instalments_with_amounts_data.olb_fee, instalments_with_amounts_data.olb_penalty, instalments_with_amounts_data.olb_interest, instalments_with_amounts_data.olb_principal, instalments_with_amounts_data.paid_fee, instalments_with_amounts_data.paid_penalty, instalments_with_amounts_data.paid_interest, instalments_with_amounts_data.paid_principal, instalments_with_amounts_data_1.id, instalments_with_amounts_data_1.last_payment, instalments_with_amounts_data_1.olb_fee, instalments_with_amounts_data_1.olb_penalty, instalments_with_amounts_data_1.olb_interest, instalments_with_amounts_data_1.olb_principal, instalments_with_amounts_data_1.paid_fee, instalments_with_amounts_data_1.paid_penalty, instalments_with_amounts_data_1.paid_interest, instalments_with_amounts_data_1.paid_principal, instalments_with_amounts_data_2.id, instalments_with_amounts_data_2.last_payment, instalments_with_amounts_data_2.olb_fee, instalments_with_amounts_data_2.olb_penalty, instalments_with_amounts_data_2.olb_interest, instalments_with_amounts_data_2.olb_principal, instalments_with_amounts_data_2.paid_fee, instalments_with_amounts_data_2.paid_penalty, instalments_with_amounts_data_2.paid_interest, instalments_with_amounts_data_2.paid_principal, instalments_with_amounts_data_3.id, instalments_with_amounts_data_3.last_payment, instalments_with_amounts_data_3.olb_fee, instalments_with_amounts_data_3.olb_penalty, instalments_with_amounts_data_3.olb_interest, instalments_with_amounts_data_3.olb_principal, instalments_with_amounts_data_3.paid_fee, instalments_with_amounts_data_3.paid_penalty, instalments_with_amounts_data_3.paid_interest, instalments_with_amounts_data_3.paid_principal, instalments_with_amounts_data_4.id, instalments_with_amounts_data_4.last_payment, instalments_with_amounts_data_4.olb_fee, instalments_with_amounts_data_4.olb_penalty, instalments_with_amounts_data_4.olb_interest, instalments_with_amounts_data_4.olb_principal, instalments_with_amounts_data_4.paid_fee, instalments_with_amounts_data_4.paid_penalty, instalments_with_amounts_data_4.paid_interest, instalments_with_amounts_data_4.paid_principal, instalments_with_amounts_data_5.id, instalments_with_amounts_data_5.last_payment, instalments_with_amounts_data_5.olb_fee, instalments_with_amounts_data_5.olb_penalty, instalments_with_amounts_data_5.olb_interest, instalments_with_amounts_data_5.olb_principal, instalments_with_amounts_data_5.paid_fee, instalments_with_amounts_data_5.paid_penalty, instalments_with_amounts_data_5.paid_interest, instalments_with_amounts_data_5.paid_principal, instalments_with_amounts_data_6.id, instalments_with_amounts_data_6.last_payment, instalments_with_amounts_data_6.olb_fee, instalments_with_amounts_data_6.olb_penalty, instalments_with_amounts_data_6.olb_interest, instalments_with_amounts_data_6.olb_principal, instalments_with_amounts_data_6.paid_fee, instalments_with_amounts_data_6.paid_penalty, instalments_with_amounts_data_6.paid_interest, instalments_with_amounts_data_6.paid_principal, (ROW((COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 1)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 2)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 3)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 4)), '0'::numeric))::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.overdue_fee)::numeric(22,2), (t.overdue_penalty)::numeric(22,2), (t.overdue_interest)::numeric(22,2), (t.overdue_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.olb_fee)::numeric(22,2), (t.olb_penalty)::numeric(22,2), (t.olb_interest)::numeric(22,2), ((t.olb_principal + COALESCE((sum(laa."capitalizedInterest")), '0'::numeric)))::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_fee)::numeric(22,2), (t.paid_penalty)::numeric(22,2), (t.paid_interest)::numeric(22,2), (t.paid_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_late_fee)::numeric(22,2), (t.paid_late_penalty)::numeric(22,2), (t.paid_late_interest)::numeric(22,2), (t.paid_late_principal)::numeric(22,2))::analytics."LoanAmounts"), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_principal ELSE NULL::numeric END)
  • Merge Cond: (ca.id = lars."accountId")
  • Buffers: shared hit=17378371 read=427737 dirtied=28 written=39461, local hit=9144209 read=1458798, temp read=316347 written=316335
14. 248.064 538,263.438 ↓ 1.4 764,817 1

Nested Loop Left Join (cost=11,964,925.30..18,404,869.43 rows=532,273 width=1,470) (actual time=486,322.866..538,263.438 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (sum(laa."capitalizedInterest")), instalments_with_amounts_data.id, instalments_with_amounts_data.last_payment, instalments_with_amounts_data.olb_fee, instalments_with_amounts_data.olb_penalty, instalments_with_amounts_data.olb_interest, instalments_with_amounts_data.olb_principal, instalments_with_amounts_data.paid_fee, instalments_with_amounts_data.paid_penalty, instalments_with_amounts_data.paid_interest, instalments_with_amounts_data.paid_principal, instalments_with_amounts_data_1.id, instalments_with_amounts_data_1.last_payment, instalments_with_amounts_data_1.olb_fee, instalments_with_amounts_data_1.olb_penalty, instalments_with_amounts_data_1.olb_interest, instalments_with_amounts_data_1.olb_principal, instalments_with_amounts_data_1.paid_fee, instalments_with_amounts_data_1.paid_penalty, instalments_with_amounts_data_1.paid_interest, instalments_with_amounts_data_1.paid_principal, instalments_with_amounts_data_2.id, instalments_with_amounts_data_2.last_payment, instalments_with_amounts_data_2.olb_fee, instalments_with_amounts_data_2.olb_penalty, instalments_with_amounts_data_2.olb_interest, instalments_with_amounts_data_2.olb_principal, instalments_with_amounts_data_2.paid_fee, instalments_with_amounts_data_2.paid_penalty, instalments_with_amounts_data_2.paid_interest, instalments_with_amounts_data_2.paid_principal, instalments_with_amounts_data_3.id, instalments_with_amounts_data_3.last_payment, instalments_with_amounts_data_3.olb_fee, instalments_with_amounts_data_3.olb_penalty, instalments_with_amounts_data_3.olb_interest, instalments_with_amounts_data_3.olb_principal, instalments_with_amounts_data_3.paid_fee, instalments_with_amounts_data_3.paid_penalty, instalments_with_amounts_data_3.paid_interest, instalments_with_amounts_data_3.paid_principal, instalments_with_amounts_data_4.id, instalments_with_amounts_data_4.last_payment, instalments_with_amounts_data_4.olb_fee, instalments_with_amounts_data_4.olb_penalty, instalments_with_amounts_data_4.olb_interest, instalments_with_amounts_data_4.olb_principal, instalments_with_amounts_data_4.paid_fee, instalments_with_amounts_data_4.paid_penalty, instalments_with_amounts_data_4.paid_interest, instalments_with_amounts_data_4.paid_principal, instalments_with_amounts_data_5.id, instalments_with_amounts_data_5.last_payment, instalments_with_amounts_data_5.olb_fee, instalments_with_amounts_data_5.olb_penalty, instalments_with_amounts_data_5.olb_interest, instalments_with_amounts_data_5.olb_principal, instalments_with_amounts_data_5.paid_fee, instalments_with_amounts_data_5.paid_penalty, instalments_with_amounts_data_5.paid_interest, instalments_with_amounts_data_5.paid_principal, instalments_with_amounts_data_6.id, instalments_with_amounts_data_6.last_payment, instalments_with_amounts_data_6.olb_fee, instalments_with_amounts_data_6.olb_penalty, instalments_with_amounts_data_6.olb_interest, instalments_with_amounts_data_6.olb_principal, instalments_with_amounts_data_6.paid_fee, instalments_with_amounts_data_6.paid_penalty, instalments_with_amounts_data_6.paid_interest, instalments_with_amounts_data_6.paid_principal, (ROW((t.overdue_fee)::numeric(22,2), (t.overdue_penalty)::numeric(22,2), (t.overdue_interest)::numeric(22,2), (t.overdue_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.olb_fee)::numeric(22,2), (t.olb_penalty)::numeric(22,2), (t.olb_interest)::numeric(22,2), ((t.olb_principal + COALESCE((sum(laa."capitalizedInterest")), '0'::numeric)))::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_fee)::numeric(22,2), (t.paid_penalty)::numeric(22,2), (t.paid_interest)::numeric(22,2), (t.paid_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_late_fee)::numeric(22,2), (t.paid_late_penalty)::numeric(22,2), (t.paid_late_interest)::numeric(22,2), (t.paid_late_principal)::numeric(22,2))::analytics."LoanAmounts"), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_principal ELSE NULL::numeric END)
  • Buffers: shared hit=17378371 read=427736 dirtied=28 written=39461, local hit=9144209 read=1458798, temp read=316347 written=316335
15. 174.602 537,250.557 ↓ 1.4 764,817 1

Nested Loop Left Join (cost=11,964,924.86..17,399,178.40 rows=532,273 width=1,307) (actual time=486,322.865..537,250.557 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (sum(laa."capitalizedInterest")), instalments_with_amounts_data.id, instalments_with_amounts_data.last_payment, instalments_with_amounts_data.olb_fee, instalments_with_amounts_data.olb_penalty, instalments_with_amounts_data.olb_interest, instalments_with_amounts_data.olb_principal, instalments_with_amounts_data.paid_fee, instalments_with_amounts_data.paid_penalty, instalments_with_amounts_data.paid_interest, instalments_with_amounts_data.paid_principal, instalments_with_amounts_data_1.id, instalments_with_amounts_data_1.last_payment, instalments_with_amounts_data_1.olb_fee, instalments_with_amounts_data_1.olb_penalty, instalments_with_amounts_data_1.olb_interest, instalments_with_amounts_data_1.olb_principal, instalments_with_amounts_data_1.paid_fee, instalments_with_amounts_data_1.paid_penalty, instalments_with_amounts_data_1.paid_interest, instalments_with_amounts_data_1.paid_principal, instalments_with_amounts_data_2.id, instalments_with_amounts_data_2.last_payment, instalments_with_amounts_data_2.olb_fee, instalments_with_amounts_data_2.olb_penalty, instalments_with_amounts_data_2.olb_interest, instalments_with_amounts_data_2.olb_principal, instalments_with_amounts_data_2.paid_fee, instalments_with_amounts_data_2.paid_penalty, instalments_with_amounts_data_2.paid_interest, instalments_with_amounts_data_2.paid_principal, instalments_with_amounts_data_3.id, instalments_with_amounts_data_3.last_payment, instalments_with_amounts_data_3.olb_fee, instalments_with_amounts_data_3.olb_penalty, instalments_with_amounts_data_3.olb_interest, instalments_with_amounts_data_3.olb_principal, instalments_with_amounts_data_3.paid_fee, instalments_with_amounts_data_3.paid_penalty, instalments_with_amounts_data_3.paid_interest, instalments_with_amounts_data_3.paid_principal, instalments_with_amounts_data_4.id, instalments_with_amounts_data_4.last_payment, instalments_with_amounts_data_4.olb_fee, instalments_with_amounts_data_4.olb_penalty, instalments_with_amounts_data_4.olb_interest, instalments_with_amounts_data_4.olb_principal, instalments_with_amounts_data_4.paid_fee, instalments_with_amounts_data_4.paid_penalty, instalments_with_amounts_data_4.paid_interest, instalments_with_amounts_data_4.paid_principal, instalments_with_amounts_data_5.id, instalments_with_amounts_data_5.last_payment, instalments_with_amounts_data_5.olb_fee, instalments_with_amounts_data_5.olb_penalty, instalments_with_amounts_data_5.olb_interest, instalments_with_amounts_data_5.olb_principal, instalments_with_amounts_data_5.paid_fee, instalments_with_amounts_data_5.paid_penalty, instalments_with_amounts_data_5.paid_interest, instalments_with_amounts_data_5.paid_principal, (ROW((t.overdue_fee)::numeric(22,2), (t.overdue_penalty)::numeric(22,2), (t.overdue_interest)::numeric(22,2), (t.overdue_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.olb_fee)::numeric(22,2), (t.olb_penalty)::numeric(22,2), (t.olb_interest)::numeric(22,2), ((t.olb_principal + COALESCE((sum(laa."capitalizedInterest")), '0'::numeric)))::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_fee)::numeric(22,2), (t.paid_penalty)::numeric(22,2), (t.paid_interest)::numeric(22,2), (t.paid_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_late_fee)::numeric(22,2), (t.paid_late_penalty)::numeric(22,2), (t.paid_late_interest)::numeric(22,2), (t.paid_late_principal)::numeric(22,2))::analytics."LoanAmounts"), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_principal ELSE NULL::numeric END)
  • Buffers: shared hit=17378371 read=427736 dirtied=28 written=39461, local hit=7833521 read=1458782, temp read=316347 written=316335
16. 172.257 536,311.138 ↓ 1.4 764,817 1

Nested Loop Left Join (cost=11,964,924.42..16,393,487.37 rows=532,273 width=1,144) (actual time=486,322.863..536,311.138 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (sum(laa."capitalizedInterest")), instalments_with_amounts_data.id, instalments_with_amounts_data.last_payment, instalments_with_amounts_data.olb_fee, instalments_with_amounts_data.olb_penalty, instalments_with_amounts_data.olb_interest, instalments_with_amounts_data.olb_principal, instalments_with_amounts_data.paid_fee, instalments_with_amounts_data.paid_penalty, instalments_with_amounts_data.paid_interest, instalments_with_amounts_data.paid_principal, instalments_with_amounts_data_1.id, instalments_with_amounts_data_1.last_payment, instalments_with_amounts_data_1.olb_fee, instalments_with_amounts_data_1.olb_penalty, instalments_with_amounts_data_1.olb_interest, instalments_with_amounts_data_1.olb_principal, instalments_with_amounts_data_1.paid_fee, instalments_with_amounts_data_1.paid_penalty, instalments_with_amounts_data_1.paid_interest, instalments_with_amounts_data_1.paid_principal, instalments_with_amounts_data_2.id, instalments_with_amounts_data_2.last_payment, instalments_with_amounts_data_2.olb_fee, instalments_with_amounts_data_2.olb_penalty, instalments_with_amounts_data_2.olb_interest, instalments_with_amounts_data_2.olb_principal, instalments_with_amounts_data_2.paid_fee, instalments_with_amounts_data_2.paid_penalty, instalments_with_amounts_data_2.paid_interest, instalments_with_amounts_data_2.paid_principal, instalments_with_amounts_data_3.id, instalments_with_amounts_data_3.last_payment, instalments_with_amounts_data_3.olb_fee, instalments_with_amounts_data_3.olb_penalty, instalments_with_amounts_data_3.olb_interest, instalments_with_amounts_data_3.olb_principal, instalments_with_amounts_data_3.paid_fee, instalments_with_amounts_data_3.paid_penalty, instalments_with_amounts_data_3.paid_interest, instalments_with_amounts_data_3.paid_principal, instalments_with_amounts_data_4.id, instalments_with_amounts_data_4.last_payment, instalments_with_amounts_data_4.olb_fee, instalments_with_amounts_data_4.olb_penalty, instalments_with_amounts_data_4.olb_interest, instalments_with_amounts_data_4.olb_principal, instalments_with_amounts_data_4.paid_fee, instalments_with_amounts_data_4.paid_penalty, instalments_with_amounts_data_4.paid_interest, instalments_with_amounts_data_4.paid_principal, (ROW((t.overdue_fee)::numeric(22,2), (t.overdue_penalty)::numeric(22,2), (t.overdue_interest)::numeric(22,2), (t.overdue_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.olb_fee)::numeric(22,2), (t.olb_penalty)::numeric(22,2), (t.olb_interest)::numeric(22,2), ((t.olb_principal + COALESCE((sum(laa."capitalizedInterest")), '0'::numeric)))::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_fee)::numeric(22,2), (t.paid_penalty)::numeric(22,2), (t.paid_interest)::numeric(22,2), (t.paid_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_late_fee)::numeric(22,2), (t.paid_late_penalty)::numeric(22,2), (t.paid_late_interest)::numeric(22,2), (t.paid_late_principal)::numeric(22,2))::analytics."LoanAmounts"), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_principal ELSE NULL::numeric END)
  • Buffers: shared hit=17378371 read=427736 dirtied=28 written=39461, local hit=6613209 read=1458285, temp read=316347 written=316335
17. 88.365 535,374.064 ↓ 1.4 764,817 1

Nested Loop Left Join (cost=11,964,923.99..15,387,796.35 rows=532,273 width=981) (actual time=486,322.861..535,374.064 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (sum(laa."capitalizedInterest")), instalments_with_amounts_data.id, instalments_with_amounts_data.last_payment, instalments_with_amounts_data.olb_fee, instalments_with_amounts_data.olb_penalty, instalments_with_amounts_data.olb_interest, instalments_with_amounts_data.olb_principal, instalments_with_amounts_data.paid_fee, instalments_with_amounts_data.paid_penalty, instalments_with_amounts_data.paid_interest, instalments_with_amounts_data.paid_principal, instalments_with_amounts_data_1.id, instalments_with_amounts_data_1.last_payment, instalments_with_amounts_data_1.olb_fee, instalments_with_amounts_data_1.olb_penalty, instalments_with_amounts_data_1.olb_interest, instalments_with_amounts_data_1.olb_principal, instalments_with_amounts_data_1.paid_fee, instalments_with_amounts_data_1.paid_penalty, instalments_with_amounts_data_1.paid_interest, instalments_with_amounts_data_1.paid_principal, instalments_with_amounts_data_2.id, instalments_with_amounts_data_2.last_payment, instalments_with_amounts_data_2.olb_fee, instalments_with_amounts_data_2.olb_penalty, instalments_with_amounts_data_2.olb_interest, instalments_with_amounts_data_2.olb_principal, instalments_with_amounts_data_2.paid_fee, instalments_with_amounts_data_2.paid_penalty, instalments_with_amounts_data_2.paid_interest, instalments_with_amounts_data_2.paid_principal, instalments_with_amounts_data_3.id, instalments_with_amounts_data_3.last_payment, instalments_with_amounts_data_3.olb_fee, instalments_with_amounts_data_3.olb_penalty, instalments_with_amounts_data_3.olb_interest, instalments_with_amounts_data_3.olb_principal, instalments_with_amounts_data_3.paid_fee, instalments_with_amounts_data_3.paid_penalty, instalments_with_amounts_data_3.paid_interest, instalments_with_amounts_data_3.paid_principal, (ROW((t.overdue_fee)::numeric(22,2), (t.overdue_penalty)::numeric(22,2), (t.overdue_interest)::numeric(22,2), (t.overdue_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.olb_fee)::numeric(22,2), (t.olb_penalty)::numeric(22,2), (t.olb_interest)::numeric(22,2), ((t.olb_principal + COALESCE((sum(laa."capitalizedInterest")), '0'::numeric)))::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_fee)::numeric(22,2), (t.paid_penalty)::numeric(22,2), (t.paid_interest)::numeric(22,2), (t.paid_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_late_fee)::numeric(22,2), (t.paid_late_penalty)::numeric(22,2), (t.paid_late_interest)::numeric(22,2), (t.paid_late_principal)::numeric(22,2))::analytics."LoanAmounts"), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_principal ELSE NULL::numeric END)
  • Buffers: shared hit=17378371 read=427736 dirtied=28 written=39461, local hit=5396151 read=1454534, temp read=316347 written=316335
18. 605.499 533,756.065 ↓ 1.4 764,817 1

Nested Loop Left Join (cost=11,964,923.55..14,382,105.32 rows=532,273 width=818) (actual time=486,322.855..533,756.065 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (sum(laa."capitalizedInterest")), instalments_with_amounts_data.id, instalments_with_amounts_data.last_payment, instalments_with_amounts_data.olb_fee, instalments_with_amounts_data.olb_penalty, instalments_with_amounts_data.olb_interest, instalments_with_amounts_data.olb_principal, instalments_with_amounts_data.paid_fee, instalments_with_amounts_data.paid_penalty, instalments_with_amounts_data.paid_interest, instalments_with_amounts_data.paid_principal, instalments_with_amounts_data_1.id, instalments_with_amounts_data_1.last_payment, instalments_with_amounts_data_1.olb_fee, instalments_with_amounts_data_1.olb_penalty, instalments_with_amounts_data_1.olb_interest, instalments_with_amounts_data_1.olb_principal, instalments_with_amounts_data_1.paid_fee, instalments_with_amounts_data_1.paid_penalty, instalments_with_amounts_data_1.paid_interest, instalments_with_amounts_data_1.paid_principal, instalments_with_amounts_data_2.id, instalments_with_amounts_data_2.last_payment, instalments_with_amounts_data_2.olb_fee, instalments_with_amounts_data_2.olb_penalty, instalments_with_amounts_data_2.olb_interest, instalments_with_amounts_data_2.olb_principal, instalments_with_amounts_data_2.paid_fee, instalments_with_amounts_data_2.paid_penalty, instalments_with_amounts_data_2.paid_interest, instalments_with_amounts_data_2.paid_principal, (ROW((t.overdue_fee)::numeric(22,2), (t.overdue_penalty)::numeric(22,2), (t.overdue_interest)::numeric(22,2), (t.overdue_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.olb_fee)::numeric(22,2), (t.olb_penalty)::numeric(22,2), (t.olb_interest)::numeric(22,2), ((t.olb_principal + COALESCE((sum(laa."capitalizedInterest")), '0'::numeric)))::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_fee)::numeric(22,2), (t.paid_penalty)::numeric(22,2), (t.paid_interest)::numeric(22,2), (t.paid_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_late_fee)::numeric(22,2), (t.paid_late_penalty)::numeric(22,2), (t.paid_late_interest)::numeric(22,2), (t.paid_late_principal)::numeric(22,2))::analytics."LoanAmounts"), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_principal ELSE NULL::numeric END)
  • Buffers: shared hit=17378371 read=427736 dirtied=28 written=39461, local hit=2473474 read=1450415, temp read=316347 written=316335
19. 934.462 528,561.664 ↓ 1.4 764,817 1

Merge Left Join (cost=11,964,923.11..13,376,414.29 rows=532,273 width=655) (actual time=486,322.826..528,561.664 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (sum(laa."capitalizedInterest")), instalments_with_amounts_data.id, instalments_with_amounts_data.last_payment, instalments_with_amounts_data.olb_fee, instalments_with_amounts_data.olb_penalty, instalments_with_amounts_data.olb_interest, instalments_with_amounts_data.olb_principal, instalments_with_amounts_data.paid_fee, instalments_with_amounts_data.paid_penalty, instalments_with_amounts_data.paid_interest, instalments_with_amounts_data.paid_principal, instalments_with_amounts_data_1.id, instalments_with_amounts_data_1.last_payment, instalments_with_amounts_data_1.olb_fee, instalments_with_amounts_data_1.olb_penalty, instalments_with_amounts_data_1.olb_interest, instalments_with_amounts_data_1.olb_principal, instalments_with_amounts_data_1.paid_fee, instalments_with_amounts_data_1.paid_penalty, instalments_with_amounts_data_1.paid_interest, instalments_with_amounts_data_1.paid_principal, (ROW((t.overdue_fee)::numeric(22,2), (t.overdue_penalty)::numeric(22,2), (t.overdue_interest)::numeric(22,2), (t.overdue_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.olb_fee)::numeric(22,2), (t.olb_penalty)::numeric(22,2), (t.olb_interest)::numeric(22,2), ((t.olb_principal + COALESCE((sum(laa."capitalizedInterest")), '0'::numeric)))::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_fee)::numeric(22,2), (t.paid_penalty)::numeric(22,2), (t.paid_interest)::numeric(22,2), (t.paid_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_late_fee)::numeric(22,2), (t.paid_late_penalty)::numeric(22,2), (t.paid_late_interest)::numeric(22,2), (t.paid_late_principal)::numeric(22,2))::analytics."LoanAmounts"), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_principal ELSE NULL::numeric END)
  • Merge Cond: (ca.id = t.account_id)
  • Buffers: shared hit=17378371 read=427736 dirtied=28 written=39461, local hit=6244 read=990725, temp read=316347 written=316335
20. 1,368.082 475,939.094 ↓ 1.4 764,817 1

Sort (cost=8,913,582.28..8,914,912.96 rows=532,273 width=495) (actual time=475,610.794..475,939.094 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), instalments_with_amounts_data.id, instalments_with_amounts_data.last_payment, instalments_with_amounts_data.olb_fee, instalments_with_amounts_data.olb_penalty, instalments_with_amounts_data.olb_interest, instalments_with_amounts_data.olb_principal, instalments_with_amounts_data.paid_fee, instalments_with_amounts_data.paid_penalty, instalments_with_amounts_data.paid_interest, instalments_with_amounts_data.paid_principal, instalments_with_amounts_data_1.id, instalments_with_amounts_data_1.last_payment, instalments_with_amounts_data_1.olb_fee, instalments_with_amounts_data_1.olb_penalty, instalments_with_amounts_data_1.olb_interest, instalments_with_amounts_data_1.olb_principal, instalments_with_amounts_data_1.paid_fee, instalments_with_amounts_data_1.paid_penalty, instalments_with_amounts_data_1.paid_interest, instalments_with_amounts_data_1.paid_principal, (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_principal ELSE NULL::numeric END)
  • Sort Key: ca.id
  • Sort Method: external merge Disk: 180248kB
  • Buffers: shared hit=17378356 read=427720 dirtied=28 written=39461, local hit=6243 read=780730, temp read=194766 written=194754
21. 1,687.941 474,571.012 ↓ 1.4 764,817 1

Merge Right Join (cost=8,023,443.01..8,744,703.80 rows=532,273 width=495) (actual time=466,865.463..474,571.012 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), instalments_with_amounts_data.id, instalments_with_amounts_data.last_payment, instalments_with_amounts_data.olb_fee, instalments_with_amounts_data.olb_penalty, instalments_with_amounts_data.olb_interest, instalments_with_amounts_data.olb_principal, instalments_with_amounts_data.paid_fee, instalments_with_amounts_data.paid_penalty, instalments_with_amounts_data.paid_interest, instalments_with_amounts_data.paid_principal, instalments_with_amounts_data_1.id, instalments_with_amounts_data_1.last_payment, instalments_with_amounts_data_1.olb_fee, instalments_with_amounts_data_1.olb_penalty, instalments_with_amounts_data_1.olb_interest, instalments_with_amounts_data_1.olb_principal, instalments_with_amounts_data_1.paid_fee, instalments_with_amounts_data_1.paid_penalty, instalments_with_amounts_data_1.paid_interest, instalments_with_amounts_data_1.paid_principal, (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_principal ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_principal ELSE NULL::numeric END)
  • Merge Cond: (instalments_with_amounts_data_1.id = (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))))
  • Buffers: shared hit=17378356 read=427720 dirtied=28 written=39461, local hit=6243 read=780730, temp read=172234 written=172222
22. 5,706.468 5,706.468 ↓ 1.0 17,632,021 1

Index Scan using ix_instalments_with_amounts on pg_temp_21.instalments_with_amounts_data instalments_with_amounts_data_1 (cost=0.44..667,866.60 rows=17,631,944 width=171) (actual time=0.010..5,706.468 rows=17,632,021 loops=1)

  • Output: instalments_with_amounts_data_1.id, instalments_with_amounts_data_1.last_payment, instalments_with_amounts_data_1.olb_fee, instalments_with_amounts_data_1.olb_penalty, instalments_with_amounts_data_1.olb_interest, instalments_with_amounts_data_1.olb_principal, instalments_with_amounts_data_1.paid_fee, instalments_with_amounts_data_1.paid_penalty, instalments_with_amounts_data_1.paid_interest, instalments_with_amounts_data_1.paid_principal, CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_fee ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_penalty ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_interest ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_1.overdue THEN instalments_with_amounts_data_1.olb_principal ELSE NULL::numeric END
  • Buffers: local hit=6 read=258167
23. 73.383 467,176.603 ↓ 1.4 764,817 1

Materialize (cost=8,023,442.57..8,026,103.93 rows=532,273 width=332) (actual time=466,865.436..467,176.603 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), instalments_with_amounts_data.id, instalments_with_amounts_data.last_payment, instalments_with_amounts_data.olb_fee, instalments_with_amounts_data.olb_penalty, instalments_with_amounts_data.olb_interest, instalments_with_amounts_data.olb_principal, instalments_with_amounts_data.paid_fee, instalments_with_amounts_data.paid_penalty, instalments_with_amounts_data.paid_interest, instalments_with_amounts_data.paid_principal, (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_principal ELSE NULL::numeric END)
  • Buffers: shared hit=17378356 read=427720 dirtied=28 written=39461, local hit=6237 read=522563, temp read=172234 written=172222
24. 1,072.029 467,103.220 ↓ 1.4 764,817 1

Sort (cost=8,023,442.57..8,024,773.25 rows=532,273 width=332) (actual time=466,865.433..467,103.220 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), instalments_with_amounts_data.id, instalments_with_amounts_data.last_payment, instalments_with_amounts_data.olb_fee, instalments_with_amounts_data.olb_penalty, instalments_with_amounts_data.olb_interest, instalments_with_amounts_data.olb_principal, instalments_with_amounts_data.paid_fee, instalments_with_amounts_data.paid_penalty, instalments_with_amounts_data.paid_interest, instalments_with_amounts_data.paid_principal, (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_principal ELSE NULL::numeric END)
  • Sort Key: (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated))))
  • Sort Method: external sort Disk: 152016kB
  • Buffers: shared hit=17378356 read=427720 dirtied=28 written=39461, local hit=6237 read=522563, temp read=172234 written=172222
25. 1,312.873 466,031.191 ↓ 1.4 764,817 1

Merge Right Join (cost=7,169,689.30..7,890,950.10 rows=532,273 width=332) (actual time=458,806.221..466,031.191 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), instalments_with_amounts_data.id, instalments_with_amounts_data.last_payment, instalments_with_amounts_data.olb_fee, instalments_with_amounts_data.olb_penalty, instalments_with_amounts_data.olb_interest, instalments_with_amounts_data.olb_principal, instalments_with_amounts_data.paid_fee, instalments_with_amounts_data.paid_penalty, instalments_with_amounts_data.paid_interest, instalments_with_amounts_data.paid_principal, (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_fee ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END), (CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_principal ELSE NULL::numeric END)
  • Merge Cond: (instalments_with_amounts_data.id = (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))))
  • Buffers: shared hit=17378356 read=427720 dirtied=28 written=39461, local hit=6237 read=522563, temp read=153232 written=153220
26. 5,537.488 5,537.488 ↓ 1.0 17,632,000 1

Index Scan using ix_instalments_with_amounts on pg_temp_21.instalments_with_amounts_data (cost=0.44..667,866.60 rows=17,631,944 width=171) (actual time=0.006..5,537.488 rows=17,632,000 loops=1)

  • Output: instalments_with_amounts_data.id, instalments_with_amounts_data.last_payment, instalments_with_amounts_data.olb_fee, instalments_with_amounts_data.olb_penalty, instalments_with_amounts_data.olb_interest, instalments_with_amounts_data.olb_principal, instalments_with_amounts_data.paid_fee, instalments_with_amounts_data.paid_penalty, instalments_with_amounts_data.paid_interest, instalments_with_amounts_data.paid_principal, CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_fee ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_penalty ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_interest ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data.overdue THEN instalments_with_amounts_data.olb_principal ELSE NULL::numeric END
  • Buffers: local hit=4 read=258169
27. 71.161 459,180.830 ↓ 1.4 764,817 1

Materialize (cost=7,169,688.86..7,172,350.23 rows=532,273 width=169) (actual time=458,806.210..459,180.830 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date)))))
  • Buffers: shared hit=17378356 read=427720 dirtied=28 written=39461, local hit=6233 read=264394, temp read=153232 written=153220
28. 1,164.289 459,109.669 ↓ 1.4 764,817 1

Sort (cost=7,169,688.86..7,171,019.55 rows=532,273 width=169) (actual time=458,806.208..459,109.669 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date)))))
  • Sort Key: (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped))))
  • Sort Method: external merge Disk: 124384kB
  • Buffers: shared hit=17378356 read=427720 dirtied=28 written=39461, local hit=6233 read=264394, temp read=153232 written=153220
29. 153.195 457,945.380 ↓ 1.4 764,817 1

Hash Left Join (cost=4,840,787.49..7,073,582.39 rows=532,273 width=169) (actual time=397,570.208..457,945.380 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", la."disbursementOn", ra.rescheduled_after, ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date)))))
  • Hash Cond: (a.id = ra.account_id)
  • Buffers: shared hit=17378356 read=427720 dirtied=28 written=39461, local hit=6233 read=264394, temp read=137672 written=137660
30. 2,215.911 457,792.180 ↓ 1.4 764,817 1

Hash Join (cost=4,840,739.24..7,071,530.63 rows=532,273 width=173) (actual time=397,570.188..457,792.180 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", a.id, la."disbursementOn", ((max(m.occurred_on))::date), rescheduling.rescheduled_before, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date)))))
  • Hash Cond: (ca.id = a.id)
  • Buffers: shared hit=17378356 read=427720 dirtied=28 written=39461, local hit=6233 read=264394, temp read=137672 written=137660
31. 345.542 450,211.634 ↑ 1.1 764,817 1

Merge Left Join (cost=4,659,886.04..6,835,581.12 rows=833,622 width=141) (actual time=392,205.155..450,211.634 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", la."disbursementOn", la."accountId", ((max(m.occurred_on))::date), (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date)))))
  • Merge Cond: (ca.id = m.account_id)
  • Buffers: shared hit=17293640 read=417267 dirtied=28 written=39461, local hit=6233 read=264394, temp read=114011 written=114011
32. 416.990 438,765.623 ↑ 1.0 764,817 1

Merge Join (cost=4,659,883.39..6,566,930.85 rows=764,817 width=137) (actual time=392,190.441..438,765.623 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", la."disbursementOn", la."accountId", (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date)))))
  • Merge Cond: (ca.id = la."accountId")
  • Buffers: shared hit=14985762 read=375574 dirtied=3 written=34116, local hit=6231 read=258169, temp read=114011 written=114011
33. 442.709 437,221.698 ↑ 1.0 764,817 1

Merge Left Join (cost=4,659,811.12..6,430,239.24 rows=764,817 width=121) (actual time=392,190.394..437,221.698 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate", (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date)))))
  • Merge Cond: (ca.id = lai.account_id)
  • Buffers: shared hit=13993286 read=317822 written=14594, local hit=6231 read=258169, temp read=114011 written=114011
34. 129.072 129.072 ↑ 1.0 764,817 1

Index Scan using ix_prepare_changed_accounts on pg_temp_21.prepare_changed_accounts ca (cost=0.42..24,010.68 rows=764,817 width=9) (actual time=0.013..129.072 rows=764,817 loops=1)

  • Output: ca.id, ca."isUpdate
  • Buffers: local hit=6227
35. 197.152 436,649.917 ↓ 1.0 730,735 1

Materialize (cost=4,659,810.70..6,395,248.20 rows=725,465 width=120) (actual time=392,190.376..436,649.917 rows=730,735 loops=1)

  • Output: lai.account_id, (max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))), (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))), (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth)))), (count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date)))), (count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date))))
  • Buffers: shared hit=13993286 read=317822 written=14594, local hit=4 read=258169, temp read=114011 written=114011
36. 39,176.378 436,452.765 ↓ 1.0 730,735 1

GroupAggregate (cost=4,659,810.70..6,386,179.89 rows=725,465 width=120) (actual time=392,190.372..436,452.765 rows=730,735 loops=1)

  • Output: lai.account_id, max(('2019-04-13'::date - lai.obligatory_payment_date)) FILTER (WHERE ((lai.obligatory_payment_date < '2019-04-13'::date) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date)))), min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped))), min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.initial_principal_due > '0'::numeric) OR lai.is_skipped))), max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated))), max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated))), max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date))), max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date))), max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date))), max(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date))), min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date))), min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date))), min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date))), min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date))), min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date)))), min(lai.obligatory_payment_date) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date)))), count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth))), count(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_zeroth) AND (lai.repaid_date < '2019-04-14'::date))), count(lai.id) FILTER (WHERE (((lai.repaid_date)::date > lai.obligatory_payment_date) AND (lai.repaid_date < '2019-04-14'::date)))
  • Group Key: lai.account_id
  • Buffers: shared hit=13993286 read=317822 written=14594, local hit=4 read=258169, temp read=114011 written=114011
37. 17,376.764 397,276.387 ↓ 1.0 17,632,021 1

Sort (cost=4,659,810.70..4,703,890.56 rows=17,631,944 width=38) (actual time=392,190.201..397,276.387 rows=17,632,021 loops=1)

  • Output: lai.account_id, lai.obligatory_payment_date, lai.repaid_date, lai.id, lai.is_prepayment, lai.is_obliterated, lai.initial_principal_due, lai.is_skipped, lai.is_zeroth
  • Sort Key: lai.account_id
  • Sort Method: external merge Disk: 911120kB
  • Buffers: shared hit=13993286 read=317822 written=14594, local hit=4 read=258169, temp read=114011 written=114011
38. 8,733.127 379,899.623 ↓ 1.0 17,632,021 1

Merge Join (cost=35.14..2,055,532.41 rows=17,631,944 width=38) (actual time=0.035..379,899.623 rows=17,632,021 loops=1)

  • Output: lai.account_id, lai.obligatory_payment_date, lai.repaid_date, lai.id, lai.is_prepayment, lai.is_obliterated, lai.initial_principal_due, lai.is_skipped, lai.is_zeroth
  • Merge Cond: (instalments_with_amounts_data_7.id = lai.id)
  • Buffers: shared hit=13993286 read=317822 written=14594, local hit=4 read=258169
39. 3,856.543 3,856.543 ↓ 1.0 17,632,021 1

Index Only Scan using ix_instalments_with_amounts on pg_temp_21.instalments_with_amounts_data instalments_with_amounts_data_7 (cost=0.44..667,866.60 rows=17,631,944 width=8) (actual time=0.022..3,856.543 rows=17,632,021 loops=1)

  • Output: instalments_with_amounts_data_7.id
  • Heap Fetches: 17632021
  • Buffers: local hit=4 read=258169
40. 367,309.953 367,309.953 ↑ 1.0 17,954,404 1

Index Scan using loan_account_installments_pkey on instafin.loan_account_installments lai (cost=0.44..1,122,320.93 rows=17,987,991 width=38) (actual time=0.010..367,309.953 rows=17,954,404 loops=1)

  • Output: lai.id, lai.account_id, lai.obligatory_payment_date, lai.last_penalty_calculation_date, lai.initial_principal_due, lai.paid_interest, lai.initial_loan_account_balance, lai.current_penalty_amount, lai.current_fee_amount, lai.current_principal_due, lai.current_interest_due, lai.status_id, lai.repaid_date, lai.is_prepayment, lai.is_obliterated, lai.is_skipped, lai.penalty_rounding_error, lai.interest_rounding_error, lai."paidFee", lai."paidPenalty", lai.is_zeroth, lai."instalmentGroupIndex", lai."gracePeriodType", lai.capitalized_interest_rounding_error, lai."isNonPay
  • Buffers: shared hit=13993286 read=317822 written=14594
41. 1,126.935 1,126.935 ↑ 1.0 764,817 1

Index Scan using loan_accounts_pkey on instafin."LoanAccount" la (cost=0.42..125,264.86 rows=771,644 width=16) (actual time=0.041..1,126.935 rows=764,817 loops=1)

  • Output: la."accountId", la."loanAmount", la."interestRate", la."numberOfInstallments", la."gracePeriodNumberOfInstallmentIntervals", la."olbP", la."olbI", la."overduePrincipal", la."overdueInterest", la."penaltyAmount", la."feeAmount", la."feeOverdue", la."daysInArrears", la."disbursementOn", la."expectedRepayment", la."expectedFee", la."scheduleStartDate", la."expectedRepaymentInterest", la."expectedRepaymentPrincipal", la."typeOfBusiness", la."loanPurpose", la."economicActivity", la."userDefinedInterestRate", la."requiredDepositBalance", la."secondInstalmentDate", la."penaltyOverdue", la."tentativeDisbursementDate", la."firstInstallmentDate", la."maturityDate", la."lastPaidInstallmentOn", la."nextUnpaidInstallmentOn", la."numberOfPaidInstallments", la."paidFee", la."paidPenalty", la."paidPrincipal", la."paidInterest", la."effectiveInterestRate", la."debtCapacity", la."workingCapital", la."automaticRepayment", la."automaticRepaymentEndDate", la."isRestructured", la."accountClassification", la."otherFinancingLendingName", la."otherLoanMonthlyInstalment", la."otherLoanTermMonths", la."otherLoanRemainingTermMonths", la."interestAccrualStrategy", la."payInstalments", la."applicationType", la.deviation, la."upToDateInstalments
  • Buffers: shared hit=992476 read=57752 dirtied=3 written=19522
42. 112.252 11,100.469 ↑ 1.1 757,366 1

Materialize (cost=2.65..256,317.95 rows=833,622 width=12) (actual time=14.711..11,100.469 rows=757,366 loops=1)

  • Output: m.account_id, ((max(m.occurred_on))::date)
  • Buffers: shared hit=2307878 read=41693 dirtied=25 written=5345, local hit=2 read=6225
43. 512.355 10,988.217 ↑ 1.1 757,366 1

GroupAggregate (cost=2.65..245,897.68 rows=833,622 width=12) (actual time=14.707..10,988.217 rows=757,366 loops=1)

  • Output: m.account_id, (max(m.occurred_on))::date
  • Group Key: m.account_id
  • Buffers: shared hit=2307878 read=41693 dirtied=25 written=5345, local hit=2 read=6225
44. 474.958 10,475.862 ↑ 1.2 2,318,780 1

Merge Join (cost=2.65..221,904.48 rows=2,714,584 width=16) (actual time=0.072..10,475.862 rows=2,318,780 loops=1)

  • Output: m.account_id, m.occurred_on
  • Merge Cond: (ca_2.id = m.account_id)
  • Buffers: shared hit=2307878 read=41693 dirtied=25 written=5345, local hit=2 read=6225
45. 220.279 220.279 ↑ 1.0 764,817 1

Index Only Scan using ix_prepare_changed_accounts on pg_temp_21.prepare_changed_accounts ca_2 (cost=0.42..24,010.68 rows=764,817 width=8) (actual time=0.026..220.279 rows=764,817 loops=1)

  • Output: ca_2.id
  • Heap Fetches: 764817
  • Buffers: local hit=2 read=6225
46. 9,780.625 9,780.625 ↓ 1.0 2,960,739 1

Index Scan using account_status_modification_information_account_id_idx on instafin.account_status_modification_information m (cost=0.43..161,440.12 rows=2,958,795 width=16) (actual time=0.043..9,780.625 rows=2,960,739 loops=1)

  • Output: m.id, m.account_id, m.old_status_id, m.new_status_id, m.occurred_on, m.created_on, m.notes
  • Filter: (m.occurred_on < '2019-04-14'::date)
  • Buffers: shared hit=2307878 read=41693 dirtied=25 written=5345
47. 581.762 5,364.635 ↑ 1.0 1,194,389 1

Hash (cost=156,304.80..156,304.80 rows=1,208,512 width=40) (actual time=5,364.635..5,364.635 rows=1,194,389 loops=1)

  • Output: a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", a.id, rescheduling.rescheduled_before
  • Buckets: 524288 Batches: 4 Memory Usage: 25092kB
  • Buffers: shared hit=84716 read=10453, temp read=6619 written=13173
48. 889.773 4,782.873 ↑ 1.0 1,194,389 1

Hash Right Join (cost=146,141.13..156,304.80 rows=1,208,512 width=40) (actual time=4,221.874..4,782.873 rows=1,194,389 loops=1)

  • Output: a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", a.id, rescheduling.rescheduled_before
  • Hash Cond: (rescheduling.account_id = a.id)
  • Buffers: shared hit=84716 read=10453, temp read=6619 written=6613
49. 1.864 2,935.100 ↓ 1.0 16,272 1

Subquery Scan on rescheduling (cost=22,249.61..22,601.96 rows=15,660 width=12) (actual time=2,926.233..2,935.100 rows=16,272 loops=1)

  • Output: rescheduling.rescheduled_before, rescheduling.account_id
  • Buffers: shared hit=4 read=7907
50. 83.231 2,933.236 ↓ 1.0 16,272 1

HashAggregate (cost=22,249.61..22,445.36 rows=15,660 width=12) (actual time=2,926.231..2,933.236 rows=16,272 loops=1)

  • Output: lair.account_id, (max(ar.occurred_on))::date
  • Group Key: lair.account_id
  • Buffers: shared hit=4 read=7907
51. 98.440 2,850.005 ↓ 1.0 481,268 1

Hash Join (cost=650.88..19,843.51 rows=481,220 width=16) (actual time=531.491..2,850.005 rows=481,268 loops=1)

  • Output: lair.account_id, ar.occurred_on
  • Hash Cond: (lair.revision_id = ar.id)
  • Buffers: shared hit=4 read=7907
52. 2,220.176 2,220.176 ↑ 1.0 481,268 1

Seq Scan on instafin.loan_account_installment_revisions lair (cost=0.00..12,575.68 rows=481,268 width=16) (actual time=0.006..2,220.176 rows=481,268 loops=1)

  • Output: lair.id, lair.installment_id, lair.revision_id, lair.account_id, lair.obligatory_payment_date, lair.last_penalty_calculation_date, lair.initial_principal_due, lair.paid_interest, lair.initial_loan_account_balance, lair.current_penalty_amount, lair.current_fee_amount, lair.current_principal_due, lair.current_interest_due, lair.status_id, lair.repaid_date, lair.is_prepayment, lair.is_obliterated, lair.is_zeroth, lair."gracePeriodType
  • Buffers: shared hit=2 read=7761
53. 3.129 531.389 ↓ 1.0 20,116 1

Hash (cost=399.45..399.45 rows=20,114 width=16) (actual time=531.389..531.389 rows=20,116 loops=1)

  • Output: ar.occurred_on, ar.id
  • Buckets: 32768 Batches: 1 Memory Usage: 1199kB
  • Buffers: shared hit=2 read=146
54. 528.260 528.260 ↓ 1.0 20,116 1

Seq Scan on instafin.account_revisions ar (cost=0.00..399.45 rows=20,114 width=16) (actual time=107.260..528.260 rows=20,116 loops=1)

  • Output: ar.occurred_on, ar.id
  • Filter: (ar.occurred_on < '2019-04-14'::date)
  • Buffers: shared hit=2 read=146
55. 559.419 958.000 ↑ 1.0 1,194,389 1

Hash (cost=99,343.12..99,343.12 rows=1,208,512 width=36) (actual time=958.000..958.000 rows=1,194,389 loops=1)

  • Output: a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", a.id
  • Buckets: 524288 Batches: 4 Memory Usage: 25076kB
  • Buffers: shared hit=84712 read=2546, temp written=6555
56. 398.581 398.581 ↑ 1.0 1,194,389 1

Seq Scan on instafin.accounts a (cost=0.00..99,343.12 rows=1,208,512 width=36) (actual time=0.014..398.581 rows=1,194,389 loops=1)

  • Output: a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", a.id
  • Buffers: shared hit=84712 read=2546
57. 0.001 0.005 ↓ 0.0 0 1

Hash (cost=27.00..27.00 rows=1,700 width=12) (actual time=0.005..0.005 rows=0 loops=1)

  • Output: ra.rescheduled_after, ra.account_id
  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
58. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on pg_temp_21.revised_accounts ra (cost=0.00..27.00 rows=1,700 width=12) (actual time=0.004..0.004 rows=0 loops=1)

  • Output: ra.rescheduled_after, ra.account_id
59. 282.165 51,688.108 ↓ 6.0 730,735 1

Materialize (cost=3,051,340.83..4,459,020.41 rows=121,596 width=168) (actual time=10,712.026..51,688.108 rows=730,735 loops=1)

  • Output: t.account_id, (sum(laa."capitalizedInterest")), (ROW((t.overdue_fee)::numeric(22,2), (t.overdue_penalty)::numeric(22,2), (t.overdue_interest)::numeric(22,2), (t.overdue_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.olb_fee)::numeric(22,2), (t.olb_penalty)::numeric(22,2), (t.olb_interest)::numeric(22,2), ((t.olb_principal + COALESCE((sum(laa."capitalizedInterest")), '0'::numeric)))::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_fee)::numeric(22,2), (t.paid_penalty)::numeric(22,2), (t.paid_interest)::numeric(22,2), (t.paid_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_late_fee)::numeric(22,2), (t.paid_late_penalty)::numeric(22,2), (t.paid_late_interest)::numeric(22,2), (t.paid_late_principal)::numeric(22,2))::analytics."LoanAmounts")
  • Buffers: shared hit=15 read=16, local hit=1 read=209995, temp read=121581 written=121581
60. 782.468 51,405.943 ↓ 6.0 730,735 1

Merge Left Join (cost=3,051,340.83..4,458,716.42 rows=121,596 width=168) (actual time=10,712.024..51,405.943 rows=730,735 loops=1)

  • Output: t.account_id, (sum(laa."capitalizedInterest")), (ROW((t.overdue_fee)::numeric(22,2), (t.overdue_penalty)::numeric(22,2), (t.overdue_interest)::numeric(22,2), (t.overdue_principal)::numeric(22,2))::analytics."LoanAmounts"), ROW((t.olb_fee)::numeric(22,2), (t.olb_penalty)::numeric(22,2), (t.olb_interest)::numeric(22,2), ((t.olb_principal + COALESCE((sum(laa."capitalizedInterest")), '0'::numeric)))::numeric(22,2))::analytics."LoanAmounts", (ROW((t.paid_fee)::numeric(22,2), (t.paid_penalty)::numeric(22,2), (t.paid_interest)::numeric(22,2), (t.paid_principal)::numeric(22,2))::analytics."LoanAmounts"), (ROW((t.paid_late_fee)::numeric(22,2), (t.paid_late_penalty)::numeric(22,2), (t.paid_late_interest)::numeric(22,2), (t.paid_late_principal)::numeric(22,2))::analytics."LoanAmounts")
  • Merge Cond: (t.account_id = laa.account_id)
  • Buffers: shared hit=15 read=16, local hit=1 read=209995, temp read=121581 written=121581
61. 1,296.266 50,623.463 ↓ 6.0 730,735 1

Subquery Scan on t (cost=3,050,860.23..4,455,936.07 rows=121,596 width=232) (actual time=10,712.006..50,623.463 rows=730,735 loops=1)

  • Output: t.account_id, t.olb_fee, t.olb_penalty, t.olb_interest, t.olb_principal, ROW((t.overdue_fee)::numeric(22,2), (t.overdue_penalty)::numeric(22,2), (t.overdue_interest)::numeric(22,2), (t.overdue_principal)::numeric(22,2))::analytics."LoanAmounts", ROW((t.paid_fee)::numeric(22,2), (t.paid_penalty)::numeric(22,2), (t.paid_interest)::numeric(22,2), (t.paid_principal)::numeric(22,2))::analytics."LoanAmounts", ROW((t.paid_late_fee)::numeric(22,2), (t.paid_late_penalty)::numeric(22,2), (t.paid_late_interest)::numeric(22,2), (t.paid_late_principal)::numeric(22,2))::analytics."LoanAmounts
  • Buffers: shared hit=15 read=16, local hit=1 read=209995, temp read=121581 written=121581
62. 33,605.179 49,327.197 ↓ 6.0 730,735 1

GroupAggregate (cost=3,050,860.23..4,451,072.23 rows=121,596 width=520) (actual time=10,711.996..49,327.197 rows=730,735 loops=1)

  • Output: instalments_with_amounts_data_8.account_id, sum(CASE WHEN instalments_with_amounts_data_8.overdue THEN instalments_with_amounts_data_8.olb_fee ELSE NULL::numeric END), CASE WHEN (sum(CASE WHEN instalments_with_amounts_data_8.overdue THEN instalments_with_amounts_data_8.olb_penalty ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(sum(CASE WHEN instalments_with_amounts_data_8.overdue THEN instalments_with_amounts_data_8.olb_penalty ELSE NULL::numeric END)) END, CASE WHEN (sum(CASE WHEN instalments_with_amounts_data_8.overdue THEN instalments_with_amounts_data_8.olb_interest ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(sum(CASE WHEN instalments_with_amounts_data_8.overdue THEN instalments_with_amounts_data_8.olb_interest ELSE NULL::numeric END)) END, sum(CASE WHEN instalments_with_amounts_data_8.overdue THEN instalments_with_amounts_data_8.olb_principal ELSE NULL::numeric END), sum(instalments_with_amounts_data_8.olb_fee), CASE WHEN (sum(instalments_with_amounts_data_8.olb_penalty) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(sum(instalments_with_amounts_data_8.olb_penalty)) END, CASE WHEN (sum(instalments_with_amounts_data_8.olb_interest) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(sum(instalments_with_amounts_data_8.olb_interest)) END, sum(instalments_with_amounts_data_8.olb_principal), sum(instalments_with_amounts_data_8.paid_fee), CASE WHEN (sum(instalments_with_amounts_data_8.paid_penalty) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(sum(instalments_with_amounts_data_8.paid_penalty)) END, CASE WHEN (sum(instalments_with_amounts_data_8.paid_interest) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(sum(instalments_with_amounts_data_8.paid_interest)) END, sum(instalments_with_amounts_data_8.paid_principal), sum(CASE WHEN instalments_with_amounts_data_8.paidlate THEN instalments_with_amounts_data_8.paid_fee ELSE NULL::numeric END), CASE WHEN (sum(CASE WHEN instalments_with_amounts_data_8.paidlate THEN instalments_with_amounts_data_8.paid_penalty ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(sum(CASE WHEN instalments_with_amounts_data_8.paidlate THEN instalments_with_amounts_data_8.paid_penalty ELSE NULL::numeric END)) END, CASE WHEN (sum(CASE WHEN instalments_with_amounts_data_8.paidlate THEN instalments_with_amounts_data_8.paid_interest ELSE NULL::numeric END) < '0'::numeric) THEN '0'::numeric ELSE round_with_sd(sum(CASE WHEN instalments_with_amounts_data_8.paidlate THEN instalments_with_amounts_data_8.paid_interest ELSE NULL::numeric END)) END, sum(CASE WHEN instalments_with_amounts_data_8.paidlate THEN instalments_with_amounts_data_8.paid_principal ELSE NULL::numeric END)
  • Group Key: instalments_with_amounts_data_8.account_id
  • Buffers: shared hit=15 read=16, local hit=1 read=209995, temp read=121581 written=121581
63. 12,243.775 15,722.018 ↓ 1.0 17,632,021 1

Sort (cost=3,050,860.23..3,094,940.09 rows=17,631,944 width=41) (actual time=10,233.422..15,722.018 rows=17,632,021 loops=1)

  • Output: instalments_with_amounts_data_8.account_id, instalments_with_amounts_data_8.overdue, instalments_with_amounts_data_8.olb_fee, instalments_with_amounts_data_8.olb_penalty, instalments_with_amounts_data_8.olb_interest, instalments_with_amounts_data_8.olb_principal, instalments_with_amounts_data_8.paid_fee, instalments_with_amounts_data_8.paid_penalty, instalments_with_amounts_data_8.paid_interest, instalments_with_amounts_data_8.paid_principal, instalments_with_amounts_data_8.paidlate
  • Sort Key: instalments_with_amounts_data_8.account_id
  • Sort Method: external merge Disk: 971496kB
  • Buffers: local hit=1 read=209995, temp read=121581 written=121581
64. 3,478.243 3,478.243 ↓ 1.0 17,632,021 1

Seq Scan on pg_temp_21.instalments_with_amounts_data instalments_with_amounts_data_8 (cost=0.00..386,315.44 rows=17,631,944 width=41) (actual time=0.016..3,478.243 rows=17,632,021 loops=1)

  • Output: instalments_with_amounts_data_8.account_id, instalments_with_amounts_data_8.overdue, instalments_with_amounts_data_8.olb_fee, instalments_with_amounts_data_8.olb_penalty, instalments_with_amounts_data_8.olb_interest, instalments_with_amounts_data_8.olb_principal, instalments_with_amounts_data_8.paid_fee, instalments_with_amounts_data_8.paid_penalty, instalments_with_amounts_data_8.paid_interest, instalments_with_amounts_data_8.paid_principal, instalments_with_amounts_data_8.paidlate
  • Buffers: local hit=1 read=209995
65. 0.001 0.012 ↓ 0.0 0 1

Materialize (cost=480.60..482.31 rows=52 width=36) (actual time=0.012..0.012 rows=0 loops=1)

  • Output: laa.account_id, (sum(laa."capitalizedInterest")), laa.account_id
66. 0.001 0.011 ↓ 0.0 0 1

GroupAggregate (cost=480.60..481.66 rows=52 width=40) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: laa.account_id, sum(laa."capitalizedInterest"), laa.account_id
  • Group Key: laa.account_id
67. 0.005 0.010 ↓ 0.0 0 1

Sort (cost=480.60..480.74 rows=55 width=36) (actual time=0.010..0.010 rows=0 loops=1)

  • Output: laa.account_id, laa."capitalizedInterest
  • Sort Key: laa.account_id
  • Sort Method: quicksort Memory: 25kB
68. 0.000 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.42..479.01 rows=55 width=36) (actual time=0.005..0.005 rows=0 loops=1)

  • Output: laa.account_id, laa."capitalizedInterest
69. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on instafin.loan_account_accruals laa (cost=0.00..14.12 rows=55 width=36) (actual time=0.005..0.005 rows=0 loops=1)

  • Output: laa.account_id, laa.general_ledger_transaction_id, laa.interest_rounding_error, laa."interestOverduePrincipalRE", laa.is_reverted, laa.interest_amount, laa."interestOverduePrincipal", laa.occurred_on, laa."capitalizedInterest", laa."capitalizedInterestRE
  • Filter: ((NOT laa.is_reverted) AND (laa.occurred_on < '2019-04-14'::date))
70. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ix_prepare_changed_accounts on pg_temp_21.prepare_changed_accounts pca (cost=0.42..8.44 rows=1 width=8) (never executed)

  • Output: pca.id
  • Index Cond: (pca.id = laa.account_id)
  • Heap Fetches: 0
71. 4,588.902 4,588.902 ↑ 1.0 1 764,817

Index Scan using ix_instalments_with_amounts on pg_temp_21.instalments_with_amounts_data instalments_with_amounts_data_2 (cost=0.44..1.88 rows=1 width=171) (actual time=0.006..0.006 rows=1 loops=764,817)

  • Output: instalments_with_amounts_data_2.id, instalments_with_amounts_data_2.last_payment, instalments_with_amounts_data_2.olb_fee, instalments_with_amounts_data_2.olb_penalty, instalments_with_amounts_data_2.olb_interest, instalments_with_amounts_data_2.olb_principal, instalments_with_amounts_data_2.paid_fee, instalments_with_amounts_data_2.paid_penalty, instalments_with_amounts_data_2.paid_interest, instalments_with_amounts_data_2.paid_principal, CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_fee ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_penalty ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_interest ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_2.overdue THEN instalments_with_amounts_data_2.olb_principal ELSE NULL::numeric END
  • Index Cond: (instalments_with_amounts_data_2.id = (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date < '2019-04-13'::date)))))
  • Buffers: local hit=2467230 read=459690
72. 1,529.634 1,529.634 ↑ 1.0 1 764,817

Index Scan using ix_instalments_with_amounts on pg_temp_21.instalments_with_amounts_data instalments_with_amounts_data_3 (cost=0.44..1.88 rows=1 width=171) (actual time=0.001..0.002 rows=1 loops=764,817)

  • Output: instalments_with_amounts_data_3.id, instalments_with_amounts_data_3.last_payment, instalments_with_amounts_data_3.olb_fee, instalments_with_amounts_data_3.olb_penalty, instalments_with_amounts_data_3.olb_interest, instalments_with_amounts_data_3.olb_principal, instalments_with_amounts_data_3.paid_fee, instalments_with_amounts_data_3.paid_penalty, instalments_with_amounts_data_3.paid_interest, instalments_with_amounts_data_3.paid_principal, CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_fee ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_penalty ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_interest ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_3.overdue THEN instalments_with_amounts_data_3.olb_principal ELSE NULL::numeric END
  • Index Cond: (instalments_with_amounts_data_3.id = (max(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.repaid_date < '2019-04-14'::date)))))
  • Buffers: local hit=2922677 read=4119
73. 764.817 764.817 ↓ 0.0 0 764,817

Index Scan using ix_instalments_with_amounts on pg_temp_21.instalments_with_amounts_data instalments_with_amounts_data_4 (cost=0.44..1.88 rows=1 width=171) (actual time=0.001..0.001 rows=0 loops=764,817)

  • Output: instalments_with_amounts_data_4.id, instalments_with_amounts_data_4.last_payment, instalments_with_amounts_data_4.olb_fee, instalments_with_amounts_data_4.olb_penalty, instalments_with_amounts_data_4.olb_interest, instalments_with_amounts_data_4.olb_principal, instalments_with_amounts_data_4.paid_fee, instalments_with_amounts_data_4.paid_penalty, instalments_with_amounts_data_4.paid_interest, instalments_with_amounts_data_4.paid_principal, CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_fee ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_penalty ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_interest ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_4.overdue THEN instalments_with_amounts_data_4.olb_principal ELSE NULL::numeric END
  • Index Cond: (instalments_with_amounts_data_4.id = (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date >= '2019-04-13'::date)))))
  • Buffers: local hit=1217058 read=3751
74. 764.817 764.817 ↓ 0.0 0 764,817

Index Scan using ix_instalments_with_amounts on pg_temp_21.instalments_with_amounts_data instalments_with_amounts_data_5 (cost=0.44..1.88 rows=1 width=171) (actual time=0.001..0.001 rows=0 loops=764,817)

  • Output: instalments_with_amounts_data_5.id, instalments_with_amounts_data_5.last_payment, instalments_with_amounts_data_5.olb_fee, instalments_with_amounts_data_5.olb_penalty, instalments_with_amounts_data_5.olb_interest, instalments_with_amounts_data_5.olb_principal, instalments_with_amounts_data_5.paid_fee, instalments_with_amounts_data_5.paid_penalty, instalments_with_amounts_data_5.paid_interest, instalments_with_amounts_data_5.paid_principal, CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_fee ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_penalty ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_interest ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_5.overdue THEN instalments_with_amounts_data_5.olb_principal ELSE NULL::numeric END
  • Index Cond: (instalments_with_amounts_data_5.id = (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND (lai.obligatory_payment_date > '2019-04-13'::date)))))
  • Buffers: local hit=1220312 read=497
75. 764.817 764.817 ↓ 0.0 0 764,817

Index Scan using ix_instalments_with_amounts on pg_temp_21.instalments_with_amounts_data instalments_with_amounts_data_6 (cost=0.44..1.88 rows=1 width=171) (actual time=0.001..0.001 rows=0 loops=764,817)

  • Output: instalments_with_amounts_data_6.id, instalments_with_amounts_data_6.last_payment, instalments_with_amounts_data_6.olb_fee, instalments_with_amounts_data_6.olb_penalty, instalments_with_amounts_data_6.olb_interest, instalments_with_amounts_data_6.olb_principal, instalments_with_amounts_data_6.paid_fee, instalments_with_amounts_data_6.paid_penalty, instalments_with_amounts_data_6.paid_interest, instalments_with_amounts_data_6.paid_principal, CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_fee ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_penalty ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_interest ELSE NULL::numeric END, CASE WHEN instalments_with_amounts_data_6.overdue THEN instalments_with_amounts_data_6.olb_principal ELSE NULL::numeric END
  • Index Cond: (instalments_with_amounts_data_6.id = (min(lai.id) FILTER (WHERE ((NOT lai.is_prepayment) AND (NOT lai.is_obliterated) AND ((lai.repaid_date IS NULL) OR (lai.repaid_date >= '2019-04-14'::date))))))
  • Buffers: local hit=1310688 read=16
76. 0.001 0.012 ↓ 0.0 0 1

Materialize (cost=11.25..11.32 rows=1 width=40) (actual time=0.012..0.012 rows=0 loops=1)

  • Output: lars."accountId", (ROW((COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 1)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 2)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 3)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 4)), '0'::numeric))::numeric(22,2))::analytics."LoanAmounts")
  • Buffers: shared read=1
77. 0.000 0.011 ↓ 0.0 0 1

GroupAggregate (cost=11.25..11.31 rows=1 width=40) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: lars."accountId", ROW((COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 1)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 2)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 3)), '0'::numeric))::numeric(22,2), (COALESCE(sum(lars.amount) FILTER (WHERE (lars."sectionTypeId" = 4)), '0'::numeric))::numeric(22,2))::analytics."LoanAmounts
  • Group Key: lars."accountId
  • Buffers: shared read=1
78. 0.002 0.011 ↓ 0.0 0 1

Sort (cost=11.25..11.25 rows=1 width=48) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: lars."accountId", lars.amount, lars."sectionTypeId
  • Sort Key: lars."accountId
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared read=1
79. 0.001 0.009 ↓ 0.0 0 1

Bitmap Heap Scan on instafin."LoanAccountRecoverySection" lars (cost=4.13..11.24 rows=1 width=48) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: lars."accountId", lars.amount, lars."sectionTypeId
  • Recheck Cond: (lars."revertedByTransactionId" IS NULL)
  • Filter: (lars."occurredOn" < '2019-04-14'::date)
  • Buffers: shared read=1
80. 0.008 0.008 ↓ 0.0 0 1

Bitmap Index Scan on "ix_LoanAccountRecoverySection_accountId_1540087396" (cost=0.00..4.13 rows=3 width=0) (actual time=0.008..0.008 rows=0 loops=1)

  • Buffers: shared read=1
81. 0.000 6,883.353 ↑ 1.0 1 764,817

Limit (cost=8.45..8.46 rows=1 width=25) (actual time=0.009..0.009 rows=1 loops=764,817)

  • Output: NULL::bigint, lcc."accountClassification", lcc."isRestructured", lcc."occurredOn", lcc."ID
  • Buffers: shared hit=2709401 read=5950 dirtied=15
82. 1,529.634 6,883.353 ↑ 1.0 1 764,817

Sort (cost=8.45..8.46 rows=1 width=25) (actual time=0.009..0.009 rows=1 loops=764,817)

  • Output: NULL::bigint, lcc."accountClassification", lcc."isRestructured", lcc."occurredOn", lcc."ID
  • Sort Key: lcc."occurredOn" DESC, lcc."ID" DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2709401 read=5950 dirtied=15
83. 5,353.719 5,353.719 ↑ 1.0 1 764,817

Index Scan using "ix_LoanClassificationChange_accountId_occurredOn" on instafin."LoanClassificationChange" lcc (cost=0.42..8.44 rows=1 width=25) (actual time=0.006..0.007 rows=1 loops=764,817)

  • Output: NULL::bigint, lcc."accountClassification", lcc."isRestructured", lcc."occurredOn", lcc."ID
  • Index Cond: ((lcc."accountId" = ca.id) AND (lcc."occurredOn" < '2019-04-14'::date))
  • Buffers: shared hit=2709401 read=5950 dirtied=15
84.          

SubPlan (forNested Loop Left Join)

85. 764.817 5,353.719 ↑ 1.0 1 764,817

Limit (cost=13.48..13.49 rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=764,817)

  • Output: account_status_modification_information.new_status_id, account_status_modification_information.id
  • Buffers: shared hit=4286365 read=8169
86. 764.817 4,588.902 ↑ 4.0 1 764,817

Sort (cost=13.48..13.49 rows=4 width=16) (actual time=0.006..0.006 rows=1 loops=764,817)

  • Output: account_status_modification_information.new_status_id, account_status_modification_information.id
  • Sort Key: account_status_modification_information.id DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4286365 read=8169
87. 3,824.085 3,824.085 ↑ 1.3 3 764,817

Index Scan using account_status_modification_information_account_id_idx on instafin.account_status_modification_information (cost=0.43..13.46 rows=4 width=16) (actual time=0.002..0.005 rows=3 loops=764,817)

  • Output: account_status_modification_information.new_status_id, account_status_modification_information.id
  • Index Cond: (account_status_modification_information.account_id = ca.id)
  • Filter: ((account_status_modification_information.old_status_id <> account_status_modification_information.new_status_id) AND (account_status_modification_information.occurred_on < '2019-04-14'::date))
  • Buffers: shared hit=4286365 read=8169
88. 0.000 3,059.268 ↓ 0.0 0 764,817

Limit (cost=13.47..13.48 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=764,817)

  • Output: account_status_modification_information_1.old_status_id, account_status_modification_information_1.occurred_on, account_status_modification_information_1.created_on, account_status_modification_information_1.id
  • Buffers: shared hit=4294534
89. 764.817 3,059.268 ↓ 0.0 0 764,817

Sort (cost=13.47..13.48 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=764,817)

  • Output: account_status_modification_information_1.old_status_id, account_status_modification_information_1.occurred_on, account_status_modification_information_1.created_on, account_status_modification_information_1.id
  • Sort Key: account_status_modification_information_1.occurred_on, account_status_modification_information_1.created_on, account_status_modification_information_1.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4294534
90. 2,294.451 2,294.451 ↓ 0.0 0 764,817

Index Scan using account_status_modification_information_account_id_idx on instafin.account_status_modification_information account_status_modification_information_1 (cost=0.43..13.46 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=764,817)

  • Output: account_status_modification_information_1.old_status_id, account_status_modification_information_1.occurred_on, account_status_modification_information_1.created_on, account_status_modification_information_1.id
  • Index Cond: (account_status_modification_information_1.account_id = ca.id)
  • Filter: ((account_status_modification_information_1.old_status_id <> account_status_modification_information_1.new_status_id) AND (account_status_modification_information_1.occurred_on >= '2019-04-14'::date))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=4294534