explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MeaE : easy

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 48.582 ↓ 2.0 2 1

Limit (cost=4,020.23..4,020.23 rows=1 width=247) (actual time=48.581..48.582 rows=2 loops=1)

2. 0.009 48.580 ↓ 2.0 2 1

Sort (cost=4,020.23..4,020.23 rows=1 width=247) (actual time=48.580..48.580 rows=2 loops=1)

  • Sort Key: (COALESCE((count(DISTINCT s_t.training_id)), '0'::bigint)) DESC
  • Sort Method: quicksort Memory: 27kB
3. 0.034 48.571 ↓ 2.0 2 1

WindowAgg (cost=3,636.43..4,020.22 rows=1 width=247) (actual time=48.565..48.571 rows=2 loops=1)

4. 0.505 48.537 ↓ 2.0 2 1

Merge Left Join (cost=3,636.43..4,020.20 rows=1 width=223) (actual time=39.050..48.537 rows=2 loops=1)

  • Merge Cond: (u.id = u_g.user_id)
5. 0.160 32.284 ↓ 2.0 2 1

Nested Loop Left Join (cost=2,734.35..2,890.75 rows=1 width=191) (actual time=28.395..32.284 rows=2 loops=1)

  • Join Filter: (t.assignee_id = u.id)
  • Rows Removed by Join Filter: 2,544
6. 0.072 16.590 ↓ 2.0 2 1

Merge Left Join (cost=1,751.86..1,817.13 rows=1 width=159) (actual time=13.525..16.590 rows=2 loops=1)

  • Merge Cond: (u.id = d_p.user_id)
7. 0.005 8.724 ↓ 2.0 2 1

Nested Loop Left Join (cost=1,202.22..1,242.41 rows=1 width=151) (actual time=7.177..8.724 rows=2 loops=1)

  • Join Filter: (share_item_users.user_id = u.id)
  • Rows Removed by Join Filter: 14
8. 0.006 8.557 ↓ 2.0 2 1

Nested Loop Left Join (cost=1,186.94..1,226.25 rows=1 width=143) (actual time=7.015..8.557 rows=2 loops=1)

  • Join Filter: (l.user_id = u.id)
  • Rows Removed by Join Filter: 29
9. 0.009 8.351 ↓ 2.0 2 1

Nested Loop Left Join (cost=1,171.38..1,209.53 rows=1 width=135) (actual time=6.814..8.351 rows=2 loops=1)

  • Join Filter: (f.user_id = u.id)
  • Rows Removed by Join Filter: 58
10. 0.007 8.120 ↓ 2.0 2 1

Nested Loop Left Join (cost=1,153.72..1,190.20 rows=1 width=127) (actual time=6.591..8.120 rows=2 loops=1)

  • Join Filter: (s_t.user_id = u.id)
  • Rows Removed by Join Filter: 1
11. 0.017 5.241 ↓ 2.0 2 1

Merge Left Join (cost=957.27..993.71 rows=1 width=119) (actual time=3.716..5.241 rows=2 loops=1)

  • Merge Cond: (u.id = s_c.user_id)
12. 0.009 1.711 ↓ 2.0 2 1

Sort (cost=549.71..549.71 rows=1 width=111) (actual time=1.710..1.711 rows=2 loops=1)

  • Sort Key: u.id
  • Sort Method: quicksort Memory: 25kB
13. 1.702 1.702 ↓ 2.0 2 1

Seq Scan on users u (cost=0.00..549.70 rows=1 width=111) (actual time=0.326..1.702 rows=2 loops=1)

  • Filter: (is_active AND (deleted_at IS NULL) AND (lower(concat(lastname, ' ', firstname, ' ', lastname, ' ', email, ' ', username)) ~~ '%android%'::text))
  • Rows Removed by Filter: 6,153
14. 1.614 3.513 ↑ 1.2 64 1

GroupAggregate (cost=407.57..443.01 rows=78 width=24) (actual time=1.548..3.513 rows=64 loops=1)

  • Group Key: s_c.user_id
15. 1.274 1.899 ↑ 1.0 4,458 1

Sort (cost=407.57..419.12 rows=4,622 width=32) (actual time=1.527..1.899 rows=4,458 loops=1)

  • Sort Key: s_c.user_id
  • Sort Method: quicksort Memory: 554kB
16. 0.625 0.625 ↑ 1.0 4,622 1

Seq Scan on stats_results_users_cards s_c (cost=0.00..126.22 rows=4,622 width=32) (actual time=0.008..0.625 rows=4,622 loops=1)

17. 0.005 2.872 ↑ 2.0 1 2

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

  • Sort Key: (count(DISTINCT s_t.training_id)) DESC
  • Sort Method: quicksort Memory: 25kB
18. 0.470 2.867 ↑ 2.0 1 1

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

  • Group Key: s_t.user_id
19. 1.788 2.397 ↓ 209.0 418 1

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

  • Hash Cond: ((c_t.training_id = s_t.training_id) AND (c_t.card_id = s_t.card_id))
20. 0.375 0.375 ↑ 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.007..0.375 rows=3,711 loops=1)

21. 0.091 0.234 ↑ 1.0 440 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 43kB
22. 0.113 0.143 ↑ 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.039..0.143 rows=440 loops=1)

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

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

  • Index Cond: (user_id = 'cf307725-752a-4e41-bd26-dca70684cb90'::uuid)
