explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NbRs : heavy

Settings
# exclusive inclusive rows x rows loops node
1. 1,742.434 1,746.059 ↑ 20.0 1 1

Limit (cost=56,719,751,328.12..56,719,751,328.17 rows=20 width=247) (actual time=1,746.059..1,746.059 rows=1 loops=1)

2. 0.010 3.625 ↑ 1,189,560,520,512.0 1 1

Sort (cost=56,719,751,328.12..59,693,652,629.40 rows=1,189,560,520,512 width=247) (actual time=3.625..3.625 rows=1 loops=1)

  • Sort Key: (COALESCE((count(DISTINCT s_t.training_id)), '0'::bigint)) DESC
  • Sort Method: quicksort Memory: 26kB
3. 0.020 3.615 ↑ 1,189,560,520,512.0 1 1

WindowAgg (cost=484.10..25,065,973,554.71 rows=1,189,560,520,512 width=247) (actual time=3.614..3.615 rows=1 loops=1)

4. 0.009 3.595 ↑ 1,189,560,520,512.0 1 1

Hash Left Join (cost=484.10..1,274,763,144.47 rows=1,189,560,520,512 width=223) (actual time=3.590..3.595 rows=1 loops=1)

  • Hash Cond: (u.id = u_g.user_id)
5. 0.004 3.478 ↑ 91,504,655,424.0 1 1

Nested Loop Left Join (cost=432.61..16,574,080.90 rows=91,504,655,424 width=191) (actual time=3.474..3.478 rows=1 loops=1)

  • Join Filter: (u_d.user_id = u.id)
6. 0.004 1.821 ↑ 551,232,864.0 1 1

Nested Loop Left Join (cost=223.51..135,813.88 rows=551,232,864 width=183) (actual time=1.816..1.821 rows=1 loops=1)

  • Join Filter: (u_t_l.user_id = u.id)
7. 0.003 1.647 ↑ 4,481,568.0 1 1

Nested Loop Left Join (cost=217.83..2,091.67 rows=4,481,568 width=151) (actual time=1.643..1.647 rows=1 loops=1)

  • Join Filter: (u_c.user_id = u.id)
8. 0.003 1.399 ↑ 57,456.0 1 1

Nested Loop Left Join (cost=206.15..288.52 rows=57,456 width=143) (actual time=1.395..1.399 rows=1 loops=1)

  • Join Filter: (u_f.user_id = u.id)
9. 0.002 1.352 ↑ 1,368.0 1 1

Nested Loop Left Join (cost=201.24..234.04 rows=1,368 width=135) (actual time=1.349..1.352 rows=1 loops=1)

  • Join Filter: (u_l.user_id = u.id)
10. 0.004 1.281 ↑ 38.0 1 1

Nested Loop Left Join (cost=201.24..217.39 rows=38 width=127) (actual time=1.278..1.281 rows=1 loops=1)

  • Join Filter: (u_s.user_id = u.id)
11. 0.005 1.241 ↑ 2.0 1 1

Nested Loop Left Join (cost=196.73..204.80 rows=2 width=119) (actual time=1.238..1.241 rows=1 loops=1)

  • Join Filter: (s_t.user_id = u.id)
12. 0.041 0.041 ↑ 1.0 1 1

Index Scan using users_pkey on users u (cost=0.28..8.31 rows=1 width=111) (actual time=0.039..0.041 rows=1 loops=1)

  • Index Cond: (id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)
  • Filter: (is_active AND (deleted_at IS NULL) AND (lower(concat(lastname, ' ', firstname, ' ', lastname, ' ', email, ' ', username)) ~~ '%android%'::text))
13. 0.005 1.195 ↑ 2.0 1 1

Sort (cost=196.44..196.45 rows=2 width=24) (actual time=1.194..1.195 rows=1 loops=1)

  • Sort Key: (count(DISTINCT s_t.training_id)) DESC
  • Sort Method: quicksort Memory: 25kB
14. 0.120 1.190 ↑ 2.0 1 1

