explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YKKb : Different Join

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 408.319 ↓ 0.0 0 1

Limit (cost=2,007.29..14,291.32 rows=2 width=337) (actual time=408.319..408.319 rows=0 loops=1)

2.          

Initplan (for Limit)

3. 0.000 0.000 ↓ 0.0 0

Result (cost=0.80..0.81 rows=1 width=4) (never executed)

4.          

Initplan (for Result)

5. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.42..0.80 rows=1 width=4) (never executed)

6. 0.000 0.000 ↓ 0.0 0

Index Only Scan Backward using emails_pkey on emails emails_1 (cost=0.42..305,475.05 rows=812,233 width=4) (never executed)

  • Index Cond: (email_id IS NOT NULL)
  • Heap Fetches: 0
7. 0.000 0.000 ↓ 0.0 0

Result (cost=0.46..0.47 rows=1 width=4) (never executed)

8.          

Initplan (for Result)

9. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.42..0.46 rows=1 width=4) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Index Only Scan Backward using chats_pkey on chats (cost=0.42..18,081.74 rows=457,904 width=4) (never executed)

  • Index Cond: (chat_id IS NOT NULL)
  • Heap Fetches: 0
11. 0.000 0.000 ↓ 0.0 0

Result (cost=0.66..0.67 rows=1 width=4) (never executed)

12.          

Initplan (for Result)

13. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.42..0.66 rows=1 width=4) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Index Only Scan Backward using outbound_email_actions_pkey on outbound_email_actions outbound_email_actions_1 (cost=0.42..233,620.47 rows=984,533 width=4) (never executed)

  • Index Cond: (outbound_email_action_id IS NOT NULL)
  • Heap Fetches: 0
15. 0.002 408.318 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,005.33..14,289.37 rows=2 width=337) (actual time=408.317..408.318 rows=0 loops=1)

16. 0.000 408.316 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,004.77..14,256.02 rows=2 width=280) (actual time=408.316..408.316 rows=0 loops=1)

  • Join Filter: (users.user_id = "*SELECT* 1".user_id)
17. 0.001 408.316 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,004.77..14,226.19 rows=2 width=261) (actual time=408.315..408.316 rows=0 loops=1)

  • Join Filter: (handling_types.handling_type_id = "*SELECT* 1".handling_type_id)
18. 0.001 408.315 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,004.77..14,224.90 rows=2 width=229) (actual time=408.315..408.315 rows=0 loops=1)

  • Join Filter: (languages.language_id = "*SELECT* 1".language_id)
19. 0.001 408.314 ↓ 0.0 0 1

Nested Loop (cost=2,004.77..14,221.43 rows=2 width=221) (actual time=408.314..408.314 rows=0 loops=1)

  • Join Filter: ("*SELECT* 1".interaction_type_id = interaction_types.interaction_type_id)
20. 0.004 408.313 ↓ 0.0 0 1

Merge Append (cost=2,004.77..14,219.73 rows=23 width=189) (actual time=408.313..408.313 rows=0 loops=1)

  • Sort Key: "*SELECT* 1".date DESC
21. 0.001 254.827 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=1,002.14..7,178.46 rows=11 width=195) (actual time=254.827..254.827 rows=0 loops=1)

22. 0.000 254.826 ↓ 0.0 0 1

Limit (cost=1,002.14..7,178.35 rows=11 width=195) (actual time=254.825..254.826 rows=0 loops=1)

23. 3.672 254.974 ↓ 0.0 0 1

