explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QFja

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 111,652.108 ↓ 12.5 25 1

Limit (cost=1,107,926.66..1,107,926.67 rows=2 width=4,574) (actual time=111,652.106..111,652.108 rows=25 loops=1)

2. 1.519 111,652.106 ↓ 12.5 25 1

Sort (cost=1,107,926.66..1,107,926.67 rows=2 width=4,574) (actual time=111,652.105..111,652.106 rows=25 loops=1)

  • Sort Key: notification_view.notification_date_time DESC
  • Sort Method: top-N heapsort Memory: 30kB
3. 0.212 111,650.587 ↓ 2,445.0 4,890 1

Append (cost=1,107,905.41..1,107,926.63 rows=2 width=4,574) (actual time=111,646.243..111,650.587 rows=4,890 loops=1)

4. 3.925 111,650.351 ↓ 4,890.0 4,890 1

Subquery Scan on notification_view (cost=1,107,905.41..1,107,905.46 rows=1 width=4,896) (actual time=111,646.242..111,650.351 rows=4,890 loops=1)

5. 16.458 111,646.426 ↓ 4,890.0 4,890 1

Sort (cost=1,107,905.41..1,107,905.42 rows=1 width=4,804) (actual time=111,646.230..111,646.426 rows=4,890 loops=1)

  • Sort Key: (CASE WHEN (notification.action = 0) THEN LEAST((notification.interview_date - '24:00:00'::interval), (notification.insert_timestamp + '48:00:00'::interval)) ELSE notification.insert_timestamp END) DESC
  • Sort Method: quicksort Memory: 896kB
6. 1,770.154 111,629.968 ↓ 4,890.0 4,890 1

Nested Loop Left Join (cost=0.28..1,107,905.40 rows=1 width=4,804) (actual time=34.300..111,629.968 rows=4,890 loops=1)

  • Join Filter: (ua_created.id = notification.created_by_id)
  • Rows Removed by Join Filter: 25,193,280
7. 13,707.009 107,385.474 ↓ 4,890.0 4,890 1

Nested Loop Left Join (cost=0.28..1,107,579.47 rows=1 width=801) (actual time=33.631..107,385.474 rows=4,890 loops=1)

  • Join Filter: (cp1.id = pd1.company_id)
  • Rows Removed by Join Filter: 182,431,229
8. 3,206.324 78,979.125 ↓ 4,890.0 4,890 1

Nested Loop Left Join (cost=0.28..1,105,912.07 rows=1 width=781) (actual time=29.619..78,979.125 rows=4,890 loops=1)

  • Join Filter: (pd1.id = notification.position_description_id)
  • Rows Removed by Join Filter: 44,704,379
9. 2,946.714 71,303.341 ↓ 4,890.0 4,890 1

Nested Loop Left Join (cost=0.28..1,105,304.37 rows=1 width=747) (actual time=28.486..71,303.341 rows=4,890 loops=1)

  • Join Filter: (cd1.id = notification.candidate_id)
  • Rows Removed by Join Filter: 38,616,768
10. 70.995 61,779.577 ↓ 4,890.0 4,890 1

Nested Loop Left Join (cost=0.28..1,101,721.95 rows=1 width=727) (actual time=28.335..61,779.577 rows=4,890 loops=1)

  • Join Filter: (cs.id = notification.candidate_source_id)
  • Rows Removed by Join Filter: 72,936
11. 5,570.531 61,698.802 ↓ 4,890.0 4,890 1

Nested Loop Left Join (cost=0.28..1,101,720.44 rows=1 width=215) (actual time=28.318..61,698.802 rows=4,890 loops=1)

  • Join Filter: (pc.candidate_id = cd.id)
  • Rows Removed by Join Filter: 76,864,900
12. 14,724.165 42,367.811 ↓ 4,890.0 4,890 1

Nested Loop Left Join (cost=0.28..1,098,138.02 rows=1 width=195) (actual time=25.079..42,367.811 rows=4,890 loops=1)

  • Join Filter: (cp.id = pd.company_id)
  • Rows Removed by Join Filter: 182,395,779
  • Filter: (cp.deleted_timestamp IS NULL)
