explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S3GI : new query kavya left join

Settings
# exclusive inclusive rows x rows loops node
1. 11.862 559.959 ↓ 116.5 9,202 1

Unique (cost=11,022.39..11,025.35 rows=79 width=1,787) (actual time=539.990..559.959 rows=9,202 loops=1)

2. 146.795 548.097 ↓ 345.5 27,296 1

Sort (cost=11,022.39..11,022.59 rows=79 width=1,787) (actual time=539.989..548.097 rows=27,296 loops=1)

  • Sort Key: u.id, u.sales_email_pref, u.login, u.first_name, u.last_name, u.email, u.lang_key, (COALESCE(np.restaurantunit_id, np_1.restaurantunit_id, np_2.restaurantunit_id)), (COALESCE(c.id, c_1.id, c_2.id)), (COALESCE(c.name, c_1.name, c_2.name)), (COALESCE(ru.name, ru_1.name, ru_2.name)), (COALESCE(ru.tenant_id, ru_1.tenant_id, ru_2.tenant_id)), ((date_part('hour'::text, timezone((ru.unit_tz)::text, CURRENT_TIMESTAMP)) >= (ru.sales_email_hour)::double precision)), (date_part('doy'::text, timezone((ru.unit_tz)::text, CURRENT_TIMESTAMP)))
  • Sort Method: external merge Disk: 4104kB
3. 0.000 401.302 ↓ 345.5 27,296 1

