explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KJ1l

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 143,824.052 ↓ 0.0 0 1

Unique (cost=1,706,717.45..1,706,717.53 rows=1 width=252) (actual time=143,824.052..143,824.052 rows=0 loops=1)

2. 17.605 143,824.051 ↓ 0.0 0 1

Sort (cost=1,706,717.45..1,706,717.45 rows=1 width=252) (actual time=143,824.051..143,824.051 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. 210.153 143,806.446 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,552,248.72..1,706,717.44 rows=1 width=252) (actual time=143,806.446..143,806.446 rows=0 loops=1)

  • Filter: (lcri.id IS NULL)
  • Rows Removed by Filter: 187184
4. 189.282 136,148.933 ↓ 353.3 186,184 1

Hash Join (cost=1,552,247.99..1,703,696.19 rows=527 width=252) (actual time=81,926.199..136,148.933 rows=186,184 loops=1)

  • Hash Cond: (lo.originator_id = originator.id)
5. 241.917 135,959.635 ↓ 117.7 186,184 1

Nested Loop (cost=1,552,246.88..1,703,687.16 rows=1,582 width=256) (actual time=81,926.155..135,959.635 rows=186,184 loops=1)

6. 662.431 135,159.166 ↓ 117.7 186,184 1

Nested Loop (cost=1,552,246.46..1,700,008.79 rows=1,582 width=252) (actual time=81,925.734..135,159.166 rows=186,184 loops=1)

7. 1,305.341 109,966.191 ↓ 57.0 1,362,808 1

Nested Loop (cost=1,552,246.03..1,642,490.10 rows=23,890 width=53) (actual time=79,579.469..109,966.191 rows=1,362,808 loops=1)

8. 2,863.744 85,493.114 ↓ 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=79,578.670..85,493.114 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,215.746 6,167.292 ↓ 9.3 1,383,411 1

GroupAggregate (cost=252,729.42..272,787.23 rows=148,577 width=32) (actual time=3,773.327..6,167.292 rows=1,383,411 loops=1)

  • Group Key: x.loan_id, x.external_transaction_key
10. 2,516.067 4,951.546 ↓ 1.0 1,486,097 1

Sort (cost=252,729.42..256,443.83 rows=1,485,763 width=32) (actual time=3,773.312..4,951.546 rows=1,486,097 loops=1)

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

Append (cost=0.00..64,868.62 rows=1,485,763 width=32) (actual time=0.403..2,435.479 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. 1,747.697 1,747.697 ↓ 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.402..1,747.697 rows=1,383,450 loops=1)

14. 138.927 138.927 ↓ 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.650..138.927 rows=102,686 loops=1)

15. 2,784.603 76,462.078 ↓ 24.1 1,384,794 1

Sort (cost=1,299,516.17..1,299,659.92 rows=57,501 width=45) (actual time=75,803.782..76,462.078 rows=1,384,794 loops=1)

  • Sort Key: lp.loan_id, lp.external_transaction_key
  • Sort Method: external sort Disk: 83936kB
16. 5,750.607 73,677.475 ↓ 24.1 1,384,794 1

Hash Join (cost=354,341.87..1,294,970.34 rows=57,501 width=45) (actual time=13,670.852..73,677.475 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. 54,261.633 54,261.633 ↓ 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.501..54,261.633 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,236.923 13,665.235 ↑ 1.0 4,799,849 1

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

  • Buckets: 65536 Batches: 128 Memory Usage: 3141kB
19. 11,428.312 11,428.312 ↑ 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.353..11,428.312 rows=4,799,849 loops=1)

  • Filter: (transaction_type = ANY ('{PAYMENT,PAYMENT_REVERSAL,PAYMENT_BOUNCED}'::text[]))
  • Rows Removed by Filter: 1501608
20. 23,167.736 23,167.736 ↑ 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.016..0.017 rows=1 loops=1,362,808)

  • Index Cond: (lendify_loan_event_id = ev.id)
21. 24,530.544 24,530.544 ↓ 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.018..0.018 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. 558.552 558.552 ↑ 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.003..0.003 rows=1 loops=186,184)

  • Index Cond: (id = lc.loan_id)
23. 0.006 0.016 ↑ 1.0 1 1

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

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

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

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

Nested Loop (cost=0.72..5.72 rows=1 width=8) (actual time=0.034..0.040 rows=1 loops=186,184)

26. 6,516.440 6,516.440 ↑ 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=12) (actual time=0.030..0.035 rows=1 loops=186,184)

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

Index Scan using loan_cashflow_report_pkey on loan_cashflow_report lcr (cost=0.29..2.06 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=187,184)

  • Index Cond: (id = lcri.loan_cashflow_report_id)
  • Filter: (status <> 'INVALID'::text)
Planning time : 56.051 ms
Execution time : 143,849.439 ms