explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z82

Settings
# exclusive inclusive rows x rows loops node
1. 301.421 302.950 ↑ 1.4 139 1

CTE Scan on tickets (cost=190,232.23..193,360.65 rows=200 width=192) (actual time=301.023..302.950 rows=139 loops=1)

2.          

CTE teams_to_look

3. 0.164 1.759 ↑ 1.1 139 1

Unique (cost=100.21..348.18 rows=149 width=4) (actual time=0.226..1.759 rows=139 loops=1)

4. 0.376 1.595 ↑ 2.1 1,191 1

Merge Join (cost=100.21..341.84 rows=2,537 width=4) (actual time=0.226..1.595 rows=1,191 loops=1)

  • Merge Cond: (pt.id = pu.team_id)
5. 0.071 0.236 ↑ 1.0 149 1

Sort (cost=99.72..100.09 rows=149 width=4) (actual time=0.219..0.236 rows=149 loops=1)

  • Sort Key: pt.id
  • Sort Method: quicksort Memory: 31kB
6. 0.165 0.165 ↑ 1.0 149 1

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

  • Filter: is_active
  • Rows Removed by Filter: 85
7. 0.983 0.983 ↑ 3.3 1,209 1

Index Only Scan using profile_userconfig_f6a7ca40 on profile_userconfig pu (cost=0.28..480.25 rows=3,984 width=4) (actual time=0.005..0.983 rows=1,209 loops=1)

  • Heap Fetches: 948
8.          

CTE teams_with_children

9. 0.289 3.578 ↑ 32.6 555 1

Recursive Union (cost=0.91..2,714.36 rows=18,112 width=8) (actual time=0.248..3.578 rows=555 loops=1)

10. 0.192 2.833 ↓ 1.1 264 1

Nested Loop (cost=0.91..303.90 rows=242 width=8) (actual time=0.246..2.833 rows=264 loops=1)

11. 1.807 1.807 ↑ 1.1 139 1

CTE Scan on teams_to_look ttl (cost=0.00..2.98 rows=149 width=4) (actual time=0.227..1.807 rows=139 loops=1)

12. 0.278 0.834 ↑ 1.5 2 139

Bitmap Heap Scan on profile_team t (cost=0.91..1.99 rows=3 width=8) (actual time=0.005..0.006 rows=2 loops=139)

  • Recheck Cond: ((ttl.id = id) OR (ttl.id = parent_id))
  • Filter: is_active
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=227
13. 0.278 0.556 ↓ 0.0 0 139

BitmapOr (cost=0.91..0.91 rows=5 width=0) (actual time=0.004..0.004 rows=0 loops=139)

14. 0.139 0.139 ↑ 1.0 1 139

Bitmap Index Scan on profile_team_pkey (cost=0.00..0.43 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=139)

  • Index Cond: (ttl.id = id)
15. 0.139 0.139 ↑ 4.0 1 139

Bitmap Index Scan on profile_team_6be37982 (cost=0.00..0.48 rows=4 width=0) (actual time=0.001..0.001 rows=1 loops=139)

  • Index Cond: (ttl.id = parent_id)
16. 0.236 0.456 ↑ 25.9 69 6

Hash Join (cost=96.20..204.82 rows=1,787 width=8) (actual time=0.030..0.076 rows=69 loops=6)

  • Hash Cond: (twc.child = pt_1.parent_id)
17. 0.060 0.060 ↑ 26.3 92 6

WorkTable Scan on teams_with_children twc (cost=0.00..48.40 rows=2,420 width=8) (actual time=0.000..0.010 rows=92 loops=6)

18. 0.037 0.160 ↑ 1.0 148 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
19. 0.123 0.123 ↑ 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.123 rows=149 loops=1)

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

CTE groupped_users

21. 1.950 9.471 ↑ 1.4 139 1

HashAggregate (cost=9,188.69..9,191.19 rows=200 width=36) (actual time=9.293..9.471 rows=139 loops=1)

  • Group Key: twc_1.team_id
22. 2.087 7.521 ↑ 33.3 5,073 1

Merge Join (cost=1,643.46..8,344.53 rows=168,832 width=8) (actual time=3.986..7.521 rows=5,073 loops=1)

  • Merge Cond: (uc.team_id = twc_1.child)
23. 0.994 0.994 ↑ 3.3 1,209 1

Index Scan using profile_userconfig_f6a7ca40 on profile_userconfig uc (cost=0.28..4,158.91 rows=3,984 width=8) (actual time=0.006..0.994 rows=1,209 loops=1)

24. 0.655 4.440 ↑ 3.6 5,089 1

Sort (cost=1,643.18..1,688.46 rows=18,112 width=8) (actual time=3.976..4.440 rows=5,089 loops=1)

  • Sort Key: twc_1.child
  • Sort Method: quicksort Memory: 51kB
