explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NzDr

Settings
# exclusive inclusive rows x rows loops node
1. 0.030 56,216.643 ↑ 153.5 46 1

Append (cost=9,142,620.03..9,714,653.28 rows=7,060 width=68) (actual time=46,585.777..56,216.643 rows=46 loops=1)

  • Buffers: shared hit=3662275 read=121595, temp read=8150 written=13969
  • I/O Timings: read=31834.792
2.          

CTE accs

3. 1,343.640 1,343.640 ↑ 1.2 1,190,573 1

Seq Scan on instafin.accounts a_1 (cost=0.00..97,736.10 rows=1,477,310 width=28) (actual time=0.054..1,343.640 rows=1,190,573 loops=1)

  • Output: a_1.id, a_1.last_action_type, a_1.last_action_occurred_on, a_1.status_id
  • Filter: (a_1.account_type_id = 1)
  • Rows Removed by Filter: 1115421
  • Buffers: shared hit=59832 read=1954
  • I/O Timings: read=219.302
4. 221.823 46,874.956 ↑ 116.6 21 1

GroupAggregate (cost=9,044,883.93..9,054,397.48 rows=2,449 width=68) (actual time=46,585.775..46,874.956 rows=21 loops=1)

  • Output: sum((((lai.initial_principal_due + lai.current_interest_due) + lai.paid_interest) + COALESCE((sum(lais.amount)), '0'::numeric))), lai.obligatory_payment_date, 'due'::text
  • Group Key: lai.obligatory_payment_date
  • Buffers: shared hit=3371727 read=114841, temp read=5827 written=11646
  • I/O Timings: read=28987.032
5. 2,053.428 46,653.133 ↑ 2.0 323,238 1

Sort (cost=9,044,883.93..9,046,464.42 rows=632,196 width=50) (actual time=46,564.235..46,653.133 rows=323,238 loops=1)

  • Output: lai.obligatory_payment_date, lai.initial_principal_due, lai.current_interest_due, lai.paid_interest, (sum(lais.amount))
  • Sort Key: lai.obligatory_payment_date
  • Sort Method: quicksort Memory: 36633kB
  • Buffers: shared hit=3371727 read=114841, temp read=5827 written=11646
  • I/O Timings: read=28987.032
6. 496.445 44,599.705 ↑ 2.0 323,238 1

Nested Loop Left Join (cost=410,420.16..8,983,971.80 rows=632,196 width=50) (actual time=13,545.003..44,599.705 rows=323,238 loops=1)

  • Output: lai.obligatory_payment_date, lai.initial_principal_due, lai.current_interest_due, lai.paid_interest, (sum(lais.amount))
  • Buffers: shared hit=3371727 read=114841, temp read=5827 written=11646
  • I/O Timings: read=28987.032
7. 577.798 16,304.792 ↑ 2.0 323,238 1

Nested Loop Left Join (cost=410,415.49..6,011,070.11 rows=632,196 width=26) (actual time=13,528.153..16,304.792 rows=323,238 loops=1)

  • Output: lai.initial_principal_due, lai.current_interest_due, lai.paid_interest, lai.obligatory_payment_date, lai.id
  • Filter: ((lai.status_id = 1) OR ((lai.status_id = 3) AND (lai.obligatory_payment_date < ((date_trunc('second'::text, min(ar.occurred_on))))::date)))
  • Rows Removed by Filter: 6807
  • Buffers: shared hit=1264108 read=22886, temp read=5827 written=11646
  • I/O Timings: read=7163.458
8. 666.788 14,736.859 ↑ 2.0 330,045 1

Merge Join (cost=410,406.88..433,951.90 rows=644,195 width=34) (actual time=13,528.112..14,736.859 rows=330,045 loops=1)

  • Output: lai.initial_principal_due, lai.current_interest_due, lai.paid_interest, lai.obligatory_payment_date, lai.id, lai.status_id
  • Merge Cond: (a.id = lai.account_id)
  • Join Filter: ((a.last_action_type <> 'WriteOff'::"AccountActionType") OR (lai.obligatory_payment_date < (a.last_action_occurred_on)::date) OR ((a.last_action_type IS NULL) AND (a.status_id <> 9)))
  • Buffers: shared hit=604012 read=22876, temp read=5827 written=11646
  • I/O Timings: read=7156.550
