explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZVXm

Settings
# exclusive inclusive rows x rows loops node
1. 12.074 859.339 ↓ 118.0 9,202 1

Unique (cost=16,448.13..16,451.06 rows=78 width=1,191) (actual time=841.664..859.339 rows=9,202 loops=1)

2. 65.432 847.265 ↓ 174.6 13,622 1

Sort (cost=16,448.13..16,448.33 rows=78 width=1,191) (actual time=841.657..847.265 rows=13,622 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, CURR
  • Sort Method: quicksort Memory: 3927kB
3. 16.840 781.833 ↓ 174.6 13,622 1

Gather (cost=3,891.96..16,445.68 rows=78 width=1,191) (actual time=107.400..781.833 rows=13,622 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 144.971 764.993 ↓ 141.9 4,541 3

Hash Left Join (cost=2,891.96..15,436.96 rows=32 width=1,191) (actual time=89.491..764.993 rows=4,541 loops=3)

  • 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: 115029
5. 217.140 597.703 ↑ 1.2 119,569 3

Hash Left Join (cost=2,069.90..10,486.65 rows=149,131 width=276) (actual time=66.922..597.703 rows=119,569 loops=3)

  • 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: 96868
6. 226.626 343.693 ↑ 1.2 119,569 3

Hash Join (cost=1,272.23..8,014.09 rows=149,131 width=190) (actual time=30.030..343.693 rows=119,569 loops=3)

  • Hash Cond: (ua.user_id = u.id)
7. 87.130 87.130 ↑ 1.2 119,376 3

Parallel Seq Scan on t_user_authority ua (cost=0.00..4,691.31 rows=149,131 width=31) (actual time=0.009..87.130 rows=119,376 loops=3)

8. 6.002 29.937 ↓ 1.0 5,109 3

Hash (cost=1,208.83..1,208.83 rows=5,072 width=167) (actual time=29.937..29.937 rows=5,109 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 571kB
9. 14.271 23.935 ↓ 1.0 5,109 3

Hash Left Join (cost=475.83..1,208.83 rows=5,072 width=167) (actual time=7.011..23.935 rows=5,109 loops=3)

  • Hash Cond: (u.id = npu.user_id)
10. 2.673 2.673 ↑ 1.0 5,072 3

Seq Scan on t_user u (cost=0.00..687.72 rows=5,072 width=65) (actual time=0.009..2.673 rows=5,072 loops=3)

11. 0.095 6.991 ↓ 1.1 95 3

Hash (cost=474.71..474.71 rows=90 width=102) (actual time=6.991..6.991 rows=95 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
12. 0.356 6.896 ↓ 1.1 95 3

Nested Loop (cost=239.21..474.71 rows=90 width=102) (actual time=4.893..6.896 rows=95 loops=3)

13. 0.323 6.538 ↓ 1.1 95 3

Nested Loop Left Join (cost=238.93..444.52 rows=90 width=84) (actual time=4.883..6.538 rows=95 loops=3)

14. 0.393 6.213 ↓ 1.1 95 3

Nested Loop Left Join (cost=238.65..399.85 rows=90 width=92) (actual time=4.871..6.213 rows=95 loops=3)

15. 0.497 5.817 ↓ 1.1 95 3

Nested Loop (cost=238.37..355.18 rows=90 width=92) (actual time=4.861..5.817 rows=95 loops=3)

16. 0.329 5.317 ↑ 1.1 106 3

Hash Join (cost=238.09..258.04 rows=115 width=24) (actual time=4.831..5.317 rows=106 loops=3)

  • Hash Cond: (npu.notificationpreferences_id = np.id)
17. 1.577 1.577 ↑ 1.3 563 3

Seq Scan on t_notification_preferences_user npu (cost=0.00..11.05 rows=705 width=16) (actual time=0.008..1.577 rows=563 loops=3)

18. 0.791 3.411 ↑ 1.0 1,475 3

Hash (cost=219.04..219.04 rows=1,524 width=16) (actual time=3.411..3.411 rows=1,475 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 86kB
19. 2.620 2.620 ↑ 1.0 1,475 3

Seq Scan on t_notification_preferences np (cost=0.00..219.04 rows=1,524 width=16) (actual time=0.374..2.620 rows=1,475 loops=3)

  • Filter: ((retired_date IS NULL) AND ((notification_type)::text = 'SALES'::text))
  • Rows Removed by Filter: 7702
20. 0.003 0.003 ↑ 1.0 1 318

Index Scan using pk_t_restaurantunit on t_restaurantunit ru (cost=0.28..0.84 rows=1 width=76) (actual time=0.003..0.003 rows=1 loops=318)

  • 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
21. 0.003 0.003 ↓ 0.0 0 285

Index Only Scan using t_notification_preferences_actors_pkey on t_notification_preferences_actors npa (cost=0.28..0.49 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=285)

  • Index Cond: (notificationpreferences_id = np.id)
  • Heap Fetches: 1
22. 0.002 0.002 ↓ 0.0 0 285

Index Only Scan using t_notification_preferences_roles_pkey on t_notification_preferences_roles npr (cost=0.28..0.49 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=285)

  • Index Cond: (notificationpreferences_id = np.id)
  • Heap Fetches: 1
23. 0.002 0.002 ↑ 1.0 1 285

Index Scan using pk_t_restaurant on t_concept c (cost=0.28..0.34 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=285)

  • Index Cond: (id = ru.concept_id)
24. 2.276 36.870 ↓ 3.3 1,254 3

Hash (cost=792.91..792.91 rows=381 width=86) (actual time=36.870..36.870 rows=1,254 loops=3)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 164kB
25. 1.300 34.594 ↓ 3.3 1,254 3

Hash Join (cost=663.80..792.91 rows=381 width=86) (actual time=28.239..34.594 rows=1,254 loops=3)

  • Hash Cond: (ru_2.concept_id = c_2.id)
26. 4.416 32.491 ↓ 3.3 1,254 3

Hash Right Join (cost=639.05..763.36 rows=381 width=68) (actual time=27.422..32.491 rows=1,254 loops=3)

  • Hash Cond: (npr_2.notificationpreferences_id = np_2.id)
27. 2.263 2.263 ↓ 1.0 6,035 3

Seq Scan on t_notification_preferences_roles npr_2 (cost=0.00..99.26 rows=6,026 width=8) (actual time=0.019..2.263 rows=6,035 loops=3)

28. 0.986 25.812 ↓ 3.3 1,254 3

Hash (cost=634.29..634.29 rows=381 width=76) (actual time=25.812..25.812 rows=1,254 loops=3)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 153kB
29. 3.695 24.826 ↓ 3.3 1,254 3

Hash Right Join (cost=620.30..634.29 rows=381 width=76) (actual time=22.718..24.826 rows=1,254 loops=3)

  • Hash Cond: (npu_2.notificationpreferences_id = np_2.id)
30. 0.240 0.240 ↑ 1.3 563 3

Seq Scan on t_notification_preferences_user npu_2 (cost=0.00..11.05 rows=705 width=8) (actual time=0.009..0.240 rows=563 loops=3)

31. 1.013 20.891 ↓ 3.3 1,254 3

Hash (cost=615.54..615.54 rows=381 width=76) (actual time=20.891..20.891 rows=1,254 loops=3)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 153kB
32. 4.754 19.878 ↓ 3.3 1,254 3

Hash Join (cost=485.62..615.54 rows=381 width=76) (actual time=14.125..19.878 rows=1,254 loops=3)

  • Hash Cond: (npa_2.notificationpreferences_id = np_2.id)
33. 2.088 2.088 ↑ 1.0 2,987 3

Seq Scan on t_notification_preferences_actors npa_2 (cost=0.00..114.90 rows=2,990 width=20) (actual time=0.164..2.088 rows=2,987 loops=3)

  • Filter: ((actors)::text = 'ALL_USERS'::text)
  • Rows Removed by Filter: 3083
34. 0.914 13.036 ↓ 1.1 1,281 3

Hash (cost=470.70..470.70 rows=1,193 width=64) (actual time=13.036..13.036 rows=1,281 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 143kB
35. 1.451 12.122 ↓ 1.1 1,281 3

Hash Join (cost=232.44..470.70 rows=1,193 width=64) (actual time=5.661..12.122 rows=1,281 loops=3)

  • Hash Cond: (np_2.restaurantunit_id = ru_2.id)
36. 5.419 5.419 ↑ 1.0 1,475 3

Seq Scan on t_notification_preferences np_2 (cost=0.00..219.04 rows=1,524 width=16) (actual time=0.391..5.419 rows=1,475 loops=3)

  • Filter: ((retired_date IS NULL) AND ((notification_type)::text = 'SALES'::text))
  • Rows Removed by Filter: 7702
37. 0.891 5.252 ↓ 1.1 1,281 3

Hash (cost=217.28..217.28 rows=1,212 width=56) (actual time=5.252..5.252 rows=1,281 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 133kB
38. 4.361 4.361 ↓ 1.1 1,281 3

Seq Scan on t_restaurantunit ru_2 (cost=0.00..217.28 rows=1,212 width=56) (actual time=0.011..4.361 rows=1,281 loops=3)

  • 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: 267
39. 0.421 0.803 ↓ 1.0 708 3

Hash (cost=16.00..16.00 rows=700 width=26) (actual time=0.803..0.803 rows=708 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
40. 0.382 0.382 ↓ 1.0 708 3

Seq Scan on t_concept c_2 (cost=0.00..16.00 rows=700 width=26) (actual time=0.018..0.382 rows=708 loops=3)

41. 0.012 22.319 ↑ 192.0 4 3

Hash (cost=810.53..810.53 rows=768 width=90) (actual time=22.319..22.319 rows=4 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 4.050 22.307 ↑ 192.0 4 3

Hash Right Join (cost=683.06..810.53 rows=768 width=90) (actual time=19.757..22.307 rows=4 loops=3)

  • Hash Cond: (npa_1.notificationpreferences_id = np_1.id)
43. 2.574 2.574 ↑ 1.0 6,070 3

Seq Scan on t_notification_preferences_actors npa_1 (cost=0.00..99.72 rows=6,072 width=8) (actual time=0.007..2.574 rows=6,070 loops=3)

44. 0.017 15.683 ↑ 192.0 4 3

Hash (cost=673.46..673.46 rows=768 width=98) (actual time=15.683..15.683 rows=4 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.270 15.666 ↑ 192.0 4 3

Hash Right Join (cost=659.19..673.46 rows=768 width=98) (actual time=15.510..15.666 rows=4 loops=3)

  • Hash Cond: (npu_1.notificationpreferences_id = np_1.id)
46. 0.242 0.242 ↑ 1.3 563 3

Seq Scan on t_notification_preferences_user npu_1 (cost=0.00..11.05 rows=705 width=8) (actual time=0.008..0.242 rows=563 loops=3)

47. 0.012 15.154 ↑ 192.0 4 3

Hash (cost=649.59..649.59 rows=768 width=98) (actual time=15.154..15.154 rows=4 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 0.019 15.142 ↑ 192.0 4 3

Hash Join (cost=510.37..649.59 rows=768 width=98) (actual time=11.619..15.142 rows=4 loops=3)

  • Hash Cond: (ru_1.concept_id = c_1.id)
49. 2.834 14.321 ↑ 192.0 4 3

Hash Join (cost=485.62..615.15 rows=768 width=80) (actual time=10.803..14.321 rows=4 loops=3)

  • Hash Cond: (npr_1.notificationpreferences_id = np_1.id)
50. 2.536 2.536 ↓ 1.0 6,035 3

Seq Scan on t_notification_preferences_roles npr_1 (cost=0.00..99.26 rows=6,026 width=24) (actual time=0.006..2.536 rows=6,035 loops=3)

51. 0.904 8.951 ↓ 1.1 1,281 3

Hash (cost=470.70..470.70 rows=1,193 width=64) (actual time=8.951..8.951 rows=1,281 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 143kB
52. 1.453 8.047 ↓ 1.1 1,281 3

Hash Join (cost=232.44..470.70 rows=1,193 width=64) (actual time=4.336..8.047 rows=1,281 loops=3)

  • Hash Cond: (np_1.restaurantunit_id = ru_1.id)
53. 2.671 2.671 ↑ 1.0 1,475 3

Seq Scan on t_notification_preferences np_1 (cost=0.00..219.04 rows=1,524 width=16) (actual time=0.396..2.671 rows=1,475 loops=3)

  • Filter: ((retired_date IS NULL) AND ((notification_type)::text = 'SALES'::text))
  • Rows Removed by Filter: 7702
54. 0.876 3.923 ↓ 1.1 1,281 3

Hash (cost=217.28..217.28 rows=1,212 width=56) (actual time=3.923..3.923 rows=1,281 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 133kB
55. 3.047 3.047 ↓ 1.1 1,281 3

Seq Scan on t_restaurantunit ru_1 (cost=0.00..217.28 rows=1,212 width=56) (actual time=0.007..3.047 rows=1,281 loops=3)

  • 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: 267
56. 0.421 0.802 ↓ 1.0 708 3

Hash (cost=16.00..16.00 rows=700 width=26) (actual time=0.802..0.802 rows=708 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
57. 0.381 0.381 ↓ 1.0 708 3

Seq Scan on t_concept c_1 (cost=0.00..16.00 rows=700 width=26) (actual time=0.012..0.381 rows=708 loops=3)

Planning time : 7.638 ms
Execution time : 865.485 ms