25. 3.785 3.785 ↑ 32.6 555 1

CTE Scan on teams_with_children twc_1 (cost=0.00..362.24 rows=18,112 width=8) (actual time=0.248..3.785 rows=555 loops=1)

26.          

CTE tickets_by_users

27. 9.950 300.738 ↑ 1.4 139 1

CTE Scan on groupped_users (cost=0.00..177,967.00 rows=200 width=44) (actual time=9.899..300.738 rows=139 loops=1)

28.          

SubPlan (forCTE Scan)

29. 2.363 69.639 ↑ 1.0 1 139

Aggregate (cost=502.34..502.35 rows=1 width=8) (actual time=0.501..0.501 rows=1 loops=139)

30. 19.321 67.276 ↑ 4.3 103 139

Bitmap Heap Scan on tickets_ticket (cost=33.27..501.22 rows=448 width=0) (actual time=0.361..0.484 rows=103 loops=139)

  • Recheck Cond: ((((status)::text = 'Open'::text) AND (created_by_id = ANY (groupped_users.users_id)) AND is_active) OR (((status)::text = 'Open'::text) AND (owner_id = ANY (groupped_users.users_id)) AND is_active))
  • Heap Blocks: exact=12202
31. 3.475 47.955 ↓ 0.0 0 139

BitmapOr (cost=33.27..33.27 rows=452 width=0) (actual time=0.345..0.345 rows=0 loops=139)

32. 22.796 22.796 ↑ 2.3 91 139

Bitmap Index Scan on idx_tickets_status_created_by (cost=0.00..16.38 rows=209 width=0) (actual time=0.164..0.164 rows=91 loops=139)

  • Index Cond: (((status)::text = 'Open'::text) AND (created_by_id = ANY (groupped_users.users_id)))
33. 21.684 21.684 ↑ 3.5 69 139

Bitmap Index Scan on idx_tickets_status_owner (cost=0.00..16.68 rows=243 width=0) (actual time=0.156..0.156 rows=69 loops=139)

  • Index Cond: (((status)::text = 'Open'::text) AND (owner_id = ANY (groupped_users.users_id)))
34. 0.556 49.206 ↑ 1.0 1 139

Aggregate (cost=41.24..41.25 rows=1 width=8) (actual time=0.354..0.354 rows=1 loops=139)

35. 2.085 48.650 ↑ 1.2 10 139

Bitmap Heap Scan on tickets_ticket tickets_ticket_1 (cost=28.76..41.21 rows=12 width=0) (actual time=0.338..0.350 rows=10 loops=139)

  • Recheck Cond: ((((status)::text = 'Pending - UST'::text) AND (created_by_id = ANY (groupped_users.users_id)) AND is_active) OR (((status)::text = 'Pending - UST'::text) AND (owner_id = ANY (groupped_users.users_id)) AND is_active))
  • Heap Blocks: exact=1390
36. 3.336 46.565 ↓ 0.0 0 139

BitmapOr (cost=28.76..28.76 rows=12 width=0) (actual time=0.335..0.335 rows=0 loops=139)

37. 21.684 21.684 ↓ 1.3 8 139

Bitmap Index Scan on idx_tickets_status_created_by (cost=0.00..14.38 rows=6 width=0) (actual time=0.156..0.156 rows=8 loops=139)

  • Index Cond: (((status)::text = 'Pending - UST'::text) AND (created_by_id = ANY (groupped_users.users_id)))
38. 21.545 21.545 ↓ 1.1 8 139

Bitmap Index Scan on idx_tickets_status_owner (cost=0.00..14.38 rows=7 width=0) (actual time=0.155..0.155 rows=8 loops=139)

  • Index Cond: (((status)::text = 'Pending - UST'::text) AND (owner_id = ANY (groupped_users.users_id)))
39. 1.529 61.994 ↑ 1.0 1 139

Aggregate (cost=109.47..109.48 rows=1 width=8) (actual time=0.446..0.446 rows=1 loops=139)

40. 11.537 60.465 ↑ 1.3 60 139

Bitmap Heap Scan on tickets_ticket tickets_ticket_2 (cost=29.39..109.28 rows=76 width=0) (actual time=0.362..0.435 rows=60 loops=139)

  • Recheck Cond: ((((status)::text = 'Pending - Client'::text) AND (created_by_id = ANY (groupped_users.users_id)) AND is_active) OR (((status)::text = 'Pending - Client'::text) AND (owner_id = ANY (groupped_users.users_id)) AND is_active))
  • Heap Blocks: exact=7413
41. 3.197 48.928 ↓ 0.0 0 139

BitmapOr (cost=29.39..29.39 rows=77 width=0) (actual time=0.352..0.352 rows=0 loops=139)

42. 22.657 22.657 ↑ 1.1 33 139