9. 1,755.508 4,442.927 ↑ 1.2 1,183,449 1

Sort (cost=206,177.65..209,870.93 rows=1,477,310 width=28) (actual time=4,076.639..4,442.927 rows=1,183,449 loops=1)

  • Output: a.id, a.last_action_type, a.last_action_occurred_on, a.status_id
  • Sort Key: a.id
  • Sort Method: external merge Disk: 46600kB
  • Buffers: shared hit=59837 read=1954, temp read=5827 written=11646
  • I/O Timings: read=219.302
10. 2,687.419 2,687.419 ↑ 1.2 1,190,573 1

CTE Scan on accs a (cost=0.00..29,546.20 rows=1,477,310 width=28) (actual time=0.063..2,687.419 rows=1,190,573 loops=1)

  • Output: a.id, a.last_action_type, a.last_action_occurred_on, a.status_id
  • Buffers: shared hit=59832 read=1954, temp written=5819
  • I/O Timings: read=219.302
11. 699.112 9,627.144 ↑ 1.1 330,045 1

Sort (cost=204,229.22..205,123.63 rows=357,763 width=42) (actual time=9,419.632..9,627.144 rows=330,045 loops=1)

  • Output: lai.initial_principal_due, lai.current_interest_due, lai.paid_interest, lai.obligatory_payment_date, lai.id, lai.account_id, lai.status_id
  • Sort Key: lai.account_id
  • Sort Method: quicksort Memory: 38084kB
  • Buffers: shared hit=544175 read=20922
  • I/O Timings: read=6937.248
12. 8,928.032 8,928.032 ↑ 1.1 330,045 1

Index Scan using loan_account_installments_obl_payment_date_idx on instafin.loan_account_installments lai (cost=0.44..171,228.01 rows=357,763 width=42) (actual time=1.062..8,928.032 rows=330,045 loops=1)

  • Output: lai.initial_principal_due, lai.current_interest_due, lai.paid_interest, lai.obligatory_payment_date, lai.id, lai.account_id, lai.status_id
  • Index Cond: ((lai.obligatory_payment_date >= '2018-10-26'::date) AND (lai.obligatory_payment_date < '2018-11-26'::date))
  • Filter: (NOT lai.is_prepayment)
  • Buffers: shared hit=544175 read=20922
  • I/O Timings: read=6937.248
13. 0.000 990.135 ↑ 1.0 1 330,045

Aggregate (cost=8.61..8.63 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=330,045)

  • Output: date_trunc('second'::text, min(ar.occurred_on))
  • Buffers: shared hit=660096 read=10
  • I/O Timings: read=6.908
14. 329.921 990.135 ↓ 0.0 0 330,045

Nested Loop (cost=0.56..8.61 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=330,045)

  • Output: ar.occurred_on
  • Buffers: shared hit=660096 read=10
  • I/O Timings: read=6.908
15. 660.090 660.090 ↓ 0.0 0 330,045

Index Scan using ix_revision_installment on instafin.loan_account_installment_revisions lar (cost=0.29..4.30 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=330,045)

  • Output: lar.id, lar.installment_id, lar.revision_id, lar.account_id, lar.obligatory_payment_date, lar.last_penalty_calculation_date, lar.initial_principal_due, lar.paid_interest, lar.initial_loan_account_balance, lar.current_penalty_amount, lar.current_fee_amount, lar.current_principal_due, lar.current_interest_due, lar.status_id, lar.repaid_date, lar.is_prepayment, lar.is_obliterated, lar.is_zeroth, lar."gracePeriodType
  • Index Cond: (lai.id = lar.installment_id)
  • Buffers: shared hit=660085 read=9
  • I/O Timings: read=6.880
16. 0.124 0.124 ↓ 0.0 0 4