13. 3,468.111 21,325.766 ↓ 815.0 4,890 1

Nested Loop Left Join (cost=0.28..1,093,486.05 rows=6 width=175) (actual time=12.999..21,325.766 rows=4,890 loops=1)

  • Join Filter: (pc.position_description_id = pd.id)
  • Rows Removed by Join Filter: 44,512,250
14. 7,549.695 16,248.845 ↓ 815.0 4,890 1

Nested Loop Left Join (cost=0.28..1,092,147.00 rows=6 width=141) (actual time=8.341..16,248.845 rows=4,890 loops=1)

  • Join Filter: (ct.id = notification.contact_id)
  • Rows Removed by Join Filter: 105,491,821
  • Filter: (ct.deleted_timestamp IS NULL)
15. 20.793 4,972.970 ↓ 376.2 4,890 1

Nested Loop Left Join (cost=0.28..1,087,135.60 rows=13 width=128) (actual time=0.740..4,972.970 rows=4,890 loops=1)

  • Filter: ((pc.rejected_date IS NULL) AND (notification.status = COALESCE(pc.status, 0)))
  • Rows Removed by Filter: 11
16. 4,947.276 4,947.276 ↓ 1.0 4,901 1

Seq Scan on notification (cost=0.00..1,085,055.73 rows=4,838 width=92) (actual time=0.734..4,947.276 rows=4,901 loops=1)

  • Filter: ((user_account_id = 32,668) AND ((action > 0) OR ((action = 0) AND (((insert_timestamp + '48:00:00'::interval) < now()) OR ((interview_date IS NOT NULL) AND ((interview_date - '24:00:00'::interval) < now()))))))
  • Rows Removed by Filter: 25,404,747
17. 4.901 4.901 ↓ 0.0 0 4,901

Index Scan using position_candidate_pkey on position_candidate pc (cost=0.28..0.42 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=4,901)

  • Index Cond: (id = notification.position_candidate_id)
18. 3,722.639 3,726.180 ↑ 1.0 21,573 4,890

Materialize (cost=0.00..858.60 rows=21,573 width=25) (actual time=0.000..0.762 rows=21,573 loops=4,890)

19. 3.541 3.541 ↑ 1.0 21,573 1

Seq Scan on contact ct (cost=0.00..750.73 rows=21,573 width=25) (actual time=0.009..3.541 rows=21,573 loops=1)

20. 1,605.675 1,608.810 ↑ 1.0 9,103 4,890

Materialize (cost=0.00..539.13 rows=9,142 width=38) (actual time=0.000..0.329 rows=9,103 loops=4,890)

21. 3.135 3.135 ↑ 1.0 9,142 1

Seq Scan on position_description pd (cost=0.00..493.42 rows=9,142 width=38) (actual time=0.018..3.135 rows=9,142 loops=1)

22. 6,312.604 6,317.880 ↑ 1.0 37,300 4,890

Materialize (cost=0.00..1,387.61 rows=37,307 width=32) (actual time=0.000..1.292 rows=37,300 loops=4,890)

23. 5.276 5.276 ↑ 1.0 37,307 1

Seq Scan on company cp (cost=0.00..1,201.07 rows=37,307 width=32) (actual time=0.016..5.276 rows=37,307 loops=1)

24. 13,760.460 13,760.460 ↑ 1.0 15,719 4,890

Seq Scan on candidate cd (cost=0.00..3,381.63 rows=16,063 width=28) (actual time=0.004..2.814 rows=15,719 loops=4,890)

25. 9.780 9.780 ↑ 1.4 16 4,890

Seq Scan on candidate_source cs (cost=0.00..1.23 rows=23 width=520) (actual time=0.001..0.002 rows=16 loops=4,890)

26. 6,577.050 6,577.050 ↑ 2.0 7,898 4,890

Seq Scan on candidate cd1 (cost=0.00..3,381.63 rows=16,063 width=28) (actual time=0.001..1.345 rows=7,898 loops=4,890)

27. 4,469.460 4,469.460 ↑ 1.0 9,142 4,890

Seq Scan on position_description pd1 (cost=0.00..493.42 rows=9,142 width=38) (actual time=0.001..0.914 rows=9,142 loops=4,890)

