explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7G9p

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 1,459.880 ↑ 1.0 100 1

Limit (cost=15,854,998.15..15,854,998.40 rows=100 width=186) (actual time=1,459.858..1,459.880 rows=100 loops=1)

2. 36.619 1,459.864 ↑ 20.4 100 1

Sort (cost=15,854,998.15..15,855,003.24 rows=2,035 width=186) (actual time=1,459.857..1,459.864 rows=100 loops=1)

  • Sort Key: cs.start_time DESC, cs.id
  • Sort Method: top-N heapsort Memory: 53kB
3. 39.542 1,423.245 ↓ 15.1 30,802 1

Nested Loop Left Join (cost=7,782.18..15,854,920.38 rows=2,035 width=186) (actual time=0.200..1,423.245 rows=30,802 loops=1)

4. 13.802 1,260.495 ↓ 15.1 30,802 1

Nested Loop Left Join (cost=7,779.88..15,850,173.74 rows=2,035 width=214) (actual time=0.180..1,260.495 rows=30,802 loops=1)

  • Join Filter: (cs.is_internal AND (cs.foc_employee_id = 311710))
  • Rows Removed by Join Filter: 1198
5. 4.465 877.069 ↓ 15.1 30,802 1

Nested Loop Left Join (cost=7.32..32,973.65 rows=2,035 width=167) (actual time=0.152..877.069 rows=30,802 loops=1)

  • Join Filter: (cs.is_internal AND (cs.foc_employee_id <> 311710))
  • Rows Removed by Join Filter: 634
6. 19.165 811.000 ↓ 15.1 30,802 1

Nested Loop Left Join (cost=1.84..13,493.61 rows=2,035 width=108) (actual time=0.145..811.000 rows=30,802 loops=1)

  • Join Filter: (NOT cs.is_internal)
  • Rows Removed by Join Filter: 438
7. 268.201 268.201 ↓ 15.1 30,802 1

Index Scan using call_session__app_id__start_time_idx on call_session cs (cost=0.57..4,539.63 rows=2,035 width=82) (actual time=0.094..268.201 rows=30,802 loops=1)

  • Index Cond: ((app_id = 1103) AND (start_time > '2018-09-01 00:00:00'::timestamp without time zone) AND (start_time <= LOCALTIMESTAMP))
  • Filter: ((ROW(start_time, id) < ROW(LOCALTIMESTAMP, 2147483647)) AND (numa =~ ANY ('{NULL}'::text[])))
8. 30.802 523.634 ↑ 1.0 1 30,802

Limit (cost=1.27..4.38 rows=1 width=26) (actual time=0.016..0.017 rows=1 loops=30,802)

9. 34.838 492.832 ↑ 1.0 1 30,802

Nested Loop (cost=1.27..4.38 rows=1 width=26) (actual time=0.016..0.016 rows=1 loops=30,802)

10. 36.856 400.426 ↑ 1.0 1 30,802

Nested Loop (cost=0.85..3.92 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=30,802)

11. 277.218 277.218 ↑ 1.0 1 30,802

Index Scan using uni_phone_phone on phone p (cost=0.42..2.44 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=30,802)

  • Index Cond: ((app_id = 1103) AND (phone = cs.numa))
12. 86.352 86.352 ↑ 2.0 1 28,784

Index Only Scan using uni_phone_in_contact__phone_id__contact_id on phone_in_contact pc (cost=0.42..1.46 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=28,784)

  • Index Cond: (phone_id = p.id)
  • Heap Fetches: 30
13. 57.568 57.568 ↑ 1.0 1 28,784

Index Scan using contact_pkey on contact c (cost=0.42..0.46 rows=1 width=30) (actual time=0.002..0.002 rows=1 loops=28,784)

  • Index Cond: (id = pc.contact_id)
14. 30.802 61.604 ↓ 0.0 0 30,802

Limit (cost=5.48..9.55 rows=1 width=59) (actual time=0.001..0.002 rows=0 loops=30,802)

15. 0.000 30.802 ↓ 0.0 0 30,802

Nested Loop Left Join (cost=5.48..9.55 rows=1 width=59) (actual time=0.001..0.001 rows=0 loops=30,802)

16. 29.174 30.802 ↓ 0.0 0 30,802

Nested Loop Left Join (cost=0.71..4.76 rows=1 width=30) (actual time=0.001..0.001 rows=0 loops=30,802)

  • Join Filter: (e.id = sp.employee_id)
17. 0.000 0.000 ↓ 0.0 0 30,802

Index Scan using pkey_employee on employee e (cost=0.42..2.44 rows=1 width=27) (actual time=0.000..0.000 rows=0 loops=30,802)

  • Index Cond: (id = cs.foc_employee_id)
18. 1.628 1.628 ↓ 0.0 0 814

Index Scan using uni_short_phone__employee_id on short_phone sp (cost=0.29..2.31 rows=1 width=7) (actual time=0.002..0.002 rows=0 loops=814)

  • Index Cond: (employee_id = cs.foc_employee_id)
19. 0.000 8.954 ↑ 1.0 1 814

Limit (cost=4.77..4.77 rows=1 width=15) (actual time=0.011..0.011 rows=1 loops=814)

20. 2.442 8.954 ↑ 1.0 1 814

Sort (cost=4.77..4.77 rows=1 width=15) (actual time=0.011..0.011 rows=1 loops=814)

  • Sort Key: pe.priority
  • Sort Method: quicksort Memory: 25kB
21. 0.571 6.512 ↑ 1.0 1 814

Nested Loop (cost=0.71..4.76 rows=1 width=15) (actual time=0.007..0.008 rows=1 loops=814)

22. 3.256 3.256 ↑ 1.0 1 814