Bitmap Index Scan on idx_tickets_status_created_by (cost=0.00..14.68 rows=35 width=0) (actual time=0.163..0.163 rows=33 loops=139)

  • Index Cond: (((status)::text = 'Pending - Client'::text) AND (created_by_id = ANY (groupped_users.users_id)))
43. 23.074 23.074 ↓ 1.4 58 139

Bitmap Index Scan on idx_tickets_status_owner (cost=0.00..14.68 rows=41 width=0) (actual time=0.166..0.166 rows=58 loops=139)

  • Index Cond: (((status)::text = 'Pending - Client'::text) AND (owner_id = ANY (groupped_users.users_id)))
44. 0.973 56.434 ↑ 1.0 1 139

Aggregate (cost=65.36..65.37 rows=1 width=8) (actual time=0.406..0.406 rows=1 loops=139)

45. 5.838 55.461 ↑ 1.1 30 139

Bitmap Heap Scan on tickets_ticket tickets_ticket_3 (cost=28.97..65.28 rows=34 width=0) (actual time=0.362..0.399 rows=30 loops=139)

  • Recheck Cond: ((((status)::text = 'Pending - TPP'::text) AND (created_by_id = ANY (groupped_users.users_id)) AND is_active) OR (((status)::text = 'Pending - TPP'::text) AND (owner_id = ANY (groupped_users.users_id)) AND is_active))
  • Heap Blocks: exact=3920
46. 3.336 49.623 ↓ 0.0 0 139

BitmapOr (cost=28.97..28.97 rows=35 width=0) (actual time=0.357..0.357 rows=0 loops=139)

47. 23.213 23.213 ↓ 1.7 27 139

Bitmap Index Scan on idx_tickets_status_created_by (cost=0.00..14.47 rows=16 width=0) (actual time=0.167..0.167 rows=27 loops=139)

  • Index Cond: (((status)::text = 'Pending - TPP'::text) AND (created_by_id = ANY (groupped_users.users_id)))
48. 23.074 23.074 ↓ 1.4 27 139

Bitmap Index Scan on idx_tickets_status_owner (cost=0.00..14.47 rows=19 width=0) (actual time=0.166..0.166 rows=27 loops=139)

  • Index Cond: (((status)::text = 'Pending - TPP'::text) AND (owner_id = ANY (groupped_users.users_id)))
49. 0.834 53.515 ↑ 1.0 1 139

Aggregate (cost=171.35..171.36 rows=1 width=8) (actual time=0.385..0.385 rows=1 loops=139)

50. 4.726 52.681 ↑ 5.4 25 139

Bitmap Heap Scan on tickets_ticket tickets_ticket_4 (cost=29.92..171.02 rows=135 width=0) (actual time=0.349..0.379 rows=25 loops=139)

  • Recheck Cond: ((((status)::text = 'Pending - Hotel'::text) AND (created_by_id = ANY (groupped_users.users_id)) AND is_active) OR (((status)::text = 'Pending - Hotel'::text) AND (owner_id = ANY (groupped_users.users_id)) AND is_active))
  • Heap Blocks: exact=3242
51. 3.336 47.955 ↓ 0.0 0 139

BitmapOr (cost=29.92..29.92 rows=136 width=0) (actual time=0.345..0.345 rows=0 loops=139)

52. 22.379 22.379 ↑ 2.9 22 139

Bitmap Index Scan on idx_tickets_status_created_by (cost=0.00..14.88 rows=63 width=0) (actual time=0.161..0.161 rows=22 loops=139)

  • Index Cond: (((status)::text = 'Pending - Hotel'::text) AND (created_by_id = ANY (groupped_users.users_id)))
53. 22.240 22.240 ↑ 3.2 23 139

Bitmap Index Scan on idx_tickets_status_owner (cost=0.00..14.97 rows=73 width=0) (actual time=0.160..0.160 rows=23 loops=139)

  • Index Cond: (((status)::text = 'Pending - Hotel'::text) AND (owner_id = ANY (groupped_users.users_id)))
54.          

CTE tickets

55. 0.350 301.180 ↑ 1.4 139 1

HashAggregate (cost=7.00..11.50 rows=200 width=164) (actual time=301.007..301.180 rows=139 loops=1)

  • Group Key: tickets_by_users.team_id
56. 300.830 300.830 ↑ 1.4 139 1

CTE Scan on tickets_by_users (cost=0.00..4.00 rows=200 width=44) (actual time=9.900..300.830 rows=139 loops=1)

57.          

SubPlan (forCTE Scan)

58. 1.529 1.529 ↑ 1.0 1 139

Index Scan using profile_userconfig_f6a7ca40 on profile_userconfig uc_1 (cost=0.28..15.62 rows=1 width=4) (actual time=0.008..0.011 rows=1 loops=139)

  • Index Cond: (team_id = tickets.team_id)
  • Filter: (user_id < 200)
  • Rows Removed by Filter: 8