explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BeAN

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 40,885.390 ↓ 0.0 0 1

Unique (cost=1,706,770.63..1,706,802.55 rows=399 width=252) (actual time=40,885.390..40,885.390 rows=0 loops=1)

2. 0.021 40,885.390 ↓ 0.0 0 1

Sort (cost=1,706,770.63..1,706,771.63 rows=399 width=252) (actual time=40,885.390..40,885.390 rows=0 loops=1)

  • Sort Key: (((COALESCE(CASE WHEN (ev.amount >= '0'::numeric) THEN (min(x.provider_posting_date)) ELSE (max(x.settlement_date)) END, (CASE WHEN (((min(x.provider_posting_date)) IS NULL) AND ((ev.posting_date_time)::date <> (lp.posting_date)::date)) THEN (ev.posting_date_time)::date ELSE ((ev.posting_date_time)::date + 1) END)::timestamp without time zone))::text || CASE WHEN (((min(x.provider_posting_date)) IS NULL) AND ((ev.posting_date_time)::date <> (lp.posting_date)::date)) THEN '-missing'::text ELSE '-okay'::text END)), (CASE WHEN (((min(x.provider_posting_date)) >= '2016-02-22 00:00:00'::timestamp without time zone) OR (((min(x.provider_posting_date)) IS NULL) AND ((ev.posting_date_time)::date <> (lp.posting_date)::date))) THEN (CASE WHEN (ev.amount >= '0'::numeric) THEN 0 ELSE 1 END)::bigint ELSE ev.id END), ev.posting_date_time, ev.id, (('PDS ACH Payment Settlement. Settlement Date: '::text || (COALESCE(CASE WHEN (ev.amount >= '0'::numeric) THEN (min(x.provider_posting_date)) ELSE (max(x.settlement_date)) END, (CASE WHEN (((min(x.provider_posting_date)) IS NULL) AND ((ev.posting_date_time)::date <> (lp.posting_date)::date)) THEN (ev.posting_date_time)::date ELSE ((ev.posting_date_time)::date + 1) END)::timestamp without time zone))::text)), (round(ev.amount, 2)), lc.created_date, lc.created_user_id, lc.modified_date, lc.modified_user_id, lc.modified_description, lc.effective_date, lc.record_valid, lc.archive_date, lc.id, lc.loan_id, lc.recipient_entity_id, lc.ledger_transaction_id, lc.cashflow_date, lc.cashflow_type, lc.external_id, lc.principal, lc.interest, lc.late_fee, lc.nsf_fee, lc.service_fee, lc.collection_fee, lc.charge_off_recovery, lc.product_fee, lc.origination_fee, lc.origination_fee_interest
  • Sort Method: quicksort Memory: 25kB
3. 192.969 40,885.369 ↓ 0.0 0 1

Nested Loop Anti Join (cost=1,552,248.72..1,706,753.39 rows=399 width=252) (actual time=40,885.369..40,885.369 rows=0 loops=1)

4. 150.913 39,202.928 ↓ 353.3 186,184 1

Hash Join (cost=1,552,247.99..1,703,698.96 rows=527 width=252) (actual time=21,237.629..39,202.928 rows=186,184 loops=1)

  • Hash Cond: (lo.originator_id = originator.id)
5. 279.095 39,052.001 ↓ 117.7 186,184 1

Nested Loop (cost=1,552,246.88..1,703,689.94 rows=1,582 width=256) (actual time=21,237.593..39,052.001 rows=186,184 loops=1)

6. 622.472 38,400.538 ↓ 117.7 186,184 1

Nested Loop (cost=1,552,246.46..1,700,011.56 rows=1,582 width=252) (actual time=21,237.582..38,400.538 rows=186,184 loops=1)

7. 829.575 32,326.834 ↓ 57.0 1,362,808 1

Nested Loop (cost=1,552,246.03..1,642,491.70 rows=23,890 width=53) (actual time=21,145.685..32,326.834 rows=1,362,808 loops=1)

8. 2,357.955 26,046.027 ↓ 47.4 1,362,808 1

Merge Right Join (cost=1,552,245.59..1,574,964.22 rows=28,750 width=45) (actual time=21,145.661..26,046.027 rows=1,362,808 loops=1)

  • Merge Cond: ((x.loan_id = lp.loan_id) AND (x.external_transaction_key = lp.external_transaction_key))
  • Filter: CASE WHEN (ev.amount >= '0'::numeric) THEN (COALESCE(((min(x.provider_posting_date)))::date, CASE WHEN (((min(x.provider_posting_date)) IS NULL) AND ((ev.posting_date_time)::date <> (lp.posting_date)::date)) THEN (ev.posting_date_time)::date ELSE ((ev.posting_date_time)::date + 1) END) < ((timezone('PST8PDT'::text, now()))::date - 3)) ELSE (COALESCE(((max(x.settlement_date)))::date, CASE WHEN (((min(x.provider_posting_date)) IS NULL) AND ((ev.posting_date_time)::date <> (lp.posting_date)::date)) THEN (ev.posting_date_time)::date ELSE ((ev.posting_date_time)::date + 1) END) < (timezone('PST8PDT'::text, now()))::date) END
  • Rows Removed by Filter: 21986
9. 1,059.285 4,544.724 ↓ 9.3 1,383,411 1

GroupAggregate (cost=252,729.42..272,787.23 rows=148,577 width=32) (actual time=2,544.976..4,544.724 rows=1,383,411 loops=1)

  • Group Key: x.loan_id, x.external_transaction_key
10. 2,232.260 3,485.439 ↓ 1.0 1,486,097 1