Index Scan using account_revisions_pkey on instafin.account_revisions ar (cost=0.28..4.30 rows=1 width=16) (actual time=0.031..0.031 rows=0 loops=4)

  • Output: ar.id, ar.occurred_on, ar."interestRate
  • Index Cond: (ar.id = lar.revision_id)
  • Filter: ((ar.occurred_on)::date >= '2018-10-26'::date)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=11 read=1
  • I/O Timings: read=0.028
17. 646.476 27,798.468 ↑ 1.0 1 323,238

Aggregate (cost=4.67..4.68 rows=1 width=32) (actual time=0.086..0.086 rows=1 loops=323,238)

  • Output: sum(lais.amount)
  • Buffers: shared hit=2107619 read=91955
  • I/O Timings: read=21823.574
18. 27,151.992 27,151.992 ↓ 2.0 2 323,238

Index Only Scan using ix_loan_sections_all on instafin.loan_account_installment_sections lais (cost=0.57..4.67 rows=1 width=6) (actual time=0.072..0.084 rows=2 loops=323,238)

  • Output: lais.installment_id, lais.id, lais.section_type_id, lais.action_type_id, lais.occurred_on, lais.amount
  • Index Cond: ((lais.installment_id = lai.id) AND (lais.occurred_on < lai.obligatory_payment_date))
  • Filter: ((lais.action_type_id = ANY ('{3,4}'::bigint[])) AND (lais.section_type_id = ANY ('{4,3}'::bigint[])))
  • Rows Removed by Filter: 2
  • Heap Fetches: 744394
  • Buffers: shared hit=2107619 read=91955
  • I/O Timings: read=21823.574
19. 0.091 9,341.657 ↑ 184.4 25 1

Merge Full Join (cost=562,273.17..562,449.10 rows=4,611 width=68) (actual time=8,802.856..9,341.657 rows=25 loops=1)

  • Output: (COALESCE((abs(sum(lais_1.amount))), '0'::numeric) + COALESCE((sum(t.amount)), '0'::numeric)), COALESCE(((lais_1.occurred_on)::date), ((t."occurredOn")::date)), 'payment'::text
  • Merge Cond: (((t."occurredOn")::date) = ((lais_1.occurred_on)::date))
  • Buffers: shared hit=290548 read=6754, temp read=2323 written=2323
  • I/O Timings: read=2847.760
20. 0.006 2,009.686 ↓ 0.0 0 1

GroupAggregate (cost=264,742.69..264,751.66 rows=397 width=36) (actual time=2,009.686..2,009.686 rows=0 loops=1)

  • Output: ((t."occurredOn")::date), sum(t.amount)
  • Group Key: ((t."occurredOn")::date)
  • Buffers: shared hit=98886 read=4385
  • I/O Timings: read=1299.187
21. 0.047 2,009.680 ↓ 0.0 0 1

Sort (cost=264,742.69..264,743.69 rows=402 width=9) (actual time=2,009.680..2,009.680 rows=0 loops=1)

  • Output: ((t."occurredOn")::date), t.amount
  • Sort Key: ((t."occurredOn")::date)
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=98886 read=4385
  • I/O Timings: read=1299.187
22. 2,009.633 2,009.633 ↓ 0.0 0 1

Index Scan using "ix_Transaction_occurredOn" on instafin."Transaction" t (cost=0.56..264,725.30 rows=402 width=9) (actual time=2,009.633..2,009.633 rows=0 loops=1)

  • Output: (t."occurredOn")::date, t.amount
  • Index Cond: ((t."occurredOn" >= '2018-10-25 18:00:00+02'::timestamp with time zone) AND (t."occurredOn" < '2018-11-25 17:00:00+01'::timestamp with time zone))
  • Filter: ((t."revertedByID" IS NULL) AND (t."transactionType" = 'LOAN_RECOVERY'::"TransactionType"))
  • Rows Removed by Filter: 839988
  • Buffers: shared hit=98886 read=4385
  • I/O Timings: read=1299.187
23. 0.049 7,331.880 ↑ 92.9 25 1

