explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wHcP

Settings
# exclusive inclusive rows x rows loops node
1. 124,423.609 184,620.517 ↓ 1.7 953,640 1

Sort (cost=435,767.30..437,131.54 rows=545,694 width=400) (actual time=156,003.502..184,620.517 rows=953,640 loops=1)

  • Sort Key: schedule.customer_id, schedule.period
  • Sort Method: external merge Disk: 1262216kB
2.          

CTE schedule

3. 24,525.334 53,639.475 ↓ 1.7 953,640 1

Recursive Union (cost=1,469.36..175,146.97 rows=545,694 width=400) (actual time=25.857..53,639.475 rows=953,640 loops=1)

4. 77.933 113.141 ↑ 1.0 15,894 1

Merge Left Join (cost=1,469.36..2,310.79 rows=15,894 width=320) (actual time=25.854..113.141 rows=15,894 loops=1)

  • Merge Cond: ((t2.balance_group = c.balance_group) AND (t2.loan_type = c.loan_type))
5. 18.992 31.316 ↑ 1.0 15,894 1

Sort (cost=1,425.04..1,464.77 rows=15,894 width=43) (actual time=25.775..31.316 rows=15,894 loops=1)

  • Sort Key: t2.balance_group, t2.loan_type
  • Sort Method: quicksort Memory: 2157kB
6. 12.324 12.324 ↑ 1.0 15,894 1

Seq Scan on cltv_customer_groups t2 (cost=0.00..315.94 rows=15,894 width=43) (actual time=0.008..12.324 rows=15,894 loops=1)

7. 3.878 3.892 ↓ 18.4 11,236 1

Sort (cost=44.32..45.85 rows=610 width=96) (actual time=0.042..3.892 rows=11,236 loops=1)

  • Sort Key: c.balance_group, c.loan_type
  • Sort Method: quicksort Memory: 25kB
8. 0.014 0.014 ↑ 50.8 12 1

Seq Scan on cltv_nar c (cost=0.00..16.10 rows=610 width=96) (actual time=0.010..0.014 rows=12 loops=1)

9. 27,806.251 29,001.000 ↑ 3.4 15,629 60

Hash Left Join (cost=25.25..16,192.23 rows=52,980 width=400) (actual time=0.302..483.350 rows=15,629 loops=60)

  • Hash Cond: ((s.balance_group = c_1.balance_group) AND (s.loan_type = c_1.loan_type))
10. 1,194.720 1,194.720 ↑ 3.4 15,629 60

WorkTable Scan on schedule s (cost=0.00..3,576.15 rows=52,980 width=272) (actual time=0.256..19.912 rows=15,629 loops=60)

  • Filter: (period < 60)
  • Rows Removed by Filter: 265
11. 0.008 0.029 ↑ 50.8 12 1

Hash (cost=16.10..16.10 rows=610 width=96) (actual time=0.029..0.029 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.021 0.021 ↑ 50.8 12 1

Seq Scan on cltv_nar c_1 (cost=0.00..16.10 rows=610 width=96) (actual time=0.017..0.021 rows=12 loops=1)

13. 60,196.908 60,196.908 ↓ 1.7 953,640 1

CTE Scan on schedule (cost=0.00..10,913.88 rows=545,694 width=400) (actual time=25.860..60,196.908 rows=953,640 loops=1)

Planning time : 145.593 ms
Execution time : 185,060.472 ms