explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NxDV

Settings
# exclusive inclusive rows x rows loops node
1. 11,005.028 184,681.368 ↑ 806,379.5 4,111,759 1

Merge Right Join (cost=3,109,436.15..886,941,402,676.38 rows=3,315,638,316,502 width=2,216) (actual time=166,159.312..184,681.368 rows=4,111,759 loops=1)

  • Merge Cond: ((calls.sfid)::text = (leads.prospect__c)::text)
2.          

CTE leads

3. 1,453.909 2,888.350 ↓ 3.7 687,830 1

Hash Join (cost=9.20..234,798.25 rows=188,310 width=92) (actual time=0.073..2,888.350 rows=687,830 loops=1)

  • Hash Cond: ((sfpa.recordtypeid)::text = (sfrt.id)::text)
4. 1,434.405 1,434.405 ↑ 1.0 2,767,078 1

Seq Scan on sf_prospect_activity__c sfpa (cost=0.00..222,313.51 rows=2,824,651 width=111) (actual time=0.019..1,434.405 rows=2,767,078 loops=1)

5. 0.003 0.036 ↑ 1.0 1 1

Hash (cost=9.19..9.19 rows=1 width=19) (actual time=0.036..0.036 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
6. 0.033 0.033 ↑ 1.0 1 1

Seq Scan on sf_recordtype sfrt (cost=0.00..9.19 rows=1 width=19) (actual time=0.032..0.033 rows=1 loops=1)

  • Filter: ((namex)::text = 'Lead Received'::text)
  • Rows Removed by Filter: 14
7.          

CTE loaded

8. 5,383.632 5,383.632 ↓ 1.0 361,277 1

Seq Scan on sf_dialer_load_activity__c (cost=0.00..43,030.06 rows=356,282 width=279) (actual time=1.800..5,383.632 rows=361,277 loops=1)

  • Filter: (((delete_flag)::text <> 'Y'::text) AND ((direction__c)::text = 'Load'::text) AND ((success__c)::text = 'true'::text))
  • Rows Removed by Filter: 384104
9.          

CTE calls

10. 33,408.811 88,660.452 ↓ 1.0 1,978,621 1

Sort (cost=1,231,532.19..1,236,474.16 rows=1,976,787 width=178) (actual time=87,551.406..88,660.452 rows=1,978,621 loops=1)

  • Sort Key: mm_call_history.sfid, (row_number() OVER (?))
  • Sort Method: external sort Disk: 381320kB
11. 1,899.065 55,251.641 ↓ 1.0 1,978,621 1

WindowAgg (cost=633,932.66..673,468.40 rows=1,976,787 width=178) (actual time=45,566.190..55,251.641 rows=1,978,621 loops=1)

12. 51,817.706 53,352.576 ↓ 1.0 1,978,621 1

Sort (cost=633,932.66..638,874.63 rows=1,976,787 width=178) (actual time=45,566.183..53,352.576 rows=1,978,621 loops=1)

  • Sort Key: mm_call_history.sfid, mm_call_history.callplacedtimeutc
  • Sort Method: external merge Disk: 376088kB
13. 1,534.870 1,534.870 ↓ 1.0 1,978,621 1

Seq Scan on mm_call_history (cost=0.00..75,868.87 rows=1,976,787 width=178) (actual time=0.005..1,534.870 rows=1,978,621 loops=1)

14. 46,603.156 140,229.361 ↑ 1.0 1,966,836 1

Sort (cost=1,215,941.47..1,220,883.44 rows=1,976,787 width=510) (actual time=139,272.600..140,229.361 rows=1,966,836 loops=1)

  • Sort Key: calls.sfid
  • Sort Method: external sort Disk: 160480kB
15. 2,569.012 93,626.205 ↓ 1.0 1,978,621 1

Hash Left Join (cost=5.42..103,834.68 rows=1,976,787 width=510) (actual time=87,552.520..93,626.205 rows=1,978,621 loops=1)

  • Hash Cond: (((calls.wrapupcategory)::text = look.wrapup_category) AND ((calls.wrapupcode)::text = look.wrapup_code))
16. 91,056.117 91,056.117 ↓ 1.0 1,978,621 1

CTE Scan on calls (cost=0.00..39,535.74 rows=1,976,787 width=506) (actual time=87,551.409..91,056.117 rows=1,978,621 loops=1)

17. 0.049 1.076 ↓ 1.0 98 1

Hash (cost=3.97..3.97 rows=97 width=33) (actual time=1.076..1.076 rows=98 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
18. 1.027 1.027 ↓ 1.0 98 1

Seq Scan on lookup_call_dispos look (cost=0.00..3.97 rows=97 width=33) (actual time=0.617..1.027 rows=98 loops=1)

19. 1,211.480 33,446.979 ↑ 81.6 4,111,759 1

Materialize (cost=359,424.32..6,231,759.63 rows=335,457,317 width=1,760) (actual time=26,886.640..33,446.979 rows=4,111,759 loops=1)

20. 1,129.711 32,235.499 ↑ 414.5 809,236 1

Merge Left Join (cost=359,424.32..5,393,116.33 rows=335,457,317 width=1,760) (actual time=26,886.635..32,235.499 rows=809,236 loops=1)

  • Merge Cond: ((leads.prospect__c)::text = (loaded.prospect__c)::text)
21. 14,941.064 18,653.069 ↓ 3.7 687,830 1

Sort (cost=306,046.75..306,517.52 rows=188,310 width=1,752) (actual time=15,680.023..18,653.069 rows=687,830 loops=1)

  • Sort Key: leads.prospect__c
  • Sort Method: external merge Disk: 71072kB
22. 3,712.005 3,712.005 ↓ 3.7 687,830 1

CTE Scan on leads (cost=0.00..3,766.20 rows=188,310 width=1,752) (actual time=0.076..3,712.005 rows=687,830 loops=1)

23. 155.791 12,452.719 ↓ 1.0 361,277 1

Materialize (cost=53,377.58..55,158.99 rows=356,282 width=62) (actual time=11,206.605..12,452.719 rows=361,277 loops=1)

24. 6,430.340 12,296.928 ↓ 1.0 361,277 1

Sort (cost=53,377.58..54,268.28 rows=356,282 width=62) (actual time=11,206.602..12,296.928 rows=361,277 loops=1)

  • Sort Key: loaded.prospect__c
  • Sort Method: external merge Disk: 13040kB
25. 5,866.588 5,866.588 ↓ 1.0 361,277 1

CTE Scan on loaded (cost=0.00..7,125.64 rows=356,282 width=62) (actual time=1.803..5,866.588 rows=361,277 loops=1)

Planning time : 1.042 ms
Execution time : 185,922.528 ms