Index Scan using uni_phone_in_employee on phone_in_employee pe (cost=0.42..2.44 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=814)

  • Index Cond: (employee_id = e.id)
  • Filter: is_active
  • Rows Removed by Filter: 1
23. 0.895 2.685 ↑ 1.0 1 895

Append (cost=0.29..2.31 rows=1 width=15) (actual time=0.003..0.003 rows=1 loops=895)

24. 1.790 1.790 ↑ 1.0 1 895

Index Scan using pkey_phone on phone p_1 (cost=0.29..2.31 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=895)

  • Index Cond: (id = pe.phone_id)
  • Filter: (protocol = 'PSTN'::staff.phone_protocol)
25. 30.802 369.624 ↓ 0.0 0 30,802

Limit (cost=7,772.55..7,772.56 rows=1 width=67) (actual time=0.012..0.012 rows=0 loops=30,802)

26. 61.604 338.822 ↓ 0.0 0 30,802

Sort (cost=7,772.55..7,772.87 rows=128 width=67) (actual time=0.011..0.011 rows=0 loops=30,802)

  • Sort Key: c_1.start_time
  • Sort Method: quicksort Memory: 25kB
27. 9.733 277.218 ↓ 0.0 0 30,802

Nested Loop Left Join (cost=6,899.89..7,771.91 rows=128 width=67) (actual time=0.009..0.009 rows=0 loops=30,802)

28. 0.000 154.010 ↓ 0.0 0 30,802

Nested Loop Left Join (cost=1.28..871.70 rows=128 width=34) (actual time=0.005..0.005 rows=0 loops=30,802)

29. 29.467 154.010 ↓ 0.0 0 30,802

Nested Loop Left Join (cost=0.99..713.43 rows=128 width=35) (actual time=0.005..0.005 rows=0 loops=30,802)

30. 123.208 123.208 ↓ 0.0 0 30,802

Index Scan using cdr__call_session_id_idx on cdr c_1 (cost=0.57..410.43 rows=128 width=12) (actual time=0.004..0.004 rows=0 loops=30,802)

  • Index Cond: (call_session_id = cs.id)
  • Filter: (cdr_type = 'out_call'::cdr_type_mnemonic)
  • Rows Removed by Filter: 1
31. 1.335 1.335 ↓ 0.0 0 1,335

Index Scan using pkey_employee on employee e_1 (cost=0.42..2.37 rows=1 width=27) (actual time=0.001..0.001 rows=0 loops=1,335)

  • Index Cond: (id = c_1.employee_id)
32. 0.000 0.000 ↓ 0.0 0 1,335

Index Scan using uni_short_phone__employee_id on short_phone sp_1 (cost=0.29..1.24 rows=1 width=7) (actual time=0.000..0.000 rows=0 loops=1,335)

  • Index Cond: (employee_id = e_1.id)
33. 0.182 113.475 ↑ 1.0 1 1,335

Materialize (cost=6,898.60..6,898.62 rows=1 width=11) (actual time=0.085..0.085 rows=1 loops=1,335)

34. 0.002 113.293 ↑ 1.0 1 1

Subquery Scan on pstn (cost=6,898.60..6,898.61 rows=1 width=11) (actual time=113.243..113.293 rows=1 loops=1)

35. 0.004 113.291 ↑ 1.0 1 1

Limit (cost=6,898.60..6,898.60 rows=1 width=15) (actual time=113.242..113.291 rows=1 loops=1)

36. 16.430 113.287 ↑ 110,554.0 1 1

Sort (cost=6,898.60..7,174.99 rows=110,554 width=15) (actual time=113.242..113.287 rows=1 loops=1)

  • Sort Key: pe_1.priority
  • Sort Method: top-N heapsort Memory: 25kB
37. 32.759 96.857 ↑ 1.2 95,330 1

Hash Join (cost=2,796.25..6,345.83 rows=110,554 width=15) (actual time=42.853..96.857 rows=95,330 loops=1)

  • Hash Cond: (pe_1.phone_id = p_2.id)
38. 21.753 21.753 ↑ 1.0 118,465 1

Seq Scan on phone_in_employee pe_1 (cost=0.00..1,998.96 rows=118,688 width=8) (actual time=0.008..21.753 rows=118,465 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 2703
39. 16.281 42.345 ↓ 1.0 81,348 1

Hash (cost=1,780.18..1,780.18 rows=81,286 width=15) (actual time=42.299..42.345 rows=81,348 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4845kB
40. 8.480 26.064 ↓ 1.0 81,348 1

Append (cost=0.00..1,780.18 rows=81,286 width=15) (actual time=0.006..26.064 rows=81,348 loops=1)

41. 17.584 17.584 ↓ 1.0 81,348 1

Seq Scan on phone p_2 (cost=0.00..1,780.18 rows=81,286 width=15) (actual time=0.005..17.584 rows=81,348 loops=1)

  • Filter: (protocol = 'PSTN'::staff.phone_protocol)
  • Rows Removed by Filter: 5
42. 30.802 123.208 ↓ 0.0 0 30,802

Limit (cost=2.30..2.31 rows=1 width=39) (actual time=0.004..0.004 rows=0 loops=30,802)

43. 30.802 92.406 ↓ 0.0 0 30,802

Sort (cost=2.30..2.31 rows=1 width=39) (actual time=0.003..0.003 rows=0 loops=30,802)

  • Sort Key: csc.date_time DESC
  • Sort Method: quicksort Memory: 25kB
44. 61.604 61.604 ↓ 0.0 0 30,802

Index Scan using fki_call_session_comment__call_session_id on call_session_comment csc (cost=0.28..2.29 rows=1 width=39) (actual time=0.002..0.002 rows=0 loops=30,802)

  • Index Cond: (call_session_id = cs.id)