Materialize (cost=297,530.48..297,617.59 rows=2,323 width=36) (actual time=6,793.153..7,331.880 rows=25 loops=1)

  • Output: ((lais_1.occurred_on)::date), (abs(sum(lais_1.amount)))
  • Buffers: shared hit=191662 read=2369, temp read=2323 written=2323
  • I/O Timings: read=1548.573
24. 345.777 7,331.831 ↑ 92.9 25 1

GroupAggregate (cost=297,530.48..297,588.55 rows=2,323 width=36) (actual time=6,793.138..7,331.831 rows=25 loops=1)

  • Output: ((lais_1.occurred_on)::date), abs(sum(lais_1.amount))
  • Group Key: ((lais_1.occurred_on)::date)
  • Buffers: shared hit=191662 read=2369, temp read=2323 written=2323
  • I/O Timings: read=1548.573
25. 2,922.188 6,986.054 ↓ 412.1 957,292 1

Sort (cost=297,530.48..297,536.29 rows=2,323 width=10) (actual time=6,788.823..6,986.054 rows=957,292 loops=1)

  • Output: ((lais_1.occurred_on)::date), lais_1.amount
  • Sort Key: ((lais_1.occurred_on)::date)
  • Sort Method: external merge Disk: 18576kB
  • Buffers: shared hit=191662 read=2369, temp read=2323 written=2323
  • I/O Timings: read=1548.573
26. 772.518 4,063.866 ↓ 412.1 957,292 1

Hash Join (cost=268,987.52..297,400.60 rows=2,323 width=10) (actual time=789.308..4,063.866 rows=957,292 loops=1)

  • Output: (lais_1.occurred_on)::date, lais_1.amount
  • Hash Cond: (lais_1.transaction_id = t_1."ID")
  • Buffers: shared hit=191662 read=2369
  • I/O Timings: read=1548.573
27. 2,507.533 2,507.533 ↓ 4.2 961,132 1

Index Scan using lais_occurred_ts_idx on instafin.loan_account_installment_sections lais_1 (cost=0.57..27,526.49 rows=228,992 width=22) (actual time=0.834..2,507.533 rows=961,132 loops=1)

  • Output: lais_1.id, lais_1.installment_id, lais_1.section_type_id, lais_1.action_type_id, lais_1.transaction_id, lais_1.amount, lais_1.undo_section_id, lais_1.occurred_on, lais_1.created_on, lais_1.product_fee_id
  • Index Cond: ((lais_1.occurred_on >= '2018-10-25 18:00:00+02'::timestamp with time zone) AND (lais_1.occurred_on < '2018-11-25 17:00:00+01'::timestamp with time zone))
  • Filter: ((lais_1.transaction_id IS NOT NULL) AND (lais_1.section_type_id = ANY ('{4,3}'::bigint[])) AND (lais_1.action_type_id = 3))
  • Rows Removed by Filter: 734918
  • Buffers: shared hit=88045 read=2369
  • I/O Timings: read=1548.573
28. 304.194 783.815 ↓ 2.5 835,591 1

Hash (cost=264,724.29..264,724.29 rows=341,013 width=8) (actual time=783.815..783.815 rows=835,591 loops=1)

  • Output: t_1."ID
  • Buckets: 1048576 (originally 524288) Batches: 1 (originally 1) Memory Usage: 40833kB
  • Buffers: shared hit=103281
29. 479.621 479.621 ↓ 2.5 835,591 1

Index Scan using "ix_Transaction_occurredOn" on instafin."Transaction" t_1 (cost=0.56..264,724.29 rows=341,013 width=8) (actual time=0.075..479.621 rows=835,591 loops=1)

  • Output: t_1."ID
  • Index Cond: ((t_1."occurredOn" >= '2018-10-25 18:00:00+02'::timestamp with time zone) AND (t_1."occurredOn" < '2018-11-25 17:00:00+01'::timestamp with time zone))
  • Filter: ((t_1."accountID" IS NOT NULL) AND (t_1."revertedByID" IS NULL) AND (t_1.status = 'APPROVED'::"TransactionStatus"))
  • Rows Removed by Filter: 4397
  • Buffers: shared hit=103281