explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3znj

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 7.363 ↓ 0.0 0 1

Sort (cost=14,569.40..14,569.40 rows=2 width=296) (actual time=7.363..7.363 rows=0 loops=1)

  • Sort Key: (14), p.pay_date
  • Sort Method: quicksort Memory: 25kB
2. 0.000 7.354 ↓ 0.0 0 1

Unique (cost=14,569.29..14,569.37 rows=2 width=189) (actual time=7.354..7.354 rows=0 loops=1)

3. 0.021 7.354 ↓ 0.0 0 1

Sort (cost=14,569.29..14,569.29 rows=2 width=189) (actual time=7.354..7.354 rows=0 loops=1)

  • Sort Key: p.pay_date, p.accr_start_date, (sum((p.paydown * (CASE WHEN (d.pss_type = 1) THEN 0 ELSE CASE WHEN (d.pss_type = 7) THEN 0 ELSE CASE WHEN (d.pss_type = 5) THEN 0 ELSE CASE WHEN (d.pss_type = 6) THEN 0 ELSE CASE WHEN (d.pss_type = 4) THEN 0 ELSE CASE WHEN (d.pss_type = 10) THEN 0 ELSE CASE WHEN (d.pss_type = 11) THEN 0 ELSE NULL::integer END END END END END END END)::numeric))), (sum((p.principal_writedown * (CASE WHEN (d.pss_type = 1) THEN 0 ELSE CASE WHEN (d.pss_type = 7) THEN 0 ELSE CASE WHEN (d.pss_type = 5) THEN 0 ELSE CASE WHEN (d.pss_type = 6) THEN 0 ELSE CASE WHEN (d.pss_type = 4) THEN 0 ELSE CASE WHEN (d.pss_type = 10) THEN 0 ELSE CASE WHEN (d.pss_type = 11) THEN 0 ELSE NULL::integer END END END END END END END)::numeric))), (sum((p.interest * (CASE WHEN (d.pss_type = 1) THEN 0 ELSE CASE WHEN (d.pss_type = 7) THEN 0 ELSE CASE WHEN (d.pss_type = 5) THEN 0 ELSE CASE WHEN (d.pss_type = 6) THEN 0 ELSE CASE WHEN (d.pss_type = 4) THEN 0 ELSE CASE WHEN (d.pss_type = 10) THEN 0 ELSE CASE WHEN (d.pss_type = 11) THEN 0 ELSE NULL::integer END END END END END END END)::numeric))), (sum((p.accrued * (CASE WHEN (d.pss_type = 1) THEN 0 ELSE CASE WHEN (d.pss_type = 7) THEN 0 ELSE CASE WHEN (d.pss_type = 5) THEN 0 ELSE CASE WHEN (d.pss_type = 6) THEN 0 ELSE CASE WHEN (d.pss_type = 4) THEN 0 ELSE CASE WHEN (d.pss_type = 10) THEN 0 ELSE CASE WHEN (d.pss_type = 11) THEN 0 ELSE NULL::integer END END END END END END END)::numeric))), (sum((p.interest_shortfall * (CASE WHEN (d.pss_type = 1) THEN 0 ELSE CASE WHEN (d.pss_type = 7) THEN 0 ELSE CASE WHEN (d.pss_type = 5) THEN 0 ELSE CASE WHEN (d.pss_type = 6) THEN 0 ELSE CASE WHEN (d.pss_type = 4) THEN 0 ELSE CASE WHEN (d.pss_type = 10) THEN 0 ELSE CASE WHEN (d.pss_type = 11) THEN 0 ELSE NULL::integer END END END END END END END)::numeric))), (sum((p.implied_writedown * (CASE WHEN (d.pss_type = 1) THEN 0 ELSE CASE WHEN (d.pss_type = 7) THEN 0 ELSE CASE WHEN (d.pss_type = 5) THEN 0 ELSE CASE WHEN (d.pss_type = 6) THEN 0 ELSE CASE WHEN (d.pss_type = 4) THEN 0 ELSE CASE WHEN (d.pss_type = 10) THEN 0 ELSE CASE WHEN (d.pss_type = 11) THEN 0 ELSE NULL::integer END END END END END END END)::double precision))), (sum((p.unrealized_accum_writedown * (CASE WHEN (d.pss_type = 1) THEN 0 ELSE CASE WHEN (d.pss_type = 7) THEN 0 ELSE CASE WHEN (d.pss_type = 5) THEN 0 ELSE CASE WHEN (d.pss_type = 6) THEN 0 ELSE CASE WHEN (d.pss_type = 4) THEN 0 ELSE CASE WHEN (d.pss_type = 10) THEN 0 ELSE CASE WHEN (d.pss_type = 11) THEN 0 ELSE NULL::integer END END END END END END END)::numeric))), (sum((p.balance * (CASE WHEN (d.pss_type = 1) THEN 0 ELSE CASE WHEN (d.pss_type = 7) THEN 0 ELSE CASE WHEN (d.pss_type = 5) THEN 0 ELSE CASE WHEN (d.pss_type = 6) THEN 0 ELSE CASE WHEN (d.pss_type = 4) THEN 0 ELSE CASE WHEN (d.pss_type = 10) THEN 0 ELSE CASE WHEN (d.pss_type = 11) THEN 0 ELSE NULL::integer END END END END END END END)::numeric))), (sum((p.accum_int_short * (CASE WHEN (d.pss_type = 1) THEN 0 ELSE CASE WHEN (d.pss_type = 7) THEN 0 ELSE CASE WHEN (d.pss_type = 5) THEN 0 ELSE CASE WHEN (d.pss_type = 6) THEN 0 ELSE CASE WHEN (d.pss_type = 4) THEN 0 ELSE CASE WHEN (d.pss_type = 10) THEN 0 ELSE CASE WHEN (d.pss_type = 11) THEN 0 ELSE NULL::integer END END END END END END END)::numeric))), (sum((p.cash_flow * (CASE WHEN (d.pss_type = 1) THEN 0 ELSE CASE WHEN (d.pss_type = 7) THEN 0 ELSE CASE WHEN (d.pss_type = 5) THEN 0 ELSE CASE WHEN (d.pss_type = 6) THEN 0 ELSE CASE WHEN (d.pss_type = 4) THEN 0 ELSE CASE WHEN (d.pss_type = 10) THEN 0 ELSE CASE WHEN (d.pss_type = 11) THEN 0 ELSE NULL::integer END END END END END END END)::double precision))), (sum((p.implied_balance * (CASE WHEN (d.pss_type = 1) THEN 0 ELSE CASE WHEN (d.pss_type = 7) THEN 0 ELSE CASE WHEN (d.pss_type = 5) THEN 0 ELSE CASE WHEN (d.pss_type = 6) THEN 0 ELSE CASE WHEN (d.pss_type = 4) THEN 0 ELSE CASE WHEN (d.pss_type = 10) THEN 0 ELSE CASE WHEN (d.pss_type = 11) THEN 0 ELSE NULL::integer END END END END END END END)::double precision))), (14), (14)
  • Sort Method: quicksort Memory: 25kB