28. 14,699.340 14,699.340 ↑ 1.0 37,307 4,890

Seq Scan on company cp1 (cost=0.00..1,201.07 rows=37,307 width=24) (actual time=0.002..3.006 rows=37,307 loops=4,890)

29. 2,474.340 2,474.340 ↑ 1.0 5,152 4,890

Seq Scan on user_account ua_created (cost=0.00..261.52 rows=5,152 width=31) (actual time=0.002..0.506 rows=5,152 loops=4,890)

30. 0.000 0.024 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=21.12..21.17 rows=1 width=856) (actual time=0.024..0.024 rows=0 loops=1)

31. 0.001 0.024 ↓ 0.0 0 1

Subquery Scan on notification_comment_view (cost=21.12..21.15 rows=1 width=856) (actual time=0.024..0.024 rows=0 loops=1)

32. 0.007 0.023 ↓ 0.0 0 1

Sort (cost=21.12..21.12 rows=1 width=701) (actual time=0.023..0.023 rows=0 loops=1)

  • Sort Key: tdn.insert_timestamp DESC
  • Sort Method: quicksort Memory: 25kB
33. 0.000 0.016 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.01..21.11 rows=1 width=701) (actual time=0.016..0.016 rows=0 loops=1)

34. 0.000 0.016 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.72..20.66 rows=1 width=203) (actual time=0.016..0.016 rows=0 loops=1)

  • Filter: (cp_1.deleted_timestamp IS NULL)
35. 0.000 0.016 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.43..20.02 rows=1 width=183) (actual time=0.016..0.016 rows=0 loops=1)

36. 0.000 0.016 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.14..19.54 rows=1 width=163) (actual time=0.016..0.016 rows=0 loops=1)

  • Filter: (ct_1.deleted_timestamp IS NULL)
37. 0.000 0.016 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.85..19.03 rows=1 width=146) (actual time=0.016..0.016 rows=0 loops=1)

38. 0.001 0.016 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.57..17.62 rows=1 width=128) (actual time=0.015..0.016 rows=0 loops=1)

39. 0.000 0.015 ↓ 0.0 0 1

Nested Loop (cost=0.29..9.32 rows=1 width=105) (actual time=0.015..0.015 rows=0 loops=1)

40. 0.015 0.015 ↓ 0.0 0 1

Seq Scan on activity_notifier tdn (cost=0.00..1.01 rows=1 width=22) (actual time=0.015..0.015 rows=0 loops=1)

  • Filter: (notifier_id = 32,668)
  • Rows Removed by Filter: 1
41. 0.000 0.000 ↓ 0.0 0

Index Scan using task_detail_pkey on activity (cost=0.29..8.30 rows=1 width=87) (never executed)

  • Index Cond: (id = tdn.activity_id)
42. 0.000 0.000 ↓ 0.0 0

Index Scan using client_account_pkey on user_account user_comment (cost=0.28..8.30 rows=1 width=31) (never executed)

  • Index Cond: (id = tdn.comment_by)
43. 0.000 0.000 ↓ 0.0 0

Index Scan using candidate_pkey on candidate cd_1 (cost=0.29..1.41 rows=1 width=26) (never executed)

  • Index Cond: (id = activity.candidate_id)
44. 0.000 0.000 ↓ 0.0 0

Index Scan using contact__pkey on contact ct_1 (cost=0.29..0.51 rows=1 width=29) (never executed)

  • Index Cond: (id = activity.contact_id)
45. 0.000 0.000 ↓ 0.0 0

Index Scan using company__pkey on company con_com (cost=0.29..0.48 rows=1 width=24) (never executed)

  • Index Cond: (id = ct_1.company_id)
46. 0.000 0.000 ↓ 0.0 0

Index Scan using company__pkey on company cp_1 (cost=0.29..0.62 rows=1 width=32) (never executed)

  • Index Cond: (id = activity.company_id)
47. 0.000 0.000 ↓ 0.0 0

Index Scan using position_description_pkey on position_description pd_1 (cost=0.29..0.45 rows=1 width=32) (never executed)

  • Index Cond: (id = activity.position_id)
Planning time : 8.214 ms
Execution time : 111,654.171 ms