Gather Merge (cost=1,002.14..167,198.19 rows=296 width=195) (actual time=254.824..254.974 rows=0 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
24. 0.001 251.302 ↓ 0.0 0 2 / 2

Nested Loop Left Join (cost=2.13..166,164.88 rows=174 width=195) (actual time=251.302..251.302 rows=0 loops=2)

25. 0.001 251.301 ↓ 0.0 0 2 / 2

Nested Loop (cost=1.71..165,760.10 rows=166 width=649) (actual time=251.301..251.301 rows=0 loops=2)

26. 0.001 251.300 ↓ 0.0 0 2 / 2

Nested Loop (cost=1.28..165,683.68 rows=166 width=649) (actual time=251.300..251.300 rows=0 loops=2)

27. 46.521 251.299 ↓ 0.0 0 2 / 2

Nested Loop (cost=0.85..165,393.87 rows=166 width=649) (actual time=251.299..251.299 rows=0 loops=2)

28. 110.712 110.712 ↓ 1.4 47,033 2 / 2

Parallel Index Scan Backward using ix_emails_received on emails (cost=0.42..118,815.50 rows=33,321 width=626) (actual time=0.055..110.712 rows=47,033 loops=2)

  • Index Cond: ((received >= '2019-09-30 23:00:00+00'::timestamp with time zone) AND (received <= '2020-03-31 22:59:59.999+00'::timestamp with time zone))
  • Filter: (is_workable IS TRUE)
29. 94.066 94.066 ↓ 0.0 0 94,066 / 2

Index Scan using interaction_details_pkey on interaction_details (cost=0.43..1.40 rows=1 width=23) (actual time=0.002..0.002 rows=0 loops=94,066)

  • Index Cond: (interaction_detail_id = emails.interaction_detail_id)
  • Filter: (lower(booking_id) = 'de811638600'::text)
  • Rows Removed by Filter: 1
30. 0.000 0.000 ↓ 0.0 0 / 2

Index Scan using conversations_pkey on conversations (cost=0.43..1.75 rows=1 width=8) (never executed)

  • Index Cond: (conversation_id = emails.conversation_id)
31. 0.000 0.000 ↓ 0.0 0 / 2

Index Only Scan using queries_pkey on queries (cost=0.43..0.46 rows=1 width=4) (never executed)

  • Index Cond: (query_id = conversations.query_id)
  • Heap Fetches: 0
32. 0.000 0.000 ↓ 0.0 0 / 2

Index Scan using ix_handling_details_interaction_detail_id on handling_details (cost=0.43..2.42 rows=1 width=24) (never executed)

  • Index Cond: (interaction_detail_id = emails.interaction_detail_id)
  • Filter: (logic_type_id = 1)
33. 0.001 153.460 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=1,002.59..7,040.92 rows=11 width=181) (actual time=153.460..153.460 rows=0 loops=1)

34. 0.000 153.459 ↓ 0.0 0 1

Limit (cost=1,002.59..7,040.81 rows=11 width=181) (actual time=153.459..153.459 rows=0 loops=1)

35. 4.925 153.513 ↓ 0.0 0 1

Gather Merge (cost=1,002.59..119,022.40 rows=215 width=181) (actual time=153.456..153.513 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
36. 0.001 148.588 ↓ 0.0 0 3 / 3

Nested Loop (cost=2.57..117,997.55 rows=90 width=181) (actual time=148.588..148.588 rows=0 loops=3)

37. 0.001 148.587 ↓ 0.0 0 3 / 3

Nested Loop (cost=2.14..117,938.33 rows=90 width=730) (actual time=148.587..148.587 rows=0 loops=3)

38. 0.001 148.586 ↓ 0.0 0 3 / 3

Nested Loop (cost=1.71..117,863.21 rows=90 width=730) (actual time=148.586..148.586 rows=0 loops=3)

39. 0.001 148.585 ↓ 0.0 0 3 / 3

Nested Loop (cost=1.28..117,666.64 rows=204 width=70) (actual time=148.585..148.585 rows=0 loops=3)

40. 30.789 148.584 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.85..117,355.91 rows=132 width=51) (actual time=148.584..148.584 rows=0 loops=3)

41. 49.613 49.613 ↑ 1.2 22,727 3 / 3