4. 0.000 7.333 ↓ 0.0 0 1

Append (cost=7,240.59..14,569.28 rows=2 width=189) (actual time=7.333..7.333 rows=0 loops=1)

5. 0.000 5.364 ↓ 0.0 0 1

GroupAggregate (cost=7,240.59..7,241.02 rows=1 width=78) (actual time=5.364..5.364 rows=0 loops=1)

  • Filter: (((((sum((p.paydown * (CASE WHEN (d.pss_type = 1) THEN 0 ELSE CASE WHEN (d.pss_type = 7) THEN 0 ELSE CASE WHEN (d.pss_type = 5) THEN 0 ELSE CASE WHEN (d.pss_type = 6) THEN 0 ELSE CASE WHEN (d.pss_type = 4) THEN 0 ELSE CASE WHEN (d.pss_type = 10) THEN 0 ELSE CASE WHEN (d.pss_type = 11) THEN 0 ELSE NULL::integer END END END END END END END)::numeric)) + sum((p.principal_writedown * (CASE WHEN (d.pss_type = 1) THEN 0 ELSE CASE WHEN (d.pss_type = 7) THEN 0 ELSE CASE WHEN (d.pss_type = 5) THEN 0 ELSE CASE WHEN (d.pss_type = 6) THEN 0 ELSE CASE WHEN (d.pss_type = 4) THEN 0 ELSE CASE WHEN (d.pss_type = 10) THEN 0 ELSE CASE WHEN (d.pss_type = 11) THEN 0 ELSE NULL::integer END END END END END END END)::numeric))) + sum((p.interest * (CASE WHEN (d.pss_type = 1) THEN 0 ELSE CASE WHEN (d.pss_type = 7) THEN 0 ELSE CASE WHEN (d.pss_type = 5) THEN 0 ELSE CASE WHEN (d.pss_type = 6) THEN 0 ELSE CASE WHEN (d.pss_type = 4) THEN 0 ELSE CASE WHEN (d.pss_type = 10) THEN 0 ELSE CASE WHEN (d.pss_type = 11) THEN 0 ELSE NULL::integer END END END END END END END)::numeric))) + sum((p.balance * (CASE WHEN (d.pss_type = 1) THEN 0 ELSE CASE WHEN (d.pss_type = 7) THEN 0 ELSE CASE WHEN (d.pss_type = 5) THEN 0 ELSE CASE WHEN (d.pss_type = 6) THEN 0 ELSE CASE WHEN (d.pss_type = 4) THEN 0 ELSE CASE WHEN (d.pss_type = 10) THEN 0 ELSE CASE WHEN (d.pss_type = 11) THEN 0 ELSE NULL::integer END END END END END END END)::numeric))) + sum((p.accrued * (CASE WHEN (d.pss_type = 1) THEN 0 ELSE CASE WHEN (d.pss_type = 7) THEN 0 ELSE CASE WHEN (d.pss_type = 5) THEN 0 ELSE CASE WHEN (d.pss_type = 6) THEN 0 ELSE CASE WHEN (d.pss_type = 4) THEN 0 ELSE CASE WHEN (d.pss_type = 10) THEN 0 ELSE CASE WHEN (d.pss_type = 11) THEN 0 ELSE NULL::integer END END END END END END END)::numeric))) <> 0::numeric)
