explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JWCg

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 30.243 ↑ 1.0 1 1

Limit (cost=6,925.22..6,925.23 rows=1 width=140) (actual time=30.242..30.243 rows=1 loops=1)

2.          

CTE personal_prefs

3. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=18.95..30.99 rows=1 width=4) (never executed)

  • Filter: (COALESCE(useremailprefs_1.preference, defaultemailprefs_1.preference) = 1)
4. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on defaultemailprefs defaultemailprefs_1 (cost=18.52..22.54 rows=1 width=8) (never executed)

  • Recheck Cond: ((orgid IS NULL) AND (action = ANY ('{3,17,74}'::integer[])))
5. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=18.52..18.52 rows=1 width=0) (never executed)

6. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_defaultemailprefs_orgid (cost=0.00..4.81 rows=52 width=0) (never executed)

  • Index Cond: (orgid IS NULL)
7. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_defaultemailprefs_action (cost=0.00..13.46 rows=27 width=0) (never executed)

  • Index Cond: (action = ANY ('{3,17,74}'::integer[]))
8. 0.000 0.000 ↓ 0.0 0

Index Scan using useremailprefs_userid_action_groupid_unique on useremailprefs useremailprefs_1 (cost=0.42..8.44 rows=1 width=8) (never executed)

  • Index Cond: ((userid = 656425065) AND (action = defaultemailprefs_1.action))
9.          

CTE prefs

10. 0.052 16.662 ↓ 30.0 30 1

Nested Loop Left Join (cost=25.08..184.45 rows=1 width=8) (actual time=16.047..16.662 rows=30 loops=1)

  • Filter: (COALESCE(CASE WHEN ((defaultemailprefs_2.action = ANY ('{3,17,74,70,71}'::integer[])) OR (useremailprefs_3.preference = 4)) THEN COALESCE(useremailprefs_2.preference, defaultemailprefs_2.preference) ELSE useremailprefs_3.preference END, CASE WHEN (useremailprefs_5.preference = 4) THEN useremailprefs_4.preference ELSE useremailprefs_5.preference END, CASE WHEN (defaultemailprefs_4.preference = 4) THEN defaultemailprefs_3.preference ELSE defaultemailprefs_4.preference END, defaultemailprefs_2.preference) = 1)
  • Rows Removed by Filter: 80
11. 0.023 16.390 ↓ 22.0 110 1

Nested Loop Left Join (cost=24.66..142.13 rows=5 width=32) (actual time=7.289..16.390 rows=110 loops=1)

  • Join Filter: (defaultemailprefs_3.action = defaultemailprefs_2.action)
  • Rows Removed by Join Filter: 110
12. 0.055 0.747 ↓ 22.0 110 1

Nested Loop Left Join (cost=24.24..99.88 rows=5 width=28) (actual time=0.284..0.747 rows=110 loops=1)

  • Join Filter: (useremailprefs_5.groupid = invites.ownerid)
  • Rows Removed by Join Filter: 110
13. 0.101 0.692 ↓ 22.0 110 1

Nested Loop Left Join (cost=19.80..87.41 rows=5 width=24) (actual time=0.274..0.692 rows=110 loops=1)

  • Join Filter: ((useremailprefs_4.groupid = invites.ownerid) AND (useremailprefs_4.action = defaultemailprefs_2.action))
  • Rows Removed by Join Filter: 330
14. 0.067 0.591 ↓ 22.0 110 1

Nested Loop Left Join (cost=15.32..55.18 rows=5 width=20) (actual time=0.246..0.591 rows=110 loops=1)

15. 0.063 0.524 ↓ 22.0 110 1

Nested Loop (cost=15.03..46.81 rows=5 width=16) (actual time=0.233..0.524 rows=110 loops=1)

16. 0.007 0.263 ↓ 22.0 22 1

Nested Loop Left Join (cost=10.57..22.61 rows=1 width=12) (actual time=0.202..0.263 rows=22 loops=1)

17. 0.010 0.036 ↓ 22.0 22 1