Gather (cost=2,365.72..11,019.90 rows=79 width=1,787) (actual time=68.633..401.302 rows=27,296 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
4. 72.056 422.574 ↓ 296.7 13,648 2

Hash Left Join (cost=1,365.72..10,011.34 rows=46 width=1,787) (actual time=68.658..422.574 rows=13,648 loops=2)

  • Hash Cond: (((ua.authority_name)::text = (npr_1.authority_name)::text) AND (ua.unit_id = np_1.restaurantunit_id))
  • Filter: ((u.id = npu.user_id) OR (((ua.authority_name)::text = (npr_1.authority_name)::text) AND (ua.unit_id = np_1.restaurantunit_id)) OR (((ua.authority_name)::text = ANY ('{ROLE_UNIT_MANAGER,ROLE_UNIT_ADMIN,ROLE_USER,ROLE_ACCOUNTANT}'::text[])) AND ((npa_2.actors)::text = 'ALL_USERS'::text) AND (ua.unit_id = np_2.restaurantunit_id) AND u.activated AND (u.retired_date IS NULL)))
  • Rows Removed by Filter: 170370
5. 120.859 336.068 ↑ 1.1 184,012 2

Hash Left Join (cost=1,009.58..8,093.21 rows=208,106 width=2,291) (actual time=54.133..336.068 rows=184,012 loops=2)

  • Hash Cond: (ua.unit_id = np_2.restaurantunit_id)
  • Join Filter: (u.activated AND (u.retired_date IS NULL) AND ((ua.authority_name)::text = ANY ('{ROLE_UNIT_MANAGER,ROLE_UNIT_ADMIN,ROLE_USER,ROLE_ACCOUNTANT}'::text[])))
  • Rows Removed by Join Filter: 286842
6. 49.108 191.494 ↑ 1.2 177,593 2

Hash Left Join (cost=688.25..6,960.27 rows=208,106 width=1,545) (actual time=30.403..191.494 rows=177,593 loops=2)

  • Hash Cond: (u.id = npu.user_id)
7. 79.159 120.405 ↑ 1.2 176,890 2

Hash Join (cost=332.12..5,822.92 rows=208,106 width=787) (actual time=8.395..120.405 rows=176,890 loops=2)

  • Hash Cond: (ua.user_id = u.id)
8. 33.043 33.043 ↑ 1.2 176,890 2

Parallel Seq Scan on t_user_authority ua (cost=0.00..4,944.06 rows=208,106 width=134) (actual time=0.014..33.043 rows=176,890 loops=2)

9. 3.074 8.203 ↑ 1.0 5,072 2

Hash (cost=268.72..268.72 rows=5,072 width=661) (actual time=8.203..8.203 rows=5,072 loops=2)

  • Buckets: 8192 Batches: 1 Memory Usage: 556kB
10. 5.129 5.129 ↑ 1.0 5,072 2

Seq Scan on t_user u (cost=0.00..268.72 rows=5,072 width=661) (actual time=0.021..5.129 rows=5,072 loops=2)

11. 0.084 21.981 ↓ 95.0 190 2

Hash (cost=356.11..356.11 rows=2 width=758) (actual time=21.981..21.981 rows=190 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
12. 0.256 21.897 ↓ 95.0 190 2

Hash Join (cost=318.62..356.11 rows=2 width=758) (actual time=21.437..21.897 rows=190 loops=2)

  • Hash Cond: (c.id = ru.concept_id)
13. 0.261 0.261 ↑ 1.0 1,416 2

Seq Scan on t_concept c (cost=0.00..32.16 rows=1,416 width=26) (actual time=0.030..0.261 rows=1,416 loops=2)

14. 0.046 21.380 ↓ 95.0 95 2

Hash (cost=318.60..318.60 rows=1 width=740) (actual time=21.380..21.380 rows=95 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
15. 0.351 21.334 ↓ 95.0 95 2

Nested Loop (cost=13.61..318.60 rows=1 width=740) (actual time=13.465..21.334 rows=95 loops=2)

16. 5.646 20.980 ↓ 106.0 106 2

Nested Loop (cost=13.33..308.52 rows=1 width=16) (actual time=13.428..20.980 rows=106 loops=2)

17. 5.944 15.332 ↓ 1,477.0 1,477 2

Nested Loop Left Join (cost=9.03..299.95 rows=1 width=16) (actual time=0.301..15.332 rows=1,477 loops=2)

18. 7.265 9.386 ↓ 1,475.0 1,475 2

Nested Loop Left Join (cost=4.51..256.33 rows=1 width=16) (actual time=0.261..9.386 rows=1,475 loops=2)

19. 2.118 2.118 ↓ 1,475.0 1,475 2

Seq Scan on t_notification_preferences np (cost=0.00..212.71 rows=1 width=16) (actual time=0.219..2.118 rows=1,475 loops=2)

  • Filter: ((retired_date IS NULL) AND ((notification_type)::text = 'SALES'::text))
  • Rows Removed by Filter: 7702
20. 0.001 0.003 ↑ 30.0 1 2,950

Bitmap Heap Scan on t_notification_preferences_actors npa (cost=4.51..43.32 rows=30 width=8) (actual time=0.003..0.003 rows=1 loops=2,950)

  • Recheck Cond: (notificationpreferences_id = np.id)
  • Heap Blocks: exact=1441
21. 0.002 0.002 ↑ 30.0 1 2,950

Bitmap Index Scan on t_notification_preferences_actors_pkey (cost=0.00..4.51 rows=30 width=0) (actual time=0.002..0.002 rows=1 loops=2,950)

  • Index Cond: (notificationpreferences_id = np.id)
22. 0.000 0.002 ↓ 0.0 0 2,950

Bitmap Heap Scan on t_notification_preferences_roles npr (cost=4.51..43.32 rows=30 width=8) (actual time=0.002..0.002 rows=0 loops=2,950)

  • Recheck Cond: (notificationpreferences_id = np.id)
  • Heap Blocks: exact=5
23. 0.002 0.002 ↓ 0.0 0 2,950

Bitmap Index Scan on t_notification_preferences_roles_pkey (cost=0.00..4.51 rows=30 width=0) (actual time=0.002..0.002 rows=0 loops=2,950)

  • Index Cond: (notificationpreferences_id = np.id)
24. 0.001 0.002 ↓ 0.0 0 2,954

Bitmap Heap Scan on t_notification_preferences_user npu (cost=4.30..8.54 rows=3 width=16) (actual time=0.002..0.002 rows=0 loops=2,954)

  • Recheck Cond: (notificationpreferences_id = np.id)
  • Heap Blocks: exact=35
25. 0.001 0.001 ↓ 0.0 0 2,954

Bitmap Index Scan on t_notification_preferences_user_pkey (cost=0.00..4.30 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=2,954)

  • Index Cond: (notificationpreferences_id = np.id)
26. 0.003 0.003 ↑ 1.0 1 212

Index Scan using pk_t_restaurantunit on t_restaurantunit ru (cost=0.28..8.30 rows=1 width=732) (actual time=0.003..0.003 rows=1 loops=212)

  • Index Cond: (id = np.restaurantunit_id)
  • Filter: ((retired_date IS NULL) AND (((unit_status)::text <> ALL ('{CLOSED,FIRED,FAILED_TO_LAUNCH}'::text[])) OR (unit_status IS NULL)))
  • Rows Removed by Filter: 0
27. 1.198 23.715 ↓ 1,254.0 2,508 2

Hash (cost=321.31..321.31 rows=2 width=746) (actual time=23.715..23.715 rows=2,508 loops=2)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 327kB
28. 1.077 22.517 ↓ 1,254.0 2,508 2

Hash Join (cost=283.82..321.31 rows=2 width=746) (actual time=21.208..22.517 rows=2,508 loops=2)

  • Hash Cond: (c_2.id = ru_2.concept_id)
29. 0.257 0.257 ↑ 1.0 1,416 2

Seq Scan on t_concept c_2 (cost=0.00..32.16 rows=1,416 width=26) (actual time=0.005..0.257 rows=1,416 loops=2)

30. 0.671 21.183 ↓ 1,254.0 1,254 2

Hash (cost=283.80..283.80 rows=1 width=728) (actual time=21.183..21.183 rows=1,254 loops=2)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 143kB
31. 4.290 20.512 ↓ 1,254.0 1,254 2

Nested Loop (cost=9.37..283.80 rows=1 width=728) (actual time=0.161..20.512 rows=1,254 loops=2)

32. 5.415 16.220 ↓ 1,441.0 1,441 2

Nested Loop Left Join (cost=9.10..273.72 rows=1 width=126) (actual time=0.150..16.220 rows=1,441 loops=2)

33. 5.836 10.803 ↓ 1,441.0 1,441 2

Nested Loop Left Join (cost=4.80..265.15 rows=1 width=134) (actual time=0.141..10.803 rows=1,441 loops=2)

34. 3.611 4.965 ↓ 1,441.0 1,441 2

Nested Loop (cost=0.28..221.53 rows=1 width=134) (actual time=0.135..4.965 rows=1,441 loops=2)

35. 1.352 1.352 ↓ 1,475.0 1,475 2

Seq Scan on t_notification_preferences np_2 (cost=0.00..212.71 rows=1 width=16) (actual time=0.105..1.352 rows=1,475 loops=2)

  • Filter: ((retired_date IS NULL) AND ((notification_type)::text = 'SALES'::text))
  • Rows Removed by Filter: 7702
36. 0.002 0.002 ↑ 1.0 1 2,950

Index Only Scan using t_notification_preferences_actors_pkey on t_notification_preferences_actors npa_2 (cost=0.28..8.30 rows=1 width=126) (actual time=0.002..0.002 rows=1 loops=2,950)

  • Index Cond: ((notificationpreferences_id = np_2.id) AND (actors = 'ALL_USERS'::text))
  • Heap Fetches: 1441
37. 0.001 0.002 ↓ 0.0 0 2,882

Bitmap Heap Scan on t_notification_preferences_roles npr_2 (cost=4.51..43.32 rows=30 width=8) (actual time=0.002..0.002 rows=0 loops=2,882)

  • Recheck Cond: (notificationpreferences_id = np_2.id)
  • Heap Blocks: exact=1
38. 0.001 0.001 ↓ 0.0 0 2,882

Bitmap Index Scan on t_notification_preferences_roles_pkey (cost=0.00..4.51 rows=30 width=0) (actual time=0.001..0.001 rows=0 loops=2,882)

  • Index Cond: (notificationpreferences_id = np_2.id)
39. 0.001 0.002 ↓ 0.0 0 2,882

Bitmap Heap Scan on t_notification_preferences_user npu_2 (cost=4.30..8.54 rows=3 width=8) (actual time=0.002..0.002 rows=0 loops=2,882)

  • Recheck Cond: (notificationpreferences_id = np_2.id)
  • Heap Blocks: exact=2
40. 0.001 0.001 ↓ 0.0 0 2,882

Bitmap Index Scan on t_notification_preferences_user_pkey (cost=0.00..4.30 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=2,882)

  • Index Cond: (notificationpreferences_id = np_2.id)
41. 0.002 0.002 ↑ 1.0 1 2,882

Index Scan using pk_t_restaurantunit on t_restaurantunit ru_2 (cost=0.28..8.30 rows=1 width=610) (actual time=0.002..0.002 rows=1 loops=2,882)

  • Index Cond: (id = np_2.restaurantunit_id)
  • Filter: ((retired_date IS NULL) AND (((unit_status)::text <> ALL ('{CLOSED,FIRED,FAILED_TO_LAUNCH}'::text[])) OR (unit_status IS NULL)))
  • Rows Removed by Filter: 0
42. 0.006 14.450 ↓ 4.0 8 2

Hash (cost=356.11..356.11 rows=2 width=746) (actual time=14.449..14.450 rows=8 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.209 14.444 ↓ 4.0 8 2

Hash Join (cost=318.62..356.11 rows=2 width=746) (actual time=14.131..14.444 rows=8 loops=2)

  • Hash Cond: (c_1.id = ru_1.concept_id)
44. 0.181 0.181 ↑ 1.0 1,416 2

Seq Scan on t_concept c_1 (cost=0.00..32.16 rows=1,416 width=26) (actual time=0.007..0.181 rows=1,416 loops=2)

45. 0.006 14.054 ↓ 4.0 4 2

Hash (cost=318.60..318.60 rows=1 width=728) (actual time=14.054..14.054 rows=4 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
46. 0.032 14.048 ↓ 4.0 4 2

Nested Loop (cost=13.61..318.60 rows=1 width=728) (actual time=9.605..14.048 rows=4 loops=2)

47. 0.034 14.012 ↓ 7.0 7 2

Nested Loop Left Join (cost=13.33..308.52 rows=1 width=126) (actual time=9.591..14.012 rows=7 loops=2)

48. 5.416 13.975 ↓ 7.0 7 2

Nested Loop (cost=9.03..299.95 rows=1 width=134) (actual time=9.582..13.975 rows=7 loops=2)

49. 6.824 8.557 ↓ 1,475.0 1,475 2

Nested Loop Left Join (cost=4.51..256.33 rows=1 width=16) (actual time=0.155..8.557 rows=1,475 loops=2)

50. 1.730 1.730 ↓ 1,475.0 1,475 2

Seq Scan on t_notification_preferences np_1 (cost=0.00..212.71 rows=1 width=16) (actual time=0.140..1.730 rows=1,475 loops=2)

  • Filter: ((retired_date IS NULL) AND ((notification_type)::text = 'SALES'::text))
  • Rows Removed by Filter: 7702
51. 0.001 0.003 ↑ 30.0 1 2,950

Bitmap Heap Scan on t_notification_preferences_actors npa_1 (cost=4.51..43.32 rows=30 width=8) (actual time=0.002..0.003 rows=1 loops=2,950)

  • Recheck Cond: (notificationpreferences_id = np_1.id)
  • Heap Blocks: exact=1441
52. 0.002 0.002 ↑ 30.0 1 2,950

Bitmap Index Scan on t_notification_preferences_actors_pkey (cost=0.00..4.51 rows=30 width=0) (actual time=0.002..0.002 rows=1 loops=2,950)

  • Index Cond: (notificationpreferences_id = np_1.id)
53. 0.001 0.002 ↓ 0.0 0 2,950

Bitmap Heap Scan on t_notification_preferences_roles npr_1 (cost=4.51..43.32 rows=30 width=126) (actual time=0.002..0.002 rows=0 loops=2,950)

  • Recheck Cond: (notificationpreferences_id = np_1.id)
  • Heap Blocks: exact=5
54. 0.001 0.001 ↓ 0.0 0 2,950

Bitmap Index Scan on t_notification_preferences_roles_pkey (cost=0.00..4.51 rows=30 width=0) (actual time=0.001..0.001 rows=0 loops=2,950)

  • Index Cond: (notificationpreferences_id = np_1.id)
55. 0.001 0.003 ↓ 0.0 0 14

Bitmap Heap Scan on t_notification_preferences_user npu_1 (cost=4.30..8.54 rows=3 width=8) (actual time=0.003..0.003 rows=0 loops=14)

  • Recheck Cond: (notificationpreferences_id = np_1.id)
  • Heap Blocks: exact=1
56. 0.002 0.002 ↓ 0.0 0 14

Bitmap Index Scan on t_notification_preferences_user_pkey (cost=0.00..4.30 rows=3 width=0) (actual time=0.002..0.002 rows=0 loops=14)

  • Index Cond: (notificationpreferences_id = np_1.id)
57. 0.004 0.004 ↑ 1.0 1 14

Index Scan using pk_t_restaurantunit on t_restaurantunit ru_1 (cost=0.28..8.30 rows=1 width=610) (actual time=0.004..0.004 rows=1 loops=14)

  • Index Cond: (id = np_1.restaurantunit_id)
  • Filter: ((retired_date IS NULL) AND (((unit_status)::text <> ALL ('{CLOSED,FIRED,FAILED_TO_LAUNCH}'::text[])) OR (unit_status IS NULL)))
  • Rows Removed by Filter: 0
Planning time : 11.384 ms
Execution time : 574.970 ms