6. 0.007 5.364 ↓ 0.0 0 1

Sort (cost=7,240.59..7,240.60 rows=1 width=78) (actual time=5.364..5.364 rows=0 loops=1)

  • Sort Key: p.pay_date, p.accr_start_date
  • Sort Method: quicksort Memory: 25kB
7. 0.000 5.357 ↓ 0.0 0 1

Nested Loop (cost=0.87..7,240.58 rows=1 width=78) (actual time=5.357..5.357 rows=0 loops=1)

  • Join Filter: (p.pss_id = d.pss_id)
8. 5.357 5.357 ↓ 0.0 0 1

Index Scan using srid_ap_index_test on abs_paydowns p (cost=0.44..7,238.97 rows=1 width=86) (actual time=5.357..5.357 rows=0 loops=1)

  • Index Cond: (security_id = 127822)
  • Filter: ((master_pss_id_quasi = 453136) AND ((((((paydown + principal_writedown) + interest) + CASE WHEN (adjusted_principal_balance IS NULL) THEN balance ELSE adjusted_principal_balance END) + accrued) <> 0::numeric) OR (cash_flow <> 0::double precision)))
  • Rows Removed by Filter: 22592
9. 0.000 0.000 ↓ 0.0 0

Index Only Scan using deals_master_pss_map_security_id_key on deals_master_pss_map d (cost=0.43..1.60 rows=1 width=16) (never executed)

  • Index Cond: ((security_id = 127822) AND (master_pss_id = 453136))
  • Filter: (pss_type = ANY ('{1,4,5,6,7,10,11,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179}'::integer[]))
  • Heap Fetches: 0
10. 0.012 1.969 ↓ 0.0 0 1

Sort (cost=7,328.21..7,328.21 rows=1 width=82) (actual time=1.969..1.969 rows=0 loops=1)

  • Sort Key: p_1.pay_date
  • Sort Method: quicksort Memory: 25kB
11. 0.001 1.957 ↓ 0.0 0 1

Nested Loop (cost=0.87..7,328.20 rows=1 width=82) (actual time=1.957..1.957 rows=0 loops=1)

  • Join Filter: (p_1.pss_id = d_1.pss_id)
12. 1.956 1.956 ↓ 0.0 0 1

Index Scan using srid_ap_index_test on abs_paydowns p_1 (cost=0.44..7,326.73 rows=1 width=86) (actual time=1.956..1.956 rows=0 loops=1)

  • Index Cond: (security_id = 224021)
  • Filter: ((master_pss_id_quasi = 448299) AND (((((paydown + principal_writedown) + interest) + balance) + accrued) <> 0::numeric) AND ((((((paydown + principal_writedown) + interest) + CASE WHEN (adjusted_principal_balance IS NULL) THEN balance ELSE adjusted_principal_balance END) + accrued) <> 0::numeric) OR (cash_flow <> 0::double precision)))
  • Rows Removed by Filter: 8256
13. 0.000 0.000 ↓ 0.0 0

Index Only Scan using deals_master_pss_map_security_id_key on deals_master_pss_map d_1 (cost=0.43..1.45 rows=1 width=16) (never executed)

  • Index Cond: ((security_id = 224021) AND (master_pss_id = 448299) AND (pss_type = 1))
  • Heap Fetches: 0
Total runtime : 7.855 ms