Bitmap Heap Scan on defaultemailprefs defaultemailprefs_2 (cost=10.15..14.16 rows=1 width=8) (actual time=0.031..0.036 rows=22 loops=1)

  • Recheck Cond: ((orgid IS NULL) AND (action IS NOT NULL))
  • Filter: (groupid IS NULL)
18. 0.002 0.026 ↓ 0.0 0 1

BitmapAnd (cost=10.15..10.15 rows=1 width=0) (actual time=0.026..0.026 rows=0 loops=1)

19. 0.015 0.015 ↑ 2.3 23 1

Bitmap Index Scan on ix_defaultemailprefs_orgid (cost=0.00..4.81 rows=52 width=0) (actual time=0.015..0.015 rows=23 loops=1)

  • Index Cond: (orgid IS NULL)
20. 0.009 0.009 ↑ 2.4 37 1

Bitmap Index Scan on ix_defaultemailprefs_action (cost=0.00..5.09 rows=89 width=0) (actual time=0.009..0.009 rows=37 loops=1)

  • Index Cond: (action IS NOT NULL)
21. 0.220 0.220 ↓ 0.0 0 22

Index Scan using useremailprefs_userid_action_groupidnull_unique on useremailprefs useremailprefs_2 (cost=0.42..8.44 rows=1 width=8) (actual time=0.009..0.010 rows=0 loops=22)

  • Index Cond: ((userid = 656425065) AND (action = defaultemailprefs_2.action))
22. 0.110 0.198 ↑ 1.0 5 22

Bitmap Heap Scan on invites (cost=4.46..24.15 rows=5 width=4) (actual time=0.005..0.009 rows=5 loops=22)

  • Recheck Cond: (userid = 656425065)
  • Filter: (status = 1)
23. 0.088 0.088 ↑ 1.0 5 22

Bitmap Index Scan on ix_invites_userid (cost=0.00..4.46 rows=5 width=0) (actual time=0.004..0.004 rows=5 loops=22)

  • Index Cond: (userid = 656425065)
24. 0.000 0.000 ↑ 1.0 1 110

Materialize (cost=0.29..8.31 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=110)

25. 0.009 0.009 ↑ 1.0 1 1