24. 0.153 0.222 ↑ 1.7 30 2

HashAggregate (cost=17.66..18.18 rows=51 width=24) (actual time=0.107..0.111 rows=30 loops=2)

  • Group Key: f.user_id
25. 0.069 0.069 ↑ 1.0 711 1

Seq Scan on favorites f (cost=0.00..14.11 rows=711 width=16) (actual time=0.012..0.069 rows=711 loops=1)

26. 0.144 0.200 ↑ 2.2 16 2

HashAggregate (cost=15.55..15.91 rows=36 width=24) (actual time=0.098..0.100 rows=16 loops=2)

  • Group Key: l.user_id
27. 0.056 0.056 ↑ 1.0 637 1

Seq Scan on likes l (cost=0.00..12.37 rows=637 width=16) (actual time=0.004..0.056 rows=637 loops=1)

28. 0.110 0.162 ↑ 3.4 8 2

HashAggregate (cost=15.28..15.55 rows=27 width=24) (actual time=0.080..0.081 rows=8 loops=2)

  • Group Key: share_item_users.user_id
29. 0.052 0.052 ↑ 1.0 552 1

Seq Scan on share_item_users (cost=0.00..12.52 rows=552 width=32) (actual time=0.005..0.052 rows=552 loops=1)

30. 1.609 7.794 ↑ 1.3 165 1

GroupAggregate (cost=549.64..572.12 rows=207 width=24) (actual time=5.110..7.794 rows=165 loops=1)

  • Group Key: d_p.user_id
31. 2.149 6.185 ↓ 1.3 3,643 1

Sort (cost=549.64..556.44 rows=2,722 width=32) (actual time=5.019..6.185 rows=3,643 loops=1)

  • Sort Key: d_p.user_id
  • Sort Method: quicksort Memory: 414kB
32. 1.380 4.036 ↓ 1.5 4,068 1

Hash Join (cost=147.56..394.34 rows=2,722 width=32) (actual time=2.118..4.036 rows=4,068 loops=1)

  • Hash Cond: (d_p.game_id = d_g.id)
33. 0.551 0.551 ↑ 1.0 5,859 1

Seq Scan on duel_players d_p (cost=0.00..197.59 rows=5,859 width=32) (actual time=0.003..0.551 rows=5,859 loops=1)

34. 0.271 2.105 ↓ 1.5 2,043 1

Hash (cost=130.40..130.40 rows=1,373 width=16) (actual time=2.105..2.105 rows=2,043 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 112kB
35. 0.417 1.834 ↓ 1.5 2,043 1

Hash Join (cost=42.16..130.40 rows=1,373 width=16) (actual time=0.496..1.834 rows=2,043 loops=1)

  • Hash Cond: (d.status_id = d_s.id)
36. 0.637 1.408 ↓ 1.1 2,043 1

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

  • Hash Cond: (d_g.duel_id = d.id)
37. 0.298 0.298 ↑ 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.005..0.298 rows=2,955 loops=1)

38. 0.091 0.473 ↑ 1.0 596 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 46kB
39. 0.382 0.382 ↑ 1.0 596 1

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

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 366
40. 0.002 0.009 ↑ 1.0 3 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
41. 0.007 0.007 ↑ 1.0 3 1

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

  • Filter: ((name)::text !~~ 'deleted'::text)
  • Rows Removed by Filter: 1
42. 6.411 15.534 ↑ 1.7 1,273 2

HashAggregate (cost=982.50..1,025.38 rows=2,144 width=48) (actual time=7.253..7.767 rows=1,273 loops=2)

  • Group Key: t.assignee_id
43. 9.123 9.123 ↑ 1.0 19,431 1

Seq Scan on tasks t (cost=0.00..739.61 rows=19,431 width=1,188) (actual time=0.021..9.123 rows=19,431 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 8,630
44. 5.132 15.748 ↑ 1.2 4,973 1

GroupAggregate (cost=902.08..1,052.66 rows=6,142 width=48) (actual time=9.511..15.748 rows=4,973 loops=1)

  • Group Key: u_g.user_id
45. 6.010 10.616 ↑ 1.1 9,098 1

Sort (cost=902.08..926.68 rows=9,841 width=42) (actual time=9.491..10.616 rows=9,098 loops=1)

  • Sort Key: u_g.user_id
  • Sort Method: quicksort Memory: 1,344kB
46. 3.472 4.606 ↓ 1.1 11,149 1

Hash Left Join (cost=7.38..249.39 rows=9,841 width=42) (actual time=0.085..4.606 rows=11,149 loops=1)

  • Hash Cond: (u_g.group_id = g.id)
  • Filter: (g.deleted_at IS NULL)
  • Rows Removed by Filter: 38
47. 1.071 1.071 ↓ 1.0 11,187 1

Seq Scan on users_groups u_g (cost=0.00..211.83 rows=11,183 width=32) (actual time=0.008..1.071 rows=11,187 loops=1)

48. 0.031 0.063 ↓ 1.0 152 1

Hash (cost=5.50..5.50 rows=150 width=50) (actual time=0.063..0.063 rows=152 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
49. 0.032 0.032 ↓ 1.0 152 1

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

Planning time : 5.414 ms
Execution time : 48.862 ms