explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fiyB : long running mc abs_paydowns good

Settings
# exclusive inclusive rows x rows loops node
1. 0.030 6.432 ↓ 35.5 71 1

Sort (cost=14,957.27..14,957.28 rows=2 width=296) (actual time=6.428..6.432 rows=71 loops=1)

  • Sort Key: (14), p.pay_date
  • Sort Method: quicksort Memory: 34kB
2. 0.024 6.402 ↓ 35.5 71 1

Unique (cost=14,957.16..14,957.24 rows=2 width=189) (actual time=6.369..6.402 rows=71 loops=1)

3. 0.031 6.378 ↓ 35.5 71 1

Sort (cost=14,957.16..14,957.17 rows=2 width=189) (actual time=6.368..6.378 rows=71 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: 34kB
4. 0.002 6.347 ↓ 35.5 71 1

Append (cost=7,433.35..14,957.15 rows=2 width=189) (actual time=6.340..6.347 rows=71 loops=1)

5. 1.922 4.456 ↓ 0.0 0 1

GroupAggregate (cost=7,433.35..7,433.78 rows=1 width=78) (actual time=4.456..4.456 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)
  • Rows Removed by Filter: 108
6. 0.179 2.534 ↓ 382.0 382 1

Sort (cost=7,433.35..7,433.35 rows=1 width=78) (actual time=2.510..2.534 rows=382 loops=1)

  • Sort Key: p.pay_date, p.accr_start_date
  • Sort Method: quicksort Memory: 78kB
7. 0.243 2.355 ↓ 382.0 382 1

Nested Loop (cost=0.87..7,433.34 rows=1 width=78) (actual time=0.959..2.355 rows=382 loops=1)

  • Join Filter: (p.pss_id = d.pss_id)
  • Rows Removed by Join Filter: 1528
8. 1.348 1.348 ↓ 382.0 382 1

Index Scan using srid_ap_index_test on abs_paydowns p (cost=0.44..7,431.73 rows=1 width=86) (actual time=0.943..1.348 rows=382 loops=1)

  • Index Cond: (security_id = 362996)
  • Filter: ((master_pss_id_quasi = 489437) 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: 4596
9. 0.764 0.764 ↓ 5.0 5 382

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) (actual time=0.001..0.002 rows=5 loops=382)

  • Index Cond: ((security_id = 362996) AND (master_pss_id = 489437))
  • 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.035 1.889 ↓ 71.0 71 1

Sort (cost=7,523.33..7,523.33 rows=1 width=82) (actual time=1.883..1.889 rows=71 loops=1)

  • Sort Key: p_1.pay_date
  • Sort Method: quicksort Memory: 34kB
11. 0.237 1.854 ↓ 71.0 71 1

Nested Loop (cost=0.87..7,523.32 rows=1 width=82) (actual time=0.695..1.854 rows=71 loops=1)

  • Join Filter: (p_1.pss_id = d_1.pss_id)
  • Rows Removed by Join Filter: 311
12. 1.235 1.235 ↓ 382.0 382 1

Index Scan using srid_ap_index_test on abs_paydowns p_1 (cost=0.44..7,521.85 rows=1 width=86) (actual time=0.690..1.235 rows=382 loops=1)

  • Index Cond: (security_id = 362996)
  • Filter: ((master_pss_id_quasi = 489437) 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: 4596
13. 0.382 0.382 ↑ 1.0 1 382

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) (actual time=0.001..0.001 rows=1 loops=382)

  • Index Cond: ((security_id = 362996) AND (master_pss_id = 489437) AND (pss_type = 1))
  • Heap Fetches: 0
Total runtime : 6.742 ms