Index Scan using useremailprefs_userid_actionnull_groupidnull_unique on useremailprefs useremailprefs_3 (cost=0.29..8.31 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (userid = 656425065)
26. 0.000 0.000 ↑ 2.3 3 110

Materialize (cost=4.48..31.63 rows=7 width=12) (actual time=0.000..0.000 rows=3 loops=110)

27. 0.004 0.016 ↑ 2.3 3 1

Bitmap Heap Scan on useremailprefs useremailprefs_4 (cost=4.48..31.60 rows=7 width=12) (actual time=0.014..0.016 rows=3 loops=1)

  • Recheck Cond: (userid = 656425065)
28. 0.012 0.012 ↑ 2.3 3 1

Bitmap Index Scan on useremailprefs_userid_action_groupid_unique (cost=0.00..4.48 rows=7 width=0) (actual time=0.012..0.012 rows=3 loops=1)

  • Index Cond: (userid = 656425065)
29. 0.000 0.000 ↑ 2.0 1 110

Materialize (cost=4.44..12.33 rows=2 width=8) (actual time=0.000..0.000 rows=1 loops=110)

30. 0.002 0.006 ↑ 2.0 1 1

Bitmap Heap Scan on useremailprefs useremailprefs_5 (cost=4.44..12.32 rows=2 width=8) (actual time=0.005..0.006 rows=1 loops=1)

  • Recheck Cond: ((userid = 656425065) AND (action IS NULL))
31. 0.004 0.004 ↑ 2.0 1 1

Bitmap Index Scan on useremailprefs_userid_action_groupid_unique (cost=0.00..4.44 rows=2 width=0) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: ((userid = 656425065) AND (action IS NULL))
32. 15.620 15.620 ↑ 1.0 1 110

Index Scan using ix_defaultemailprefs_groupid on defaultemailprefs defaultemailprefs_3 (cost=0.42..8.44 rows=1 width=12) (actual time=0.141..0.142 rows=1 loops=110)

  • Index Cond: (groupid = invites.ownerid)
33. 0.220 0.220 ↑ 1.0 1 110

Index Scan using ix_defaultemailprefs_groupid on defaultemailprefs defaultemailprefs_4 (cost=0.42..8.44 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=110)

  • Index Cond: (groupid = invites.ownerid)
  • Filter: (action IS NULL)
34. 0.040 30.242 ↑ 1.0 1 1

Sort (cost=6,709.78..6,709.78 rows=1 width=140) (actual time=30.241..30.242 rows=1 loops=1)

  • Sort Key: activity.created
  • Sort Method: quicksort Memory: 25kB
35. 0.221 30.202 ↑ 1.0 1 1

Nested Loop Left Join (cost=23.69..6,709.77 rows=1 width=140) (actual time=30.154..30.202 rows=1 loops=1)

  • Filter: (((activity.action = ANY ('{3,17,74}'::integer[])) AND (hashed SubPlan 3)) OR ((activity.action <> ALL ('{3,17,74}'::integer[])) AND (SubPlan 6)))
  • Rows Removed by Filter: 111
36. 0.038 12.845 ↓ 112.0 112 1

Nested Loop (cost=23.38..6,695.86 rows=1 width=140) (actual time=3.824..12.845 rows=112 loops=1)

37. 3.680 4.600 ↑ 1.8 283 1

Bitmap Heap Scan on notification_recipients (cost=22.94..2,434.98 rows=504 width=28) (actual time=1.186..4.600 rows=283 loops=1)

  • Recheck Cond: ((userid = 656425065) AND (email = 0))
  • Filter: (status = 0)
  • Rows Removed by Filter: 253
38. 0.920 0.920 ↓ 1.3 801 1

Bitmap Index Scan on ix_notification_recipients_userid_email (cost=0.00..22.82 rows=625 width=0) (actual time=0.920..0.920 rows=801 loops=1)

  • Index Cond: ((userid = 656425065) AND (email = 0))
39. 8.207 8.207 ↓ 0.0 0 283

Index Scan using activity_pkey on activity (cost=0.43..8.44 rows=1 width=112) (actual time=0.028..0.029 rows=0 loops=283)

  • Index Cond: (id = notification_recipients.activityid)
  • Filter: ((created > '2019-06-27 22:59:57.896565'::timestamp without time zone) AND (delete_status = 0))
  • Rows Removed by Filter: 1
40. 0.000 0.000 ↓ 0.0 0 112

Index Scan using mentionkeys_pkey on mentionkeys (cost=0.29..5.40 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=112)

  • Index Cond: (id = notification_recipients.mentionid)
41.          

SubPlan (forNested Loop Left Join)

42. 0.000 0.000 ↓ 0.0 0

CTE Scan on personal_prefs (cost=0.00..0.02 rows=1 width=4) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Index Scan using useremailprefs_userid_action_groupid_unique on useremailprefs (cost=0.42..8.44 rows=1 width=0) (never executed)

  • Index Cond: ((userid = 656425065) AND (action = mentionkeys.action))
  • Filter: (preference IS NULL)
44. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on useremailprefs useremailprefs_6 (cost=4.48..31.59 rows=1 width=4) (never executed)

  • Recheck Cond: (userid = 656425065)
  • Filter: (preference IS NULL)
45. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on useremailprefs_userid_action_groupid_unique (cost=0.00..4.48 rows=7 width=0) (never executed)

  • Index Cond: (userid = 656425065)
46. 17.136 17.136 ↓ 6.0 6 112

CTE Scan on prefs (cost=0.00..0.02 rows=1 width=4) (actual time=0.144..0.153 rows=6 loops=112)

  • Filter: (ownerid = notification_recipients.ctxid)
  • Rows Removed by Filter: 24