explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1JDe

Settings
# exclusive inclusive rows x rows loops node
1. 605.694 605.694 ↑ 9.5 21 1

CTE Scan on tickets (cost=749,228.60..749,232.60 rows=200 width=44) (actual time=19.920..605.694 rows=21 loops=1)

2.          

CTE teams_to_look

3. 0.009 0.362 ↑ 3.1 21 1

Unique (cost=277.74..278.07 rows=66 width=4) (actual time=0.351..0.362 rows=21 loops=1)

4. 0.014 0.353 ↑ 2.3 29 1

Sort (cost=277.74..277.91 rows=66 width=4) (actual time=0.351..0.353 rows=29 loops=1)

  • Sort Key: pt.id
  • Sort Method: quicksort Memory: 26kB
5. 0.026 0.339 ↑ 2.3 29 1

Hash Join (cost=96.61..275.75 rows=66 width=4) (actual time=0.205..0.339 rows=29 loops=1)

  • Hash Cond: (pu.team_id = pt.id)
6. 0.119 0.119 ↑ 1.5 69 1

Index Scan using profile_userconfig_user_id_key on profile_userconfig pu (cost=0.41..178.24 rows=103 width=4) (actual time=0.005..0.119 rows=69 loops=1)

  • Index Cond: (user_id < 70)
7. 0.036 0.194 ↑ 1.0 149 1

