explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ap2

Settings
# exclusive inclusive rows x rows loops node
1. 7,597.816 479,290.488 ↓ 1.6 596,444 1

Nested Loop Left Join (cost=26,568,179.75..48,686,833.70 rows=370,194 width=625) (actual time=245,462.217..479,290.488 rows=596,444 loops=1)

  • Output: ca.id, (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((sum(t.amount)), '0'::numeric), CASE WHEN ((sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date))) < (sum(sd.paid))) THEN '0'::numeric ELSE ((sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date))) - (sum(sd.paid))) END, CASE WHEN (sd.account_id IS NOT NULL) THEN COALESCE((sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date = '2019-04-13'::date))), '0'::numeric) ELSE NULL::numeric END, CASE WHEN ((('2019-04-13'::date - min(sd.scheduled_deposit_date) FILTER (WHERE (sd.deposit_amount > sd.paid)))) < 0) THEN 0 ELSE (('2019-04-13'::date - min(sd.scheduled_deposit_date) FILTER (WHERE (sd.deposit_amount > sd.paid)))) END, (count(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (count(sd.id) FILTER (WHERE sd.paid_late)), CASE WHEN (('now'::cstring)::date = '2019-04-13'::date) THEN (sum(daa.amount)) ELSE (sum((sum(daa_1.amount)))) END, CASE WHEN ((max(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric))))) IS NULL) THEN NULL::analytics."TransactionInfo" ELSE ROW(((min(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric))))))::date, ((max(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric))))))::date, ((sum(t.amount) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_REVERT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_REVERT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT,DEPOSIT_REVERT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric))))))::numeric(22,2), (((count(*) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_DEPOSIT,DEPOSIT_REVERT_CHEQUE_DEPOSIT,DEPOSIT_REVERT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))))))::integer, (((array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))))[1]))::numeric(22,2), (((array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))))[1]))::numeric(22,2))::analytics."TransactionInfo" END, CASE WHEN ((max(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric))))) IS NULL) THEN NULL::analytics."TransactionInfo" ELSE ROW(((min(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric))))))::date, ((max(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric))))))::date, (((- sum(t.amount) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_REVERT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_REVERT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL,DEPOSIT_REVERT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))))))::numeric(22,2), (((count(*) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_WITHDRAWAL,DEPOSIT_REVERT_CHEQUE_WITHDRAWAL,DEPOSIT_REVERT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))))))::integer, (((array_agg((- t.amount) ORDER BY t."occurredOn", t."ID") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))))[1]))::numeric(22,2), (((array_agg((- t.amount) ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))))[1]))::numeric(22,2))::analytics."TransactionInfo" END, CASE WHEN ((max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType")))) IS NULL) THEN NULL::analytics."TransactionInfo" ELSE ROW(((min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType")))))::date, ((max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType")))))::date, ((sum((- t.amount)) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_FEE,DEPOSIT_REVERT_FEE}'::"TransactionType"[])))))::numeric(22,2), (((count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType")) - count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_REVERT_FEE'::"TransactionType")))))::integer, (((array_agg((- t.amount) ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType"))))[1]))::numeric(22,2), (((array_agg((- t.amount) ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType"))))[1]))::numeric(22,2))::analytics."TransactionInfo" END, CASE WHEN ((max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))))) IS NULL) THEN NULL::analytics."TransactionInfo" ELSE ROW(((min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))))))::date, ((max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))))))::date, ((sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_REVERT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))))::numeric(22,2), (((count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_INCOMING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))))))::integer, (((array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))))[1]))::numeric(22,2), (((array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))))[1]))::numeric(22,2))::analytics."TransactionInfo" END, CASE WHEN ((max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))) IS NULL) THEN NULL::analytics."TransactionInfo" ELSE ROW(((min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))))::date, ((max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))))::date, (((- sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_REVERT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))))::numeric(22,2), (((count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_OUTGOING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))))::integer, (((array_agg((- t.amount) ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[])))))[1]))::numeric(22,2), (((array_agg((- t.amount) ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[])))))[1]))::numeric(22,2))::analytics."TransactionInfo" END, CASE WHEN ((max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType")))) IS NULL) THEN NULL::analytics."TransactionInfo" ELSE ROW(((min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType")))))::date, ((max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType")))))::date, ((sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INTEREST_POSTING,DEPOSIT_INTEREST_POSTING_ADJUSTMENT}'::"TransactionType"[])))))::numeric(22,2), ((count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType"))))::integer, (((array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType"))))[1]))::numeric(22,2), (((array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType"))))[1]))::numeric(22,2))::analytics."TransactionInfo" END, CASE WHEN ((max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType")))) IS NULL) THEN NULL::analytics."TransactionInfo" ELSE ROW(((min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType")))))::date, ((max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType")))))::date, ((sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INTEREST_TAX,DEPOSIT_INTEREST_TAX_ADJUSTMENT}'::"TransactionType"[])))))::numeric(22,2), ((count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))))::integer, (((array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))))[1]))::numeric(22,2), (((array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))))[1]))::numeric(22,2))::analytics."TransactionInfo" END, (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_WITHDRAWAL,DEPOSIT_FEE,DEPOSIT_INTEREST_POSTING,DEPOSIT_MIGRATION,DEPOSIT_CLIENT_INTERBRANCH_MOVEMENT,DEPOSIT_INTEREST_TAX,DEPOSIT_INCOMING_TRANSFER,DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT,DEPOSIT_CHEQUE_ENCASHMENT,DEPOSIT_BOUNCED_CHEQUE_ENCASHMENT,DEPOSIT_CHEQUE_CLEARING,DEPOSIT_INTEREST_POSTING_ADJUSTMENT,DEPOSIT_INTEREST_TAX_ADJUSTMENT}'::"TransactionType"[]))))), CASE WHEN (first_schedule.id IS NULL) THEN NULL::analytics."ScheduledDepositInfo" ELSE ROW(first_schedule.scheduled_deposit_date, (first_schedule.deposit_amount)::numeric(22,2), (first_schedule.paid)::numeric(22,2))::analytics."ScheduledDepositInfo" END, CASE WHEN (last_schedule.id IS NULL) THEN NULL::analytics."ScheduledDepositInfo" ELSE ROW(last_schedule.scheduled_deposit_date, (last_schedule.deposit_amount)::numeric(22,2), (last_schedule.paid)::numeric(22,2))::analytics."ScheduledDepositInfo" END, CASE WHEN (prev_schedule.id IS NULL) THEN NULL::analytics."ScheduledDepositInfo" ELSE ROW(prev_schedule.scheduled_deposit_date, (prev_schedule.deposit_amount)::numeric(22,2), (prev_schedule.paid)::numeric(22,2))::analytics."ScheduledDepositInfo" END, CASE WHEN (last_paid_schedule.id IS NULL) THEN NULL::analytics."ScheduledDepositInfo" ELSE ROW(last_paid_schedule.scheduled_deposit_date, (last_paid_schedule.deposit_amount)::numeric(22,2), (last_paid_schedule.paid)::numeric(22,2))::analytics."ScheduledDepositInfo" END, CASE WHEN (next_schedule.id IS NULL) THEN NULL::analytics."ScheduledDepositInfo" ELSE ROW(next_schedule.scheduled_deposit_date, (next_schedule.deposit_amount)::numeric(22,2), (next_schedule.paid)::numeric(22,2))::analytics."ScheduledDepositInfo" END, CASE WHEN (unpaid_schedule.id IS NULL) THEN NULL::analytics."ScheduledDepositInfo" ELSE ROW(unpaid_schedule.scheduled_deposit_date, (unpaid_schedule.deposit_amount)::numeric(22,2), (unpaid_schedule.paid)::numeric(22,2))::analytics."ScheduledDepositInfo" END, a."lastActionNo", ca."isUpdate
  • Buffers: shared hit=17027548 read=2653724 dirtied=6017 written=546, local hit=13197156 read=670147, temp read=474294 written=474294
  • I/O Timings: read=122538.314 write=11.445
2. 493.462 462,149.568 ↓ 1.6 596,444 1

Nested Loop Left Join (cost=26,568,179.19..38,349,794.96 rows=370,194 width=1,222) (actual time=245,428.572..462,149.568 rows=596,444 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", ((max(m.occurred_on))::date), (sum(daa.amount)), (sum((sum(daa_1.amount)))), (sum(t.amount)), (max(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric))))), (min(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric))))), (sum(t.amount) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_REVERT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_REVERT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT,DEPOSIT_REVERT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric))))), ((count(*) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_DEPOSIT,DEPOSIT_REVERT_CHEQUE_DEPOSIT,DEPOSIT_REVERT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))))), ((array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))))[1]), ((array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))))[1]), (max(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric))))), (min(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric))))), ((- sum(t.amount) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_REVERT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_REVERT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL,DEPOSIT_REVERT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))))), ((count(*) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_WITHDRAWAL,DEPOSIT_REVERT_CHEQUE_WITHDRAWAL,DEPOSIT_REVERT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))))), ((array_agg((- t.amount) ORDER BY t."occurredOn", t."ID") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))))[1]), ((array_agg((- t.amount) ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType")))), (min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType")))), (sum((- t.amount)) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_FEE,DEPOSIT_REVERT_FEE}'::"TransactionType"[])))), ((count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType")) - count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_REVERT_FEE'::"TransactionType")))), ((array_agg((- t.amount) ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType"))))[1]), ((array_agg((- t.amount) ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType"))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))))), (min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))))), (sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_REVERT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))), ((count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_INCOMING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))))), ((array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))))[1]), ((array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))), (min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))), ((- sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_REVERT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))), ((count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_OUTGOING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))), ((array_agg((- t.amount) ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[])))))[1]), ((array_agg((- t.amount) ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[])))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType")))), (min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType")))), (sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INTEREST_POSTING,DEPOSIT_INTEREST_POSTING_ADJUSTMENT}'::"TransactionType"[])))), (count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType"))), ((array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType"))))[1]), ((array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType"))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType")))), (min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType")))), (sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INTEREST_TAX,DEPOSIT_INTEREST_TAX_ADJUSTMENT}'::"TransactionType"[])))), (count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))), ((array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))))[1]), ((array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_WITHDRAWAL,DEPOSIT_FEE,DEPOSIT_INTEREST_POSTING,DEPOSIT_MIGRATION,DEPOSIT_CLIENT_INTERBRANCH_MOVEMENT,DEPOSIT_INTEREST_TAX,DEPOSIT_INCOMING_TRANSFER,DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT,DEPOSIT_CHEQUE_ENCASHMENT,DEPOSIT_BOUNCED_CHEQUE_ENCASHMENT,DEPOSIT_CHEQUE_CLEARING,DEPOSIT_INTEREST_POSTING_ADJUSTMENT,DEPOSIT_INTEREST_TAX_ADJUSTMENT}'::"TransactionType"[]))))), (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date))), (sum(sd.paid)), sd.account_id, (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date = '2019-04-13'::date))), (('2019-04-13'::date - min(sd.scheduled_deposit_date) FILTER (WHERE (sd.deposit_amount > sd.paid)))), (count(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (count(sd.id) FILTER (WHERE sd.paid_late)), (min(sd.id) FILTER (WHERE (sd.deposit_amount > sd.paid))), first_schedule.id, first_schedule.scheduled_deposit_date, first_schedule.deposit_amount, first_schedule.paid, last_schedule.id, last_schedule.scheduled_deposit_date, last_schedule.deposit_amount, last_schedule.paid, prev_schedule.id, prev_schedule.scheduled_deposit_date, prev_schedule.deposit_amount, prev_schedule.paid, last_paid_schedule.id, last_paid_schedule.scheduled_deposit_date, last_paid_schedule.deposit_amount, last_paid_schedule.paid, next_schedule.id, next_schedule.scheduled_deposit_date, next_schedule.deposit_amount, next_schedule.paid
  • Buffers: shared hit=11241954 read=2653710 dirtied=6014 written=546, local hit=11605739 read=670145, temp read=474294 written=474294
  • I/O Timings: read=122503.624 write=11.445
3. 651.463 461,059.662 ↓ 1.6 596,444 1

Nested Loop Left Join (cost=26,568,178.62..37,629,322.57 rows=370,194 width=1,209) (actual time=245,428.569..461,059.662 rows=596,444 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", ((max(m.occurred_on))::date), (sum(daa.amount)), (sum((sum(daa_1.amount)))), (sum(t.amount)), (max(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric))))), (min(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric))))), (sum(t.amount) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_REVERT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_REVERT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT,DEPOSIT_REVERT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric))))), ((count(*) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_DEPOSIT,DEPOSIT_REVERT_CHEQUE_DEPOSIT,DEPOSIT_REVERT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))))), ((array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))))[1]), ((array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))))[1]), (max(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric))))), (min(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric))))), ((- sum(t.amount) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_REVERT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_REVERT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL,DEPOSIT_REVERT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))))), ((count(*) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_WITHDRAWAL,DEPOSIT_REVERT_CHEQUE_WITHDRAWAL,DEPOSIT_REVERT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))))), ((array_agg((- t.amount) ORDER BY t."occurredOn", t."ID") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))))[1]), ((array_agg((- t.amount) ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType")))), (min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType")))), (sum((- t.amount)) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_FEE,DEPOSIT_REVERT_FEE}'::"TransactionType"[])))), ((count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType")) - count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_REVERT_FEE'::"TransactionType")))), ((array_agg((- t.amount) ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType"))))[1]), ((array_agg((- t.amount) ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType"))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))))), (min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))))), (sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_REVERT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))), ((count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_INCOMING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))))), ((array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))))[1]), ((array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))), (min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))), ((- sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_REVERT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))), ((count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_OUTGOING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))), ((array_agg((- t.amount) ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[])))))[1]), ((array_agg((- t.amount) ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[])))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType")))), (min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType")))), (sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INTEREST_POSTING,DEPOSIT_INTEREST_POSTING_ADJUSTMENT}'::"TransactionType"[])))), (count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType"))), ((array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType"))))[1]), ((array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType"))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType")))), (min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType")))), (sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INTEREST_TAX,DEPOSIT_INTEREST_TAX_ADJUSTMENT}'::"TransactionType"[])))), (count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))), ((array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))))[1]), ((array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_WITHDRAWAL,DEPOSIT_FEE,DEPOSIT_INTEREST_POSTING,DEPOSIT_MIGRATION,DEPOSIT_CLIENT_INTERBRANCH_MOVEMENT,DEPOSIT_INTEREST_TAX,DEPOSIT_INCOMING_TRANSFER,DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT,DEPOSIT_CHEQUE_ENCASHMENT,DEPOSIT_BOUNCED_CHEQUE_ENCASHMENT,DEPOSIT_CHEQUE_CLEARING,DEPOSIT_INTEREST_POSTING_ADJUSTMENT,DEPOSIT_INTEREST_TAX_ADJUSTMENT}'::"TransactionType"[]))))), (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date))), (sum(sd.paid)), sd.account_id, (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date = '2019-04-13'::date))), (('2019-04-13'::date - min(sd.scheduled_deposit_date) FILTER (WHERE (sd.deposit_amount > sd.paid)))), (count(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (count(sd.id) FILTER (WHERE sd.paid_late)), (min(sd.id) FILTER (WHERE (sd.scheduled_deposit_date >= '2019-04-13'::date))), (min(sd.id) FILTER (WHERE (sd.deposit_amount > sd.paid))), first_schedule.id, first_schedule.scheduled_deposit_date, first_schedule.deposit_amount, first_schedule.paid, last_schedule.id, last_schedule.scheduled_deposit_date, last_schedule.deposit_amount, last_schedule.paid, prev_schedule.id, prev_schedule.scheduled_deposit_date, prev_schedule.deposit_amount, prev_schedule.paid, last_paid_schedule.id, last_paid_schedule.scheduled_deposit_date, last_paid_schedule.deposit_amount, last_paid_schedule.paid
  • Buffers: shared hit=11241954 read=2653710 dirtied=6014 written=546, local hit=10554686 read=670132, temp read=474294 written=474294
  • I/O Timings: read=122502.299 write=11.445
4. 1,020.285 459,215.311 ↓ 1.6 596,444 1

Merge Right Join (cost=26,568,178.06..36,908,850.19 rows=370,194 width=1,196) (actual time=245,428.556..459,215.311 rows=596,444 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", ((max(m.occurred_on))::date), (sum(daa.amount)), (sum((sum(daa_1.amount)))), (sum(t.amount)), (max(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric))))), (min(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric))))), (sum(t.amount) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_REVERT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_REVERT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT,DEPOSIT_REVERT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric))))), ((count(*) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_DEPOSIT,DEPOSIT_REVERT_CHEQUE_DEPOSIT,DEPOSIT_REVERT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))))), ((array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))))[1]), ((array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))))[1]), (max(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric))))), (min(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric))))), ((- sum(t.amount) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_REVERT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_REVERT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL,DEPOSIT_REVERT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))))), ((count(*) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_WITHDRAWAL,DEPOSIT_REVERT_CHEQUE_WITHDRAWAL,DEPOSIT_REVERT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))))), ((array_agg((- t.amount) ORDER BY t."occurredOn", t."ID") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))))[1]), ((array_agg((- t.amount) ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType")))), (min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType")))), (sum((- t.amount)) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_FEE,DEPOSIT_REVERT_FEE}'::"TransactionType"[])))), ((count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType")) - count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_REVERT_FEE'::"TransactionType")))), ((array_agg((- t.amount) ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType"))))[1]), ((array_agg((- t.amount) ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType"))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))))), (min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))))), (sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_REVERT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))), ((count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_INCOMING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))))), ((array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))))[1]), ((array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))), (min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))), ((- sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_REVERT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))), ((count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_OUTGOING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))))), ((array_agg((- t.amount) ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[])))))[1]), ((array_agg((- t.amount) ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[])))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType")))), (min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType")))), (sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INTEREST_POSTING,DEPOSIT_INTEREST_POSTING_ADJUSTMENT}'::"TransactionType"[])))), (count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType"))), ((array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType"))))[1]), ((array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType"))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType")))), (min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType")))), (sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INTEREST_TAX,DEPOSIT_INTEREST_TAX_ADJUSTMENT}'::"TransactionType"[])))), (count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))), ((array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))))[1]), ((array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))))[1]), (max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_WITHDRAWAL,DEPOSIT_FEE,DEPOSIT_INTEREST_POSTING,DEPOSIT_MIGRATION,DEPOSIT_CLIENT_INTERBRANCH_MOVEMENT,DEPOSIT_INTEREST_TAX,DEPOSIT_INCOMING_TRANSFER,DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT,DEPOSIT_CHEQUE_ENCASHMENT,DEPOSIT_BOUNCED_CHEQUE_ENCASHMENT,DEPOSIT_CHEQUE_CLEARING,DEPOSIT_INTEREST_POSTING_ADJUSTMENT,DEPOSIT_INTEREST_TAX_ADJUSTMENT}'::"TransactionType"[]))))), (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date))), (sum(sd.paid)), sd.account_id, (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date = '2019-04-13'::date))), (('2019-04-13'::date - min(sd.scheduled_deposit_date) FILTER (WHERE (sd.deposit_amount > sd.paid)))), (count(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (count(sd.id) FILTER (WHERE sd.paid_late)), (max(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (min(sd.id) FILTER (WHERE (sd.scheduled_deposit_date >= '2019-04-13'::date))), (min(sd.id) FILTER (WHERE (sd.deposit_amount > sd.paid))), first_schedule.id, first_schedule.scheduled_deposit_date, first_schedule.deposit_amount, first_schedule.paid, last_schedule.id, last_schedule.scheduled_deposit_date, last_schedule.deposit_amount, last_schedule.paid, prev_schedule.id, prev_schedule.scheduled_deposit_date, prev_schedule.deposit_amount, prev_schedule.paid
  • Merge Cond: (t."accountID" = ca.id)
  • Buffers: shared hit=11241954 read=2653710 dirtied=6014 written=546, local hit=8565836 read=669292, temp read=474294 written=474294
  • I/O Timings: read=122490.658 write=11.445
5. 44,212.540 87,717.410 ↑ 1.3 444,214 1

GroupAggregate (cost=775.18..3,978,826.28 rows=597,316 width=888) (actual time=438.682..87,717.410 rows=444,214 loops=1)

  • Output: t."accountID", sum(t.amount), max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_WITHDRAWAL,DEPOSIT_FEE,DEPOSIT_INTEREST_POSTING,DEPOSIT_MIGRATION,DEPOSIT_CLIENT_INTERBRANCH_MOVEMENT,DEPOSIT_INTEREST_TAX,DEPOSIT_INCOMING_TRANSFER,DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT,DEPOSIT_CHEQUE_ENCASHMENT,DEPOSIT_BOUNCED_CHEQUE_ENCASHMENT,DEPOSIT_CHEQUE_CLEARING,DEPOSIT_INTEREST_POSTING_ADJUSTMENT,DEPOSIT_INTEREST_TAX_ADJUSTMENT}'::"TransactionType"[])))), min(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))), max(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))), sum(t.amount) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_REVERT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_REVERT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT,DEPOSIT_REVERT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))), (array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))))[1], (array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))))[1], (count(*) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_DEPOSIT,DEPOSIT_CHEQUE_DEPOSIT,DEPOSIT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount > '0'::numeric)))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_DEPOSIT,DEPOSIT_REVERT_CHEQUE_DEPOSIT,DEPOSIT_REVERT_BOUNCED_CHEQUE_DEPOSIT}'::"TransactionType"[])))), min(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))), max(t."occurredOn") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))), (- sum(t.amount) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_REVERT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_REVERT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL,DEPOSIT_REVERT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric))))), (array_agg((- t.amount) ORDER BY t."occurredOn", t."ID") FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))))[1], (array_agg((- t.amount) ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE (((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[]))) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))))[1], (count(*) FILTER (WHERE ((t."transactionType" = ANY ('{DEPOSIT_WITHDRAWAL,DEPOSIT_CHEQUE_WITHDRAWAL,DEPOSIT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[])) OR ((t."transactionType" = 'DEPOSIT_MIGRATION'::"TransactionType") AND (t.amount < '0'::numeric)))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_WITHDRAWAL,DEPOSIT_REVERT_CHEQUE_WITHDRAWAL,DEPOSIT_REVERT_BOUNCED_CHEQUE_WITHDRAWAL}'::"TransactionType"[])))), min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType"))), max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType"))), sum((- t.amount)) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_FEE,DEPOSIT_REVERT_FEE}'::"TransactionType"[]))), (array_agg((- t.amount) ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType"))))[1], (array_agg((- t.amount) ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType"))))[1], (count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_FEE'::"TransactionType")) - count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_REVERT_FEE'::"TransactionType"))), min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))), max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))), sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_REVERT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))), (array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))))[1], (array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))))[1], (count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INCOMING_TRANSFER,DEPOSIT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[]))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_INCOMING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_INCOMING_TRANSFER}'::"TransactionType"[])))), min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[])))), max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[])))), (- sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_REVERT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[])))), (array_agg((- t.amount) ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[])))))[1], (array_agg((- t.amount) ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[])))))[1], (count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_OUTGOING_TRANSFER,DEPOSIT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[]))) - count(*) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_REVERT_OUTGOING_TRANSFER,DEPOSIT_REVERT_AUTOMATED_OUTGOING_TRANSFER}'::"TransactionType"[])))), min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType"))), max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType"))), sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INTEREST_POSTING,DEPOSIT_INTEREST_POSTING_ADJUSTMENT}'::"TransactionType"[]))), (array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType"))))[1], (array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType"))))[1], count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_INTEREST_POSTING'::"TransactionType")), min(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))), max(t."occurredOn") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))), sum(t.amount) FILTER (WHERE (t."transactionType" = ANY ('{DEPOSIT_INTEREST_TAX,DEPOSIT_INTEREST_TAX_ADJUSTMENT}'::"TransactionType"[]))), (array_agg(t.amount ORDER BY t."occurredOn", t."ID") FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))))[1], (array_agg(t.amount ORDER BY t."occurredOn" DESC, t."ID" DESC) FILTER (WHERE ((t."revertedByID" IS NULL) AND (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))))[1], count(*) FILTER (WHERE (t."transactionType" = 'DEPOSIT_INTEREST_TAX'::"TransactionType"))
  • Group Key: t."accountID
  • Buffers: shared hit=7217672 read=152458 dirtied=48, local hit=2447 read=2410
  • I/O Timings: read=26593.189
6. 1,881.718 43,504.870 ↑ 2.0 4,344,273 1

Merge Join (cost=775.18..642,035.96 rows=8,766,698 width=41) (actual time=436.060..43,504.870 rows=4,344,273 loops=1)

  • Output: t."accountID", t.amount, t."occurredOn", t."revertedByID", t."transactionType", t."ID
  • Merge Cond: (ca_1.id = t."accountID")
  • Buffers: shared hit=7217664 read=152458 dirtied=48, local hit=2447 read=2410
  • I/O Timings: read=26593.189
7. 283.312 283.312 ↑ 1.0 596,444 1

Index Only Scan using ix_prepare_changed_accounts on pg_temp_166.prepare_changed_accounts ca_1 (cost=0.42..15,449.09 rows=596,444 width=8) (actual time=0.081..283.312 rows=596,444 loops=1)

  • Output: ca_1.id
  • Heap Fetches: 596444
  • Buffers: local hit=2447 read=2410
  • I/O Timings: read=35.196
8. 41,339.840 41,339.840 ↑ 1.1 7,650,175 1

Index Scan using "ix_Transaction_accountID_occurredOn_ID_776102225" on instafin."Transaction" t (cost=0.56..516,133.79 rows=8,779,515 width=41) (actual time=1.465..41,339.840 rows=7,650,175 loops=1)

  • Output: t."accountID", t.amount, t."occurredOn", t."revertedByID", t."transactionType", t."ID
  • Index Cond: (t."occurredOn" < '2019-04-14'::date)
  • Filter: (t.status = 'APPROVED'::"TransactionStatus")
  • Rows Removed by Filter: 3
  • Buffers: shared hit=7217664 read=152458 dirtied=48
  • I/O Timings: read=26557.993
9. 730.899 370,477.616 ↓ 1.6 596,444 1

Materialize (cost=26,567,402.88..32,917,928.69 rows=369,654 width=316) (actual time=244,989.868..370,477.616 rows=596,444 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", ((max(m.occurred_on))::date), (sum(daa.amount)), (sum((sum(daa_1.amount)))), (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date))), (sum(sd.paid)), sd.account_id, (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date = '2019-04-13'::date))), (('2019-04-13'::date - min(sd.scheduled_deposit_date) FILTER (WHERE (sd.deposit_amount > sd.paid)))), (count(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (count(sd.id) FILTER (WHERE sd.paid_late)), (max(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (min(sd.id) FILTER (WHERE (sd.scheduled_deposit_date >= '2019-04-13'::date))), (min(sd.id) FILTER (WHERE (sd.deposit_amount > sd.paid))), first_schedule.id, first_schedule.scheduled_deposit_date, first_schedule.deposit_amount, first_schedule.paid, last_schedule.id, last_schedule.scheduled_deposit_date, last_schedule.deposit_amount, last_schedule.paid, prev_schedule.id, prev_schedule.scheduled_deposit_date, prev_schedule.deposit_amount, prev_schedule.paid
  • Buffers: shared hit=4024282 read=2501252 dirtied=5966 written=546, local hit=8563389 read=666882, temp read=474294 written=474294
  • I/O Timings: read=95897.469 write=11.445
10. 506.465 369,746.717 ↓ 1.6 596,444 1

Nested Loop Left Join (cost=26,567,402.88..32,917,004.55 rows=369,654 width=316) (actual time=244,989.849..369,746.717 rows=596,444 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", ((max(m.occurred_on))::date), (sum(daa.amount)), (sum((sum(daa_1.amount)))), (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date))), (sum(sd.paid)), sd.account_id, (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date = '2019-04-13'::date))), (('2019-04-13'::date - min(sd.scheduled_deposit_date) FILTER (WHERE (sd.deposit_amount > sd.paid)))), (count(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (count(sd.id) FILTER (WHERE sd.paid_late)), (max(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (min(sd.id) FILTER (WHERE (sd.scheduled_deposit_date >= '2019-04-13'::date))), (min(sd.id) FILTER (WHERE (sd.deposit_amount > sd.paid))), first_schedule.id, first_schedule.scheduled_deposit_date, first_schedule.deposit_amount, first_schedule.paid, last_schedule.id, last_schedule.scheduled_deposit_date, last_schedule.deposit_amount, last_schedule.paid, prev_schedule.id, prev_schedule.scheduled_deposit_date, prev_schedule.deposit_amount, prev_schedule.paid
  • Buffers: shared hit=4024282 read=2501252 dirtied=5966 written=546, local hit=8563389 read=666882, temp read=474294 written=474294
  • I/O Timings: read=95897.469 write=11.445
11. 276.795 368,047.364 ↓ 1.6 596,444 1

Nested Loop Left Join (cost=26,567,402.32..32,197,583.12 rows=369,654 width=303) (actual time=244,989.845..368,047.364 rows=596,444 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", ((max(m.occurred_on))::date), (sum(daa.amount)), (sum((sum(daa_1.amount)))), (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date))), (sum(sd.paid)), sd.account_id, (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date = '2019-04-13'::date))), (('2019-04-13'::date - min(sd.scheduled_deposit_date) FILTER (WHERE (sd.deposit_amount > sd.paid)))), (count(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (count(sd.id) FILTER (WHERE sd.paid_late)), (max(sd.id) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date))), (max(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (min(sd.id) FILTER (WHERE (sd.scheduled_deposit_date >= '2019-04-13'::date))), (min(sd.id) FILTER (WHERE (sd.deposit_amount > sd.paid))), first_schedule.id, first_schedule.scheduled_deposit_date, first_schedule.deposit_amount, first_schedule.paid, last_schedule.id, last_schedule.scheduled_deposit_date, last_schedule.deposit_amount, last_schedule.paid
  • Buffers: shared hit=4024282 read=2501252 dirtied=5966 written=546, local hit=5586588 read=665967, temp read=474294 written=474294
  • I/O Timings: read=95885.473 write=11.445
12. 727.238 360,613.241 ↓ 1.6 596,444 1

Nested Loop Left Join (cost=26,567,401.75..31,478,161.69 rows=369,654 width=290) (actual time=244,989.835..360,613.241 rows=596,444 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", ((max(m.occurred_on))::date), (sum(daa.amount)), (sum((sum(daa_1.amount)))), (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date))), (sum(sd.paid)), sd.account_id, (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date = '2019-04-13'::date))), (('2019-04-13'::date - min(sd.scheduled_deposit_date) FILTER (WHERE (sd.deposit_amount > sd.paid)))), (count(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (count(sd.id) FILTER (WHERE sd.paid_late)), (max(sd.id)), (max(sd.id) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date))), (max(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (min(sd.id) FILTER (WHERE (sd.scheduled_deposit_date >= '2019-04-13'::date))), (min(sd.id) FILTER (WHERE (sd.deposit_amount > sd.paid))), first_schedule.id, first_schedule.scheduled_deposit_date, first_schedule.deposit_amount, first_schedule.paid
  • Buffers: shared hit=4024282 read=2501252 dirtied=5966 written=546, local hit=2979753 read=293066, temp read=474294 written=474294
  • I/O Timings: read=91029.654 write=11.445
13. 653.004 357,500.227 ↓ 1.6 596,444 1

Merge Left Join (cost=26,567,401.19..30,758,740.25 rows=369,654 width=277) (actual time=244,865.100..357,500.227 rows=596,444 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", ((max(m.occurred_on))::date), (sum(daa.amount)), (sum((sum(daa_1.amount)))), (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date))), (sum(sd.paid)), sd.account_id, (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date = '2019-04-13'::date))), (('2019-04-13'::date - min(sd.scheduled_deposit_date) FILTER (WHERE (sd.deposit_amount > sd.paid)))), (count(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (count(sd.id) FILTER (WHERE sd.paid_late)), (min(sd.id)), (max(sd.id)), (max(sd.id) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date))), (max(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (min(sd.id) FILTER (WHERE (sd.scheduled_deposit_date >= '2019-04-13'::date))), (min(sd.id) FILTER (WHERE (sd.deposit_amount > sd.paid)))
  • Merge Cond: (ca.id = sd.account_id)
  • Buffers: shared hit=4024282 read=2501252 dirtied=5966 written=546, local hit=7271 read=285857, temp read=474294 written=474294
  • I/O Timings: read=90453.564 write=11.445
14. 357.837 255,928.654 ↓ 1.6 596,444 1

Merge Left Join (cost=20,185,710.22..22,016,781.86 rows=369,654 width=105) (actual time=206,310.953..255,928.654 rows=596,444 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", ((max(m.occurred_on))::date), (sum(daa.amount)), (sum((sum(daa_1.amount))))
  • Merge Cond: (ca.id = daa_1.account_id)
  • Buffers: shared hit=4024282 read=2501252 dirtied=5966 written=546, local hit=7271 read=2443, temp read=98541 written=98541
  • I/O Timings: read=80901.217 write=11.445
15. 220.892 22,026.530 ↓ 1.6 596,444 1

Merge Left Join (cost=2,063,652.61..2,462,901.53 rows=369,654 width=73) (actual time=287.895..22,026.530 rows=596,444 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", ((max(m.occurred_on))::date), (sum(daa.amount))
  • Merge Cond: (ca.id = daa.account_id)
  • Buffers: shared hit=4022108 read=65902 dirtied=1227, local hit=7271 read=2443
  • I/O Timings: read=9611.603
16. 493.290 21,805.620 ↓ 1.6 596,444 1

Merge Left Join (cost=491.15..376,249.24 rows=369,654 width=41) (actual time=287.875..21,805.620 rows=596,444 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", ((max(m.occurred_on))::date)
  • Merge Cond: (ca.id = m.account_id)
  • Buffers: shared hit=4022108 read=65902 dirtied=1227, local hit=7271 read=2443
  • I/O Timings: read=9611.603
17. 437.957 5,979.637 ↓ 1.6 596,444 1

Merge Join (cost=330.48..125,437.63 rows=369,654 width=37) (actual time=27.360..5,979.637 rows=596,444 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo
  • Merge Cond: (a.id = da.account_id)
  • Buffers: shared hit=974138 read=14361 dirtied=1102, local hit=4857
  • I/O Timings: read=1829.428
18. 589.613 4,462.548 ↑ 1.0 596,444 1

Merge Join (cost=144.01..103,731.32 rows=596,444 width=45) (actual time=27.294..4,462.548 rows=596,444 loops=1)

  • Output: ca.id, ca."isUpdate", a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", a.id
  • Merge Cond: (ca.id = a.id)
  • Buffers: shared hit=899578 read=9798 dirtied=562, local hit=4857
  • I/O Timings: read=1168.036
19. 163.757 163.757 ↑ 1.0 596,444 1

Index Scan using ix_prepare_changed_accounts on pg_temp_166.prepare_changed_accounts ca (cost=0.42..15,449.09 rows=596,444 width=9) (actual time=0.032..163.757 rows=596,444 loops=1)

  • Output: ca.id, ca."isUpdate
  • Buffers: local hit=4857
20. 3,709.178 3,709.178 ↓ 1.0 962,362 1

Index Scan using accounts_pkey on instafin.accounts a (cost=0.42..78,442.52 rows=962,304 width=36) (actual time=0.021..3,709.178 rows=962,362 loops=1)

  • Output: a.status_id, a.created_on, a.status_changed_on, a."lastActionNo", a.id
  • Buffers: shared hit=899578 read=9798 dirtied=562
  • I/O Timings: read=1168.036
21. 1,079.132 1,079.132 ↓ 1.0 596,445 1

Index Only Scan using deposit_accounts_pkey on instafin.deposit_accounts da (cost=0.42..15,060.42 rows=596,400 width=8) (actual time=0.062..1,079.132 rows=596,445 loops=1)

  • Output: da.account_id
  • Heap Fetches: 112560
  • Buffers: shared hit=74560 read=4563 dirtied=540
  • I/O Timings: read=661.392
22. 221.715 15,332.693 ↓ 1.1 596,393 1

Materialize (cost=160.67..244,957.61 rows=566,806 width=12) (actual time=260.512..15,332.693 rows=596,393 loops=1)

  • Output: m.account_id, ((max(m.occurred_on))::date)
  • Buffers: shared hit=3047970 read=51541 dirtied=125, local hit=2414 read=2443
  • I/O Timings: read=7782.175
23. 837.383 15,110.978 ↓ 1.1 596,393 1

GroupAggregate (cost=160.67..237,872.54 rows=566,806 width=12) (actual time=260.509..15,110.978 rows=596,393 loops=1)

  • Output: m.account_id, (max(m.occurred_on))::date
  • Group Key: m.account_id
  • Buffers: shared hit=3047970 read=51541 dirtied=125, local hit=2414 read=2443
  • I/O Timings: read=7782.175
24. 921.668 14,273.595 ↑ 2.6 1,281,692 1

Merge Join (cost=160.67..213,874.10 rows=3,382,673 width=16) (actual time=258.727..14,273.595 rows=1,281,692 loops=1)

  • Output: m.account_id, m.occurred_on
  • Merge Cond: (ca_2.id = m.account_id)
  • Buffers: shared hit=3047970 read=51541 dirtied=125, local hit=2414 read=2443
  • I/O Timings: read=7782.175
25. 367.105 367.105 ↑ 1.0 596,444 1

Index Only Scan using ix_prepare_changed_accounts on pg_temp_166.prepare_changed_accounts ca_2 (cost=0.42..15,449.09 rows=596,444 width=8) (actual time=0.048..367.105 rows=596,444 loops=1)

  • Output: ca_2.id
  • Heap Fetches: 596444
  • Buffers: local hit=2414 read=2443
  • I/O Timings: read=69.003
26. 12,984.822 12,984.822 ↓ 1.0 3,547,234 1

Index Scan using account_status_modification_information_account_id_idx on instafin.account_status_modification_information m (cost=0.43..155,663.90 rows=3,382,673 width=16) (actual time=0.040..12,984.822 rows=3,547,234 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=3047970 read=51541 dirtied=125
  • I/O Timings: read=7713.172
27. 0.003 0.018 ↓ 0.0 0 1

Materialize (cost=2,063,161.46..2,083,869.40 rows=213,709 width=40) (actual time=0.017..0.018 rows=0 loops=1)

  • Output: daa.account_id, (sum(daa.amount))
28. 0.001 0.015 ↓ 0.0 0 1

GroupAggregate (cost=2,063,161.46..2,081,198.03 rows=213,709 width=40) (actual time=0.015..0.015 rows=0 loops=1)

  • Output: daa.account_id, sum(daa.amount)
  • Group Key: daa.account_id
29. 0.008 0.014 ↓ 0.0 0 1

Sort (cost=2,063,161.46..2,068,283.20 rows=2,048,695 width=12) (actual time=0.014..0.014 rows=0 loops=1)

  • Output: daa.account_id, daa.amount
  • Sort Key: daa.account_id
  • Sort Method: quicksort Memory: 25kB
30. 0.006 0.006 ↓ 0.0 0 1

Result (cost=22,462.22..1,823,383.96 rows=2,048,695 width=12) (actual time=0.006..0.006 rows=0 loops=1)

  • Output: daa.account_id, daa.amount
  • One-Time Filter: (('now'::cstring)::date = '2019-04-13'::date)
31. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on instafin.deposit_account_accruals daa (cost=22,462.22..1,823,383.96 rows=2,048,695 width=12) (never executed)

  • Output: daa.account_id, daa.general_ledger_transaction_id, daa.rounding_error, daa.amount, daa.occurred_on, daa.interest_transaction_status
  • Recheck Cond: (daa.interest_transaction_status = 1)
32. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_deposit_account_accruals_accumulating (cost=0.00..21,950.04 rows=2,048,695 width=0) (never executed)

33. 79.702 233,544.287 ↓ 963.0 192,592 1

Materialize (cost=18,122,057.61..19,552,954.45 rows=200 width=40) (actual time=206,023.054..233,544.287 rows=192,592 loops=1)

  • Output: daa_1.account_id, (sum((sum(daa_1.amount))))
  • Buffers: shared hit=2174 read=2435350 dirtied=4739 written=546, temp read=98541 written=98541
  • I/O Timings: read=71289.614 write=11.445
34. 3,911.323 233,464.585 ↓ 963.0 192,592 1

GroupAggregate (cost=18,122,057.61..19,552,951.95 rows=200 width=40) (actual time=206,023.052..233,464.585 rows=192,592 loops=1)

  • Output: daa_1.account_id, sum((sum(daa_1.amount)))
  • Group Key: daa_1.account_id
  • Buffers: shared hit=2174 read=2435350 dirtied=4739 written=546, temp read=98541 written=98541
  • I/O Timings: read=71289.614 write=11.445
35. 10,161.316 229,553.262 ↑ 1.2 18,508,738 1

GroupAggregate (cost=18,122,057.61..19,217,172.77 rows=22,385,112 width=44) (actual time=206,023.045..229,553.262 rows=18,508,738 loops=1)

  • Output: daa_1.account_id, ((daa_1.occurred_on)::date), sum(daa_1.amount)
  • Group Key: daa_1.account_id, ((daa_1.occurred_on)::date)
  • Buffers: shared hit=2174 read=2435350 dirtied=4739 written=546, temp read=98541 written=98541
  • I/O Timings: read=71289.614 write=11.445
36. 30,310.125 219,391.946 ↑ 4.1 18,511,310 1

Sort (cost=18,122,057.61..18,311,892.23 rows=75,933,848 width=16) (actual time=206,023.036..219,391.946 rows=18,511,310 loops=1)

  • Output: daa_1.account_id, ((daa_1.occurred_on)::date), daa_1.amount
  • Sort Key: daa_1.account_id, ((daa_1.occurred_on)::date)
  • Sort Method: external merge Disk: 488464kB
  • Buffers: shared hit=2174 read=2435350 dirtied=4739 written=546, temp read=98541 written=98541
  • I/O Timings: read=71289.614 write=11.445
37. 3,506.741 189,081.821 ↑ 4.1 18,511,469 1

Result (cost=2.47..6,329,130.21 rows=75,933,848 width=16) (actual time=1.585..189,081.821 rows=18,511,469 loops=1)

  • Output: daa_1.account_id, ((daa_1.occurred_on)::date), daa_1.amount
  • One-Time Filter: (('now'::cstring)::date <> '2019-04-13'::date)
  • Buffers: shared hit=2171 read=2435350 dirtied=4739 written=546
  • I/O Timings: read=71289.614 write=11.445
38. 40,269.945 185,575.080 ↑ 4.1 18,511,469 1

Hash Left Join (cost=2.47..6,329,130.21 rows=75,933,848 width=16) (actual time=1.581..185,575.080 rows=18,511,469 loops=1)

  • Output: daa_1.account_id, (daa_1.occurred_on)::date, daa_1.amount
  • Hash Cond: (daa_1.general_ledger_transaction_id = glt."ID")
  • Join Filter: (daa_1.interest_transaction_status = 3)
  • Filter: ((daa_1.interest_transaction_status = 1) OR (glt."ID" IS NOT NULL))
  • Rows Removed by Filter: 181680704
  • Buffers: shared hit=2171 read=2435350 dirtied=4739 written=546
  • I/O Timings: read=71289.614 write=11.445
39. 145,305.117 145,305.117 ↓ 2.6 200,192,173 1

Seq Scan on instafin.deposit_account_accruals daa_1 (cost=0.00..5,854,541.17 rows=75,933,848 width=32) (actual time=0.746..145,305.117 rows=200,192,173 loops=1)

  • Output: daa_1.account_id, daa_1.general_ledger_transaction_id, daa_1.rounding_error, daa_1.amount, daa_1.occurred_on, daa_1.interest_transaction_status
  • Filter: ((daa_1.occurred_on)::date < '2019-04-14'::date)
  • Buffers: shared hit=2168 read=2435350 dirtied=4739 written=546
  • I/O Timings: read=71289.614 write=11.445
40. 0.001 0.018 ↓ 0.0 0 1

Hash (cost=2.45..2.45 rows=1 width=8) (actual time=0.018..0.018 rows=0 loops=1)

  • Output: glt."ID
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=3
41. 0.017 0.017 ↓ 0.0 0 1

Index Only Scan using "ix_GeneralLedgerTransaction_occurredOn_ID" on instafin."GeneralLedgerTransaction" glt (cost=0.43..2.45 rows=1 width=8) (actual time=0.017..0.017 rows=0 loops=1)

  • Output: glt."ID
  • Index Cond: (glt."occurredOn" >= '2019-04-14'::date)
  • Heap Fetches: 0
  • Buffers: shared hit=3
42. 428.572 100,918.569 ↓ 1.1 595,292 1

Materialize (cost=6,381,690.96..8,736,138.77 rows=562,852 width=180) (actual time=38,554.141..100,918.569 rows=595,292 loops=1)

  • Output: sd.account_id, (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date))), (sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date = '2019-04-13'::date))), (sum(sd.paid)), (min(sd.id)), (max(sd.id)), (max(sd.id) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date))), (max(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (count(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))), (count(sd.id) FILTER (WHERE sd.paid_late)), (min(sd.id) FILTER (WHERE (sd.scheduled_deposit_date >= '2019-04-13'::date))), (min(sd.id) FILTER (WHERE (sd.scheduled_deposit_date > '2019-04-13'::date))), (min(sd.id) FILTER (WHERE (sd.deposit_amount > sd.paid))), (('2019-04-13'::date - min(sd.scheduled_deposit_date) FILTER (WHERE (sd.deposit_amount > sd.paid))))
  • Buffers: local read=283414, temp read=375753 written=375753
  • I/O Timings: read=9552.347
43. 49,156.242 100,489.997 ↓ 1.1 595,292 1

GroupAggregate (cost=6,381,690.96..8,729,103.12 rows=562,852 width=180) (actual time=38,554.138..100,489.997 rows=595,292 loops=1)

  • Output: sd.account_id, sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date)), sum((sd.deposit_amount - sd.paid)) FILTER (WHERE (sd.scheduled_deposit_date = '2019-04-13'::date)), sum(sd.paid), min(sd.id), max(sd.id), max(sd.id) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date)), max(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid)), count(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid)), count(sd.id) FILTER (WHERE sd.paid_late), min(sd.id) FILTER (WHERE (sd.scheduled_deposit_date >= '2019-04-13'::date)), min(sd.id) FILTER (WHERE (sd.scheduled_deposit_date > '2019-04-13'::date)), min(sd.id) FILTER (WHERE (sd.deposit_amount > sd.paid)), ('2019-04-13'::date - min(sd.scheduled_deposit_date) FILTER (WHERE (sd.deposit_amount > sd.paid)))
  • Group Key: sd.account_id
  • Buffers: local read=283414, temp read=375753 written=375753
  • I/O Timings: read=9552.347
44. 34,957.183 51,333.755 ↑ 1.0 35,940,848 1

Sort (cost=6,381,690.96..6,471,543.08 rows=35,940,848 width=30) (actual time=38,553.898..51,333.755 rows=35,940,848 loops=1)

  • Output: sd.account_id, sd.deposit_amount, sd.paid, sd.scheduled_deposit_date, sd.id, sd.paid_late
  • Sort Key: sd.account_id
  • Sort Method: external merge Disk: 1494024kB
  • Buffers: local read=283414, temp read=375753 written=375753
  • I/O Timings: read=9552.347
45. 16,376.572 16,376.572 ↑ 1.0 35,940,848 1

Seq Scan on pg_temp_166.deposit_schedule_with_payments sd (cost=0.00..642,822.48 rows=35,940,848 width=30) (actual time=0.031..16,376.572 rows=35,940,848 loops=1)

  • Output: sd.account_id, sd.deposit_amount, sd.paid, sd.scheduled_deposit_date, sd.id, sd.paid_late
  • Buffers: local read=283414
  • I/O Timings: read=9552.347
46. 2,385.776 2,385.776 ↑ 1.0 1 596,444

Index Scan using ix_deposit_schedule_with_payments on pg_temp_166.deposit_schedule_with_payments first_schedule (cost=0.56..1.94 rows=1 width=21) (actual time=0.004..0.004 rows=1 loops=596,444)

  • Output: first_schedule.id, first_schedule.scheduled_deposit_date, first_schedule.account_id, first_schedule.deposit_amount, first_schedule.paid, first_schedule.paid_late
  • Index Cond: (first_schedule.id = (min(sd.id)))
  • Buffers: local hit=2972482 read=7209
  • I/O Timings: read=576.090
47. 7,157.328 7,157.328 ↑ 1.0 1 596,444

Index Scan using ix_deposit_schedule_with_payments on pg_temp_166.deposit_schedule_with_payments last_schedule (cost=0.56..1.94 rows=1 width=21) (actual time=0.011..0.012 rows=1 loops=596,444)

  • Output: last_schedule.id, last_schedule.scheduled_deposit_date, last_schedule.account_id, last_schedule.deposit_amount, last_schedule.paid, last_schedule.paid_late
  • Index Cond: (last_schedule.id = (max(sd.id)))
  • Buffers: local hit=2606835 read=372901
  • I/O Timings: read=4855.819
48. 1,192.888 1,192.888 ↑ 1.0 1 596,444

Index Scan using ix_deposit_schedule_with_payments on pg_temp_166.deposit_schedule_with_payments prev_schedule (cost=0.56..1.94 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=596,444)

  • Output: prev_schedule.id, prev_schedule.scheduled_deposit_date, prev_schedule.account_id, prev_schedule.deposit_amount, prev_schedule.paid, prev_schedule.paid_late
  • Index Cond: (prev_schedule.id = (max(sd.id) FILTER (WHERE (sd.scheduled_deposit_date < '2019-04-13'::date))))
  • Buffers: local hit=2976801 read=915
  • I/O Timings: read=11.996
49. 1,192.888 1,192.888 ↑ 1.0 1 596,444

Index Scan using ix_deposit_schedule_with_payments on pg_temp_166.deposit_schedule_with_payments last_paid_schedule (cost=0.56..1.94 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=596,444)

  • Output: last_paid_schedule.id, last_paid_schedule.scheduled_deposit_date, last_paid_schedule.account_id, last_paid_schedule.deposit_amount, last_paid_schedule.paid, last_paid_schedule.paid_late
  • Index Cond: (last_paid_schedule.id = (max(sd.id) FILTER (WHERE (sd.deposit_amount <= sd.paid))))
  • Buffers: local hit=1988850 read=840
  • I/O Timings: read=11.641
50. 596.444 596.444 ↓ 0.0 0 596,444

Index Scan using ix_deposit_schedule_with_payments on pg_temp_166.deposit_schedule_with_payments next_schedule (cost=0.56..1.94 rows=1 width=21) (actual time=0.001..0.001 rows=0 loops=596,444)

  • Output: next_schedule.id, next_schedule.scheduled_deposit_date, next_schedule.account_id, next_schedule.deposit_amount, next_schedule.paid, next_schedule.paid_late
  • Index Cond: (next_schedule.id = (min(sd.id) FILTER (WHERE (sd.scheduled_deposit_date >= '2019-04-13'::date))))
  • Buffers: local hit=1051053 read=13
  • I/O Timings: read=1.325
51. 596.444 596.444 ↑ 1.0 1 596,444

Index Scan using ix_deposit_schedule_with_payments on pg_temp_166.deposit_schedule_with_payments unpaid_schedule (cost=0.56..1.94 rows=1 width=21) (actual time=0.001..0.001 rows=1 loops=596,444)

  • Output: unpaid_schedule.id, unpaid_schedule.scheduled_deposit_date, unpaid_schedule.account_id, unpaid_schedule.deposit_amount, unpaid_schedule.paid, unpaid_schedule.paid_late
  • Index Cond: (unpaid_schedule.id = (min(sd.id) FILTER (WHERE (sd.deposit_amount > sd.paid))))
  • Buffers: local hit=1591417 read=2
  • I/O Timings: read=0.033
52.          

SubPlan (forNested Loop Left Join)

53. 596.444 5,367.996 ↑ 1.0 1 596,444

Limit (cost=12.92..12.92 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=596,444)

  • Output: account_status_modification_information.new_status_id, account_status_modification_information.id
  • Buffers: shared hit=2892795 read=14 dirtied=3
  • I/O Timings: read=34.657
54. 1,789.332 4,771.552 ↑ 6.0 1 596,444

Sort (cost=12.92..12.93 rows=6 width=16) (actual time=0.008..0.008 rows=1 loops=596,444)

  • 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=2892795 read=14 dirtied=3
  • I/O Timings: read=34.657
55. 2,982.220 2,982.220 ↑ 3.0 2 596,444

Index Scan using account_status_modification_information_account_id_idx on instafin.account_status_modification_information (cost=0.43..12.89 rows=6 width=16) (actual time=0.004..0.005 rows=2 loops=596,444)

  • 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))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2892795 read=14 dirtied=3
  • I/O Timings: read=34.657
56. 596.444 3,578.664 ↓ 0.0 0 596,444

Limit (cost=12.90..12.90 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=596,444)

  • 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=2892799
57. 1,192.888 2,982.220 ↓ 0.0 0 596,444

Sort (cost=12.90..12.90 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=596,444)

  • 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=2892799
58. 1,789.332 1,789.332 ↓ 0.0 0 596,444

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

  • 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: 2
  • Buffers: shared hit=2892799