GroupAggregate (cost=100.79..196.41 rows=2 width=24) (actual time=1.190..1.190 rows=1 loops=1)

  • Group Key: s_t.user_id
15. 0.529 1.070 ↓ 209.0 418 1

Hash Join (cost=100.79..196.38 rows=2 width=32) (actual time=0.246..1.070 rows=418 loops=1)

  • Hash Cond: ((c_t.training_id = s_t.training_id) AND (c_t.card_id = s_t.card_id))
16. 0.318 0.318 ↑ 1.0 3,711 1

Seq Scan on trainings_cards c_t (cost=0.00..76.11 rows=3,711 width=32) (actual time=0.009..0.318 rows=3,711 loops=1)

17. 0.094 0.223 ↑ 1.0 440 1

Hash (cost=94.19..94.19 rows=440 width=48) (actual time=0.223..0.223 rows=440 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 43kB
18. 0.104 0.129 ↑ 1.0 440 1

Bitmap Heap Scan on stats_results_users_trainings_cards s_t (cost=11.69..94.19 rows=440 width=48) (actual time=0.037..0.129 rows=440 loops=1)

  • Recheck Cond: (user_id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)
  • Heap Blocks: exact=36
19. 0.025 0.025 ↑ 1.0 440 1

Bitmap Index Scan on idx_91abc94aa76ed395 (cost=0.00..11.58 rows=440 width=0) (actual time=0.025..0.025 rows=440 loops=1)

  • Index Cond: (user_id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)
20. 0.002 0.036 ↑ 19.0 1 1

Materialize (cost=4.52..12.53 rows=19 width=24) (actual time=0.036..0.036 rows=1 loops=1)

21. 0.001 0.034 ↑ 19.0 1 1

Subquery Scan on u_s (cost=4.52..12.44 rows=19 width=24) (actual time=0.034..0.034 rows=1 loops=1)

22. 0.010 0.033 ↑ 19.0 1 1

GroupAggregate (cost=4.52..12.25 rows=19 width=24) (actual time=0.033..0.033 rows=1 loops=1)

  • Group Key: share_item_users.user_id
23. 0.015 0.023 ↑ 1.0 31 1

Bitmap Heap Scan on share_item_users (cost=4.52..11.90 rows=31 width=32) (actual time=0.012..0.023 rows=31 loops=1)

  • Recheck Cond: (user_id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)
  • Heap Blocks: exact=7
24. 0.008 0.008 ↑ 1.0 31 1

Bitmap Index Scan on idx_dac186ffa76ed395 (cost=0.00..4.51 rows=31 width=0) (actual time=0.008..0.008 rows=31 loops=1)

  • Index Cond: (user_id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)
25. 0.001 0.069 ↑ 36.0 1 1

Materialize (cost=0.00..15.55 rows=36 width=24) (actual time=0.069..0.069 rows=1 loops=1)

26. 0.002 0.068 ↑ 36.0 1 1

Subquery Scan on u_l (cost=0.00..15.37 rows=36 width=24) (actual time=0.067..0.068 rows=1 loops=1)

27. 0.013 0.066 ↑ 36.0 1 1

GroupAggregate (cost=0.00..15.01 rows=36 width=24) (actual time=0.066..0.066 rows=1 loops=1)

  • Group Key: l.user_id
28. 0.053 0.053 ↑ 1.0 138 1

Seq Scan on likes l (cost=0.00..13.96 rows=138 width=16) (actual time=0.009..0.053 rows=138 loops=1)

  • Filter: (user_id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)
  • Rows Removed by Filter: 499
29. 0.002 0.044 ↑ 42.0 1 1

Materialize (cost=4.91..14.40 rows=42 width=24) (actual time=0.044..0.044 rows=1 loops=1)

30. 0.001 0.042 ↑ 42.0 1 1

Subquery Scan on u_f (cost=4.91..14.19 rows=42 width=24) (actual time=0.042..0.042 rows=1 loops=1)

31. 0.011 0.041 ↑ 42.0 1 1

GroupAggregate (cost=4.91..13.77 rows=42 width=24) (actual time=0.041..0.041 rows=1 loops=1)

  • Group Key: f.user_id
32. 0.021 0.030 ↑ 1.0 82 1

Bitmap Heap Scan on favorites f (cost=4.91..12.94 rows=82 width=16) (actual time=0.013..0.030 rows=82 loops=1)

  • Recheck Cond: (user_id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)
  • Heap Blocks: exact=7
33. 0.009 0.009 ↑ 1.0 82 1

Bitmap Index Scan on idx_e46960f5a76ed395 (cost=0.00..4.89 rows=82 width=0) (actual time=0.009..0.009 rows=82 loops=1)

  • Index Cond: (user_id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)
34. 0.001 0.245 ↑ 78.0 1 1

Materialize (cost=11.68..101.29 rows=78 width=24) (actual time=0.245..0.245 rows=1 loops=1)

35. 0.001 0.244 ↑ 78.0 1 1

Subquery Scan on u_c (cost=11.68..100.90 rows=78 width=24) (actual time=0.244..0.244 rows=1 loops=1)

36. 0.135 0.243 ↑ 78.0 1 1

GroupAggregate (cost=11.68..100.12 rows=78 width=24) (actual time=0.243..0.243 rows=1 loops=1)

  • Group Key: s_c.user_id
37. 0.084 0.108 ↑ 1.0 438 1

Bitmap Heap Scan on stats_results_users_cards s_c (cost=11.68..97.15 rows=438 width=32) (actual time=0.029..0.108 rows=438 loops=1)

  • Recheck Cond: (user_id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)
  • Heap Blocks: exact=35
38. 0.024 0.024 ↑ 1.0 438 1

Bitmap Index Scan on idx_38490689a76ed395 (cost=0.00..11.57 rows=438 width=0) (actual time=0.023..0.024 rows=438 loops=1)

  • Index Cond: (user_id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)
39. 0.001 0.170 ↑ 123.0 1 1

Materialize (cost=5.68..359.43 rows=123 width=48) (actual time=0.170..0.170 rows=1 loops=1)

40. 0.001 0.169 ↑ 123.0 1 1

Subquery Scan on u_t_l (cost=5.68..358.81 rows=123 width=48) (actual time=0.169..0.169 rows=1 loops=1)

41. 0.017 0.168 ↑ 123.0 1 1

GroupAggregate (cost=5.68..357.58 rows=123 width=48) (actual time=0.168..0.168 rows=1 loops=1)

  • Group Key: t.assignee_id
42. 0.137 0.151 ↑ 1.1 119 1

Bitmap Heap Scan on tasks t (cost=5.68..353.55 rows=126 width=1,188) (actual time=0.031..0.151 rows=119 loops=1)

  • Recheck Cond: (assignee_id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 63
  • Heap Blocks: exact=66
43. 0.014 0.014 ↑ 1.0 182 1

Bitmap Index Scan on idx_5058659759ec7d60 (cost=0.00..5.65 rows=182 width=0) (actual time=0.014..0.014 rows=182 loops=1)

  • Index Cond: (assignee_id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)
44. 0.001 1.653 ↑ 166.0 1 1

Materialize (cost=209.10..304.98 rows=166 width=24) (actual time=1.653..1.653 rows=1 loops=1)

45. 0.001 1.652 ↑ 166.0 1 1

Subquery Scan on u_d (cost=209.10..304.15 rows=166 width=24) (actual time=1.652..1.652 rows=1 loops=1)

46. 0.079 1.651 ↑ 166.0 1 1

GroupAggregate (cost=209.10..302.49 rows=166 width=24) (actual time=1.651..1.651 rows=1 loops=1)

  • Group Key: d_p.user_id
47. 0.058 1.572 ↓ 1.5 246 1

Hash Join (cost=209.10..300.00 rows=166 width=32) (actual time=0.476..1.572 rows=246 loops=1)

  • Hash Cond: (d.status_id = d_s.id)
48. 0.253 1.502 ↓ 1.1 246 1

Hash Join (cost=208.01..297.72 rows=221 width=48) (actual time=0.456..1.502 rows=246 loops=1)

  • Hash Cond: (d_g.id = d_p.game_id)
49. 0.523 1.049 ↓ 1.1 2,043 1

Hash Join (cost=41.07..119.41 rows=1,831 width=32) (actual time=0.250..1.049 rows=2,043 loops=1)

  • Hash Cond: (d_g.duel_id = d.id)
50. 0.289 0.289 ↑ 1.0 2,955 1

Seq Scan on duel_games d_g (cost=0.00..70.55 rows=2,955 width=32) (actual time=0.004..0.289 rows=2,955 loops=1)

51. 0.088 0.237 ↑ 1.0 596 1

Hash (cost=33.62..33.62 rows=596 width=32) (actual time=0.237..0.237 rows=596 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 46kB
52. 0.149 0.149 ↑ 1.0 596 1

Seq Scan on duels d (cost=0.00..33.62 rows=596 width=32) (actual time=0.007..0.149 rows=596 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 366
53. 0.057 0.200 ↑ 1.0 356 1

Hash (cost=162.49..162.49 rows=356 width=32) (actual time=0.200..0.200 rows=356 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
54. 0.122 0.143 ↑ 1.0 356 1

Bitmap Heap Scan on duel_players d_p (cost=19.04..162.49 rows=356 width=32) (actual time=0.031..0.143 rows=356 loops=1)

  • Recheck Cond: (user_id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)
  • Heap Blocks: exact=57
55. 0.021 0.021 ↑ 1.0 356 1

Bitmap Index Scan on idx_6549af9fa76ed395 (cost=0.00..18.95 rows=356 width=0) (actual time=0.021..0.021 rows=356 loops=1)

  • Index Cond: (user_id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)
56. 0.002 0.012 ↑ 1.0 3 1

Hash (cost=1.05..1.05 rows=3 width=16) (actual time=0.012..0.012 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
57. 0.010 0.010 ↑ 1.0 3 1

Seq Scan on duel_statuses d_s (cost=0.00..1.05 rows=3 width=16) (actual time=0.009..0.010 rows=3 loops=1)

  • Filter: ((name)::text !~~ 'deleted'::text)
  • Rows Removed by Filter: 1
58. 0.002 0.108 ↑ 13.0 1 1

Hash (cost=51.32..51.32 rows=13 width=48) (actual time=0.108..0.108 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
59. 0.002 0.106 ↑ 13.0 1 1

Subquery Scan on u_g (cost=45.06..51.32 rows=13 width=48) (actual time=0.105..0.106 rows=1 loops=1)

60. 0.013 0.104 ↑ 13.0 1 1

GroupAggregate (cost=45.06..51.19 rows=13 width=48) (actual time=0.104..0.104 rows=1 loops=1)

  • Group Key: u_g_1.user_id
61. 0.026 0.091 ↓ 1.2 15 1

Hash Right Join (cost=45.06..50.97 rows=13 width=42) (actual time=0.056..0.091 rows=15 loops=1)

  • Hash Cond: (g.id = u_g_1.group_id)
  • Filter: (g.deleted_at IS NULL)
62. 0.021 0.021 ↓ 1.0 152 1

Seq Scan on groups g (cost=0.00..5.50 rows=150 width=50) (actual time=0.005..0.021 rows=152 loops=1)

63. 0.005 0.044 ↑ 1.0 15 1

Hash (cost=44.87..44.87 rows=15 width=32) (actual time=0.043..0.044 rows=15 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
64. 0.030 0.039 ↑ 1.0 15 1

Bitmap Heap Scan on users_groups u_g_1 (cost=4.40..44.87 rows=15 width=32) (actual time=0.017..0.039 rows=15 loops=1)

  • Recheck Cond: (user_id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)
  • Heap Blocks: exact=9
65. 0.009 0.009 ↓ 2.5 37 1

Bitmap Index Scan on idx_ff8ab7e0a76ed395 (cost=0.00..4.40 rows=15 width=0) (actual time=0.009..0.009 rows=37 loops=1)

  • Index Cond: (user_id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)