Hash (cost=94.34..94.34 rows=149 width=4) (actual time=0.194..0.194 rows=149 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
8. 0.158 0.158 ↑ 1.0 149 1

Seq Scan on profile_team pt (cost=0.00..94.34 rows=149 width=4) (actual time=0.006..0.158 rows=149 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 85
9.          

CTE teams_with_children

10. 0.042 0.777 ↑ 221.1 64 1

Recursive Union (cost=1.34..2,321.82 rows=14,149 width=8) (actual time=0.371..0.777 rows=64 loops=1)

11. 0.041 0.558 ↑ 4.7 40 1

Nested Loop (cost=1.34..228.46 rows=189 width=8) (actual time=0.369..0.558 rows=40 loops=1)

12. 0.370 0.370 ↑ 3.1 21 1

CTE Scan on teams_to_look ttl (cost=0.00..1.32 rows=66 width=4) (actual time=0.352..0.370 rows=21 loops=1)

13. 0.042 0.147 ↑ 1.5 2 21

Bitmap Heap Scan on profile_team t (cost=1.34..3.41 rows=3 width=8) (actual time=0.006..0.007 rows=2 loops=21)

  • Recheck Cond: ((ttl.id = id) OR (ttl.id = parent_id))
  • Filter: is_active
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=37
14. 0.021 0.105 ↓ 0.0 0 21

BitmapOr (cost=1.34..1.34 rows=5 width=0) (actual time=0.005..0.005 rows=0 loops=21)

15. 0.042 0.042 ↑ 1.0 1 21

Bitmap Index Scan on profile_team_pkey (cost=0.00..0.63 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=21)

  • Index Cond: (ttl.id = id)
16. 0.042 0.042 ↑ 4.0 1 21

Bitmap Index Scan on profile_team_6be37982 (cost=0.00..0.71 rows=4 width=0) (actual time=0.002..0.002 rows=1 loops=21)

  • Index Cond: (ttl.id = parent_id)
17. 0.030 0.177 ↑ 99.7 14 3

Hash Join (cost=96.20..181.04 rows=1,396 width=8) (actual time=0.050..0.059 rows=14 loops=3)

  • Hash Cond: (twc.child = pt_1.parent_id)
18. 0.009 0.009 ↑ 90.0 21 3

WorkTable Scan on teams_with_children twc (cost=0.00..37.80 rows=1,890 width=8) (actual time=0.000..0.003 rows=21 loops=3)

19. 0.035 0.138 ↑ 1.0 148 1

Hash (cost=94.34..94.34 rows=149 width=8) (actual time=0.138..0.138 rows=148 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
20. 0.103 0.103 ↑ 1.0 149 1

Seq Scan on profile_team pt_1 (cost=0.00..94.34 rows=149 width=8) (actual time=0.003..0.103 rows=149 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 85
21.          

CTE groupped_users

22. 0.376 7.888 ↑ 9.5 21 1

HashAggregate (cost=8,557.29..8,559.79 rows=200 width=36) (actual time=7.808..7.888 rows=21 loops=1)

  • Group Key: twc_1.team_id
23. 0.457 7.512 ↑ 176.1 762 1

Merge Join (cost=5,853.70..7,886.31 rows=134,195 width=8) (actual time=6.889..7.512 rows=762 loops=1)

  • Merge Cond: (uc.team_id = twc_1.child)
24. 0.672 6.161 ↑ 3.3 1,192 1

Sort (cost=4,595.26..4,605.10 rows=3,938 width=8) (actual time=6.058..6.161 rows=1,192 loops=1)

  • Sort Key: uc.team_id
  • Sort Method: quicksort Memory: 215kB
25. 1.654 5.489 ↑ 1.6 2,534 1

Hash Join (cost=216.25..4,360.10 rows=3,938 width=8) (actual time=1.950..5.489 rows=2,534 loops=1)

  • Hash Cond: (uc.user_id = au.id)
26. 1.901 1.901 ↑ 1.6 2,534 1

Index Scan using profile_userconfig_f6a7ca40 on profile_userconfig uc (cost=0.28..4,094.42 rows=3,938 width=8) (actual time=0.006..1.901 rows=2,534 loops=1)

27. 0.609 1.934 ↑ 1.0 2,534 1

Hash (cost=184.29..184.29 rows=2,534 width=4) (actual time=1.934..1.934 rows=2,534 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 122kB
28. 1.325 1.325 ↑ 1.0 2,534 1

Index Only Scan using auth_user_pkey on auth_user au (cost=0.28..184.29 rows=2,534 width=4) (actual time=0.005..1.325 rows=2,534 loops=1)

  • Heap Fetches: 1407
29. 0.091 0.894 ↑ 18.6 759 1

Sort (cost=1,258.44..1,293.81 rows=14,149 width=8) (actual time=0.827..0.894 rows=759 loops=1)

  • Sort Key: twc_1.child
  • Sort Method: quicksort Memory: 28kB
30. 0.803 0.803 ↑ 221.1 64 1

CTE Scan on teams_with_children twc_1 (cost=0.00..282.98 rows=14,149 width=8) (actual time=0.373..0.803 rows=64 loops=1)

31.          

CTE tickets

32. 8.222 605.651 ↑ 9.5 21 1

CTE Scan on groupped_users (cost=0.00..738,068.92 rows=200 width=44) (actual time=19.918..605.651 rows=21 loops=1)

33.          

SubPlan (forCTE Scan)

34. 0.420 206.409 ↑ 1.0 1 21

Aggregate (cost=1,432.71..1,432.72 rows=1 width=8) (actual time=9.829..9.829 rows=1 loops=21)

35. 2.877 205.989 ↑ 5.4 83 21

Bitmap Heap Scan on tickets_ticket (cost=963.64..1,431.59 rows=448 width=0) (actual time=9.696..9.809 rows=83 loops=21)

  • Recheck Cond: (((status)::text = 'Open'::text) AND is_active AND ((created_by_id = ANY (groupped_users.users_id)) OR (owner_id = ANY (groupped_users.users_id))))
  • Heap Blocks: exact=1542
36. 4.158 203.112 ↓ 0.0 0 21

BitmapAnd (cost=963.64..963.64 rows=452 width=0) (actual time=9.672..9.672 rows=0 loops=21)

37. 15.309 15.309 ↑ 3.0 4,682 21

Bitmap Index Scan on idx_tickets_status (cost=0.00..146.03 rows=13,814 width=0) (actual time=0.729..0.729 rows=4,682 loops=21)

  • Index Cond: ((status)::text = 'Open'::text)
38. 0.567 183.645 ↓ 0.0 0 21

BitmapOr (cost=817.25..817.25 rows=37,535 width=0) (actual time=8.745..8.745 rows=0 loops=21)

39. 96.705 96.705 ↓ 2.1 36,260 21

Bitmap Index Scan on tickets_ticket_e93cb7eb (cost=0.00..373.47 rows=17,363 width=0) (actual time=4.605..4.605 rows=36,260 loops=21)

  • Index Cond: (created_by_id = ANY (groupped_users.users_id))
40. 86.373 86.373 ↓ 2.1 41,439 21

Bitmap Index Scan on tickets_ticket_5e7b1936 (cost=0.00..443.55 rows=20,173 width=0) (actual time=4.113..4.113 rows=41,439 loops=21)

  • Index Cond: (owner_id = ANY (groupped_users.users_id))
41. 0.126 25.473 ↑ 1.0 1 21

Aggregate (cost=127.54..127.55 rows=1 width=8) (actual time=1.213..1.213 rows=1 loops=21)

42. 25.347 25.347 ↓ 1.9 23 21

Index Scan using idx_tickets_status on tickets_ticket tickets_ticket_1 (cost=0.43..127.51 rows=12 width=0) (actual time=0.555..1.207 rows=23 loops=21)

  • Index Cond: ((status)::text = 'Pending - UST'::text)
  • Filter: ((created_by_id = ANY (groupped_users.users_id)) OR (owner_id = ANY (groupped_users.users_id)))
  • Rows Removed by Filter: 421
43. 0.336 132.594 ↑ 1.0 1 21

Aggregate (cost=773.37..773.38 rows=1 width=8) (actual time=6.314..6.314 rows=1 loops=21)

44. 132.258 132.258 ↓ 1.1 87 21

Index Scan using idx_tickets_status on tickets_ticket tickets_ticket_2 (cost=0.43..773.18 rows=76 width=0) (actual time=1.827..6.298 rows=87 loops=21)

  • Index Cond: ((status)::text = 'Pending - Client'::text)
  • Filter: ((created_by_id = ANY (groupped_users.users_id)) OR (owner_id = ANY (groupped_users.users_id)))
  • Rows Removed by Filter: 2361
45. 0.147 46.662 ↑ 1.0 1 21

Aggregate (cost=353.27..353.28 rows=1 width=8) (actual time=2.222..2.222 rows=1 loops=21)

46. 46.515 46.515 ↑ 1.2 29 21

Index Scan using idx_tickets_status on tickets_ticket tickets_ticket_3 (cost=0.43..353.18 rows=34 width=0) (actual time=0.746..2.215 rows=29 loops=21)

  • Index Cond: ((status)::text = 'Pending - TPP'::text)
  • Filter: ((created_by_id = ANY (groupped_users.users_id)) OR (owner_id = ANY (groupped_users.users_id)))
  • Rows Removed by Filter: 784
47. 0.273 186.291 ↑ 1.0 1 21

Aggregate (cost=1,003.38..1,003.39 rows=1 width=8) (actual time=8.871..8.871 rows=1 loops=21)

48. 1.344 186.018 ↑ 4.2 32 21

Bitmap Heap Scan on tickets_ticket tickets_ticket_4 (cost=861.94..1,003.04 rows=135 width=0) (actual time=8.811..8.858 rows=32 loops=21)

  • Recheck Cond: (((status)::text = 'Pending - Hotel'::text) AND is_active AND ((created_by_id = ANY (groupped_users.users_id)) OR (owner_id = ANY (groupped_users.users_id))))
  • Heap Blocks: exact=651
49. 3.276 184.674 ↓ 0.0 0 21

BitmapAnd (cost=861.94..861.94 rows=136 width=0) (actual time=8.794..8.794 rows=0 loops=21)

50. 13.125 13.125 ↑ 1.1 3,934 21

Bitmap Index Scan on idx_tickets_status (cost=0.00..44.57 rows=4,152 width=0) (actual time=0.625..0.625 rows=3,934 loops=21)

  • Index Cond: ((status)::text = 'Pending - Hotel'::text)
51. 0.588 168.273 ↓ 0.0 0 21

BitmapOr (cost=817.09..817.09 rows=37,535 width=0) (actual time=8.013..8.013 rows=0 loops=21)

52. 90.174 90.174 ↓ 2.1 36,260 21

Bitmap Index Scan on tickets_ticket_e93cb7eb (cost=0.00..373.47 rows=17,363 width=0) (actual time=4.294..4.294 rows=36,260 loops=21)

  • Index Cond: (created_by_id = ANY (groupped_users.users_id))
53. 77.511 77.511 ↓ 2.1 41,439 21

Bitmap Index Scan on tickets_ticket_5e7b1936 (cost=0.00..443.55 rows=20,173 width=0) (actual time=3.691..3.691 rows=41,439 loops=21)

  • Index Cond: (owner_id = ANY (groupped_users.users_id))