explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pQx8 : long running mc abs_paydowns

Settings
# exclusive inclusive rows x rows loops node
1. 0.053 291.813 ↓ 118.0 236 1

Sort (cost=102,169.99..102,169.99 rows=2 width=296) (actual time=291.803..291.813 rows=236 loops=1)

  • Sort Key: (14), p.pay_date
  • Sort Method: quicksort Memory: 58kB
2. 0.093 291.760 ↓ 118.0 236 1

Unique (cost=102,169.88..102,169.96 rows=2 width=189) (actual time=291.663..291.760 rows=236 loops=1)

3. 0.043 291.667 ↓ 118.0 236 1

Sort (cost=102,169.88..102,169.88 rows=2 width=189) (actual time=291.663..291.667 rows=236 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: 58kB
4. 0.013 291.624 ↓ 118.0 236 1

Append (cost=50,764.15..102,169.87 rows=2 width=189) (actual time=291.601..291.624 rows=236 loops=1)

5. 25.478 278.689 ↓ 0.0 0 1

GroupAggregate (cost=50,764.15..50,764.58 rows=1 width=78) (actual time=278.689..278.689 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: 236
6. 2.504 253.211 ↓ 5,664.0 5,664 1

Sort (cost=50,764.15..50,764.15 rows=1 width=78) (actual time=252.890..253.211 rows=5,664 loops=1)

  • Sort Key: p.pay_date, p.accr_start_date
  • Sort Method: quicksort Memory: 989kB
7. 8.253 250.707 ↓ 5,664.0 5,664 1

Nested Loop (cost=0.87..50,764.14 rows=1 width=78) (actual time=0.034..250.707 rows=5,664 loops=1)

  • Join Filter: (p.pss_id = d.pss_id)
  • Rows Removed by Join Filter: 113280
8. 0.030 0.030 ↓ 24.0 24 1

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.014..0.030 rows=24 loops=1)

  • Index Cond: ((security_id = 200135) AND (master_pss_id = 425586))
  • 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
9. 242.424 242.424 ↓ 1,239.0 4,956 24

Index Scan using srid_ap_index_test on abs_paydowns p (cost=0.44..50,762.49 rows=4 width=86) (actual time=0.005..10.101 rows=4,956 loops=24)

  • Index Cond: (security_id = 200135)
  • Filter: ((master_pss_id_quasi = 425586) 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: 44917
10. 0.074 12.922 ↓ 236.0 236 1

Sort (cost=51,405.24..51,405.25 rows=1 width=82) (actual time=12.911..12.922 rows=236 loops=1)

  • Sort Key: p_1.pay_date
  • Sort Method: quicksort Memory: 58kB
11. 0.338 12.848 ↓ 236.0 236 1

Nested Loop (cost=0.87..51,405.23 rows=1 width=82) (actual time=0.021..12.848 rows=236 loops=1)

  • Join Filter: (p_1.pss_id = d_1.pss_id)
  • Rows Removed by Join Filter: 4720
12. 0.006 0.006 ↑ 1.0 1 1

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.006..0.006 rows=1 loops=1)

  • Index Cond: ((security_id = 200135) AND (master_pss_id = 425586) AND (pss_type = 1))
  • Heap Fetches: 0
13. 12.504 12.504 ↓ 1,239.0 4,956 1

Index Scan using srid_ap_index_test on abs_paydowns p_1 (cost=0.44..51,403.73 rows=4 width=86) (actual time=0.012..12.504 rows=4,956 loops=1)

  • Index Cond: (security_id = 200135)
  • Filter: ((master_pss_id_quasi = 425586) 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: 44917
Total runtime : 292.127 ms