Parallel Index Scan Backward using ix_handling_details_agent_finish_time on handling_details handling_details_1 (cost=0.43..77,966.29 rows=26,398 width=32) (actual time=0.048..49.613 rows=22,727 loops=3)

  • Index Cond: ((agent_finish_time >= '2019-09-30 23:00:00+00'::timestamp with time zone) AND (agent_finish_time <= '2020-03-31 22:59:59.999+00'::timestamp with time zone))
42. 68.182 68.182 ↓ 0.0 0 68,182 / 3

Index Scan using interaction_details_pkey on interaction_details interaction_details_1 (cost=0.43..1.49 rows=1 width=23) (actual time=0.003..0.003 rows=0 loops=68,182)

  • Index Cond: (interaction_detail_id = handling_details_1.interaction_detail_id)
  • Filter: (lower(booking_id) = 'de811638600'::text)
  • Rows Removed by Filter: 1
43. 0.000 0.000 ↓ 0.0 0 / 3

Index Scan using ix_actions_handling_detail_id on actions (cost=0.43..2.33 rows=2 width=27) (never executed)

  • Index Cond: (handling_detail_id = handling_details_1.handling_detail_id)
44. 0.000 0.000 ↓ 0.0 0 / 3

Index Scan using ix_outbound_email_actions_action_id on outbound_email_actions (cost=0.42..0.96 rows=1 width=668) (never executed)

  • Index Cond: (action_id = actions.action_id)
45. 0.000 0.000 ↓ 0.0 0 / 3

Index Scan using conversations_pkey on conversations conversations_1 (cost=0.43..0.83 rows=1 width=8) (never executed)

  • Index Cond: (conversation_id = outbound_email_actions.conversation_id)
46. 0.000 0.000 ↓ 0.0 0 / 3

Index Only Scan using queries_pkey on queries queries_1 (cost=0.43..0.65 rows=1 width=4) (never executed)

  • Index Cond: (query_id = conversations_1.query_id)
  • Heap Fetches: 0
47. 0.001 0.022 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=0.01..0.03 rows=1 width=217) (actual time=0.022..0.022 rows=0 loops=1)

48. 0.001 0.021 ↓ 0.0 0 1

Limit (cost=0.01..0.02 rows=1 width=217) (actual time=0.021..0.021 rows=0 loops=1)

49. 0.018 0.020 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=0 width=217) (actual time=0.019..0.020 rows=0 loops=1)

  • Sort Key: "timestamp" DESC
  • Sort Method: quicksort Memory: 25kB
50. 0.002 0.002 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=217) (actual time=0.001..0.002 rows=0 loops=1)

  • One-Time Filter: false
51. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.03 rows=2 width=36) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Seq Scan on interaction_types (cost=0.00..1.02 rows=2 width=36) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.87 rows=58 width=12) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Seq Scan on languages (cost=0.00..1.58 rows=58 width=12) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.10 rows=7 width=36) (never executed)

56. 0.000 0.000 ↓ 0.0 0

Seq Scan on handling_types (cost=0.00..1.07 rows=7 width=36) (never executed)

57. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..15.06 rows=537 width=19) (never executed)

58. 0.000 0.000 ↓ 0.0 0

Seq Scan on users (cost=0.00..12.37 rows=537 width=19) (never executed)

59. 0.000 0.000 ↓ 0.0 0

Subquery Scan on anon_1 (cost=0.56..16.66 rows=1 width=36) (never executed)

  • Filter: (anon_1.email_id = "*SELECT* 1".email_id)
60. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.56..16.65 rows=1 width=36) (never executed)

  • Group Key: email_user_assignments.email_id
61. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..16.63 rows=1 width=19) (never executed)

62. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_email_user_assignments_email_id on email_user_assignments (cost=0.29..8.31 rows=1 width=8) (never executed)

  • Index Cond: (email_id = "*SELECT* 1".email_id)
63. 0.000 0.000 ↓ 0.0 0

Index Scan using users_pkey on users users_1 (cost=0.28..8.29 rows=1 width=19) (never executed)

  • Index Cond: (user_id = email_user_assignments.user_id)
Planning time : 12.249 ms
Execution time : 408.972 ms