Sort (cost=252,729.42..256,443.83 rows=1,485,763 width=32) (actual time=2,544.966..3,485.439 rows=1,486,097 loops=1)

  • Sort Key: x.loan_id, x.external_transaction_key
  • Sort Method: external merge Disk: 60960kB
11. 544.206 1,253.179 ↓ 1.0 1,486,136 1

Append (cost=0.00..64,868.62 rows=1,485,763 width=32) (actual time=0.004..1,253.179 rows=1,486,136 loops=1)

12. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on loan_payment_settlement_all x (cost=0.00..0.00 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=1)

13. 660.597 660.597 ↓ 1.0 1,383,450 1

Seq Scan on loan_payment_settlement x_1 (cost=0.00..60,597.10 rows=1,383,110 width=32) (actual time=0.003..660.597 rows=1,383,450 loops=1)

14. 48.375 48.375 ↓ 1.0 102,686 1

Seq Scan on loan_payment_settlement_archive x_2 (cost=0.00..4,271.52 rows=102,652 width=32) (actual time=0.004..48.375 rows=102,686 loops=1)

15. 2,663.222 19,143.348 ↓ 24.1 1,384,794 1

Sort (cost=1,299,516.17..1,299,659.92 rows=57,501 width=45) (actual time=18,600.642..19,143.348 rows=1,384,794 loops=1)

  • Sort Key: lp.loan_id, lp.external_transaction_key
  • Sort Method: external sort Disk: 83936kB
16. 7,119.394 16,480.126 ↓ 24.1 1,384,794 1

Hash Join (cost=354,341.87..1,294,970.34 rows=57,501 width=45) (actual time=5,741.767..16,480.126 rows=1,384,794 loops=1)

  • Hash Cond: (lp.loan_id = ev.loan_id)
  • Join Filter: ((lp.external_transaction_key = ev.transaction_key) OR (lp.adjusted_external_transaction_key = ev.transaction_key))
  • Rows Removed by Join Filter: 24892087
17. 3,977.274 3,977.274 ↓ 14.6 1,355,270 1

Seq Scan on loan_payment lp (cost=0.00..779,072.47 rows=92,778 width=40) (actual time=0.065..3,977.274 rows=1,355,270 loops=1)

  • Filter: ((location_id <= 1000) AND ((payment_type)::text = 'PAYMENT'::text) AND ((payment_purpose)::text = 'LOAN'::text) AND (payment_method = 'ACH'::text) AND (payment_channel = 'LENDIFY'::text) AND (provider_name = 'PDS_ACH_DEBIT'::text))
  • Rows Removed by Filter: 3786898
18. 2,180.055 5,383.458 ↑ 1.0 4,799,849 1

Hash (cost=256,837.76..256,837.76 rows=4,800,169 width=37) (actual time=5,383.458..5,383.458 rows=4,799,849 loops=1)

  • Buckets: 65536 Batches: 128 Memory Usage: 3141kB
19. 3,203.403 3,203.403 ↑ 1.0 4,799,849 1

Seq Scan on loan_event ev (cost=0.00..256,837.76 rows=4,800,169 width=37) (actual time=0.019..3,203.403 rows=4,799,849 loops=1)

  • Filter: (transaction_type = ANY ('{PAYMENT,PAYMENT_REVERSAL,PAYMENT_BOUNCED}'::text[]))
  • Rows Removed by Filter: 1501608
20. 5,451.232 5,451.232 ↑ 1.0 1 1,362,808

Index Scan using fki_loan_event_registration_2_lendify_loan_event on loan_event_registration ler (cost=0.43..2.34 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=1,362,808)

  • Index Cond: (lendify_loan_event_id = ev.id)
21. 5,451.232 5,451.232 ↓ 0.0 0 1,362,808

Index Scan using loan_cashflow_pkey on loan_cashflow lc (cost=0.43..2.40 rows=1 width=207) (actual time=0.004..0.004 rows=0 loops=1,362,808)

  • Index Cond: (id = ler.asset_loan_cashflow_id)
  • Filter: (((cashflow_type)::text = ANY ('{STANDARD,ADJUSTMENT,CHARGEBACK}'::text[])) AND (cashflow_date >= ((now())::date - 60)))
  • Rows Removed by Filter: 1
22. 372.368 372.368 ↑ 1.0 1 186,184

Index Scan using loan_pkey on loan lo (cost=0.42..2.32 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=186,184)

  • Index Cond: (id = lc.loan_id)
23. 0.005 0.014 ↑ 1.0 1 1

Hash (cost=1.10..1.10 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on originator (cost=0.00..1.10 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)

  • Filter: (name = 'LENDIFY'::text)
  • Rows Removed by Filter: 7
25. 186.184 1,489.472 ↑ 1.0 1 186,184

Nested Loop (cost=0.72..5.73 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=186,184)

  • Join Filter: (lcr.created_date >= lc.created_date)
26. 930.920 930.920 ↑ 1.0 1 186,184

Index Scan using fki_loan_cashflow_report_item_2_loan_cashflow_id on loan_cashflow_report_item lcri (cost=0.43..3.65 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=186,184)

  • Index Cond: (lc.id = loan_cashflow_id)
  • Filter: (type = 'IS_DISBURSEMENT'::text)
  • Rows Removed by Filter: 1
27. 372.368 372.368 ↑ 1.0 1 186,184

Index Scan using loan_cashflow_report_pkey on loan_cashflow_report lcr (cost=0.29..2.06 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=186,184)

  • Index Cond: (id = lcri.loan_cashflow_report_id)
  • Filter: (status <> 'INVALID'::text)
Planning time : 2.679 ms
Execution time : 40,910.832 ms