explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VXpk

Settings
# exclusive inclusive rows x rows loops node
1. 6.515 319.140 ↓ 1.2 1,083 1

Sort (cost=4,620,804.63..4,620,806.96 rows=933 width=219) (actual time=318.552..319.140 rows=1,083 loops=1)

  • Sort Key: markings.start_time
  • Sort Method: external merge Disk: 2,584kB
2. 23.553 312.625 ↓ 1.2 1,083 1

GroupAggregate (cost=4,620,541.52..4,620,758.61 rows=933 width=219) (actual time=274.803..312.625 rows=1,083 loops=1)

  • Group Key: markings.id
3. 8.800 289.072 ↓ 48.8 45,486 1

Merge Left Join (cost=4,620,541.52..4,620,742.28 rows=933 width=235) (actual time=274.741..289.072 rows=45,486 loops=1)

  • Merge Cond: (markings.id = "values".id)
4. 0.667 1.352 ↓ 1.2 1,083 1

Sort (cost=3,458.24..3,460.58 rows=933 width=187) (actual time=1.191..1.352 rows=1,083 loops=1)

  • Sort Key: markings.id
  • Sort Method: quicksort Memory: 336kB
5. 0.131 0.685 ↓ 1.2 1,083 1

Nested Loop (cost=60.67..3,412.22 rows=933 width=187) (actual time=0.215..0.685 rows=1,083 loops=1)

6. 0.006 0.006 ↑ 1.0 1 1

Index Scan using schemas_pkey on schemas (cost=0.28..8.29 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (id = '2840904f-0f19-4365-b14a-95295425482e'::uuid)
  • Filter: ((org_id IS NULL) OR (org_id = '4946e15c-1d39-43e1-b3bc-49a2a7aa261c'::uuid))
7. 0.170 0.548 ↓ 1.2 1,083 1

Nested Loop (cost=60.40..3,394.60 rows=933 width=187) (actual time=0.208..0.548 rows=1,083 loops=1)

8. 0.029 0.094 ↑ 1.0 1 1

Index Scan using games_pkey on games (cost=20.74..28.76 rows=1 width=16) (actual time=0.093..0.094 rows=1 loops=1)

  • Index Cond: (id = '0a23a3f5-ff89-434e-8a83-0ff0bd01b56c'::uuid)
  • Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2) OR (hashed SubPlan 3))
9.          

SubPlan (for Index Scan)

10. 0.012 0.012 ↓ 0.0 0 1

Index Only Scan using org_games_pkey on org_games (cost=0.28..8.29 rows=1 width=16) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (org_id = '4946e15c-1d39-43e1-b3bc-49a2a7aa261c'::uuid)
  • Heap Fetches: 0
11. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on org_game_groups (cost=0.00..1.91 rows=1 width=16) (actual time=0.007..0.007 rows=0 loops=1)

  • Filter: (org_id = '4946e15c-1d39-43e1-b3bc-49a2a7aa261c'::uuid)
  • Rows Removed by Filter: 76
12. 0.015 0.046 ↑ 2.3 35 1

Hash Join (cost=4.44..9.92 rows=79 width=16) (actual time=0.032..0.046 rows=35 loops=1)

  • Hash Cond: (game_groups.league_id = org_leagues.league_id)
13. 0.010 0.010 ↑ 1.0 35 1

Seq Scan on game_groups (cost=0.00..5.35 rows=35 width=32) (actual time=0.003..0.010 rows=35 loops=1)

14. 0.006 0.021 ↑ 1.0 28 1

Hash (cost=4.09..4.09 rows=28 width=16) (actual time=0.021..0.021 rows=28 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
15. 0.015 0.015 ↑ 1.0 28 1

Seq Scan on org_leagues (cost=0.00..4.09 rows=28 width=16) (actual time=0.007..0.015 rows=28 loops=1)

  • Filter: (org_id = '4946e15c-1d39-43e1-b3bc-49a2a7aa261c'::uuid)
  • Rows Removed by Filter: 140
16. 0.182 0.284 ↓ 1.2 1,083 1

Bitmap Heap Scan on "markings_2840904f-0f19-4365-b14a-95295425482e" markings (cost=39.66..3,356.50 rows=933 width=187) (actual time=0.114..0.284 rows=1,083 loops=1)

  • Recheck Cond: (game_id = '0a23a3f5-ff89-434e-8a83-0ff0bd01b56c'::uuid)
  • Filter: (schema_id = '2840904f-0f19-4365-b14a-95295425482e'::uuid)
  • Heap Blocks: exact=22
17. 0.102 0.102 ↓ 1.2 1,083 1

Bitmap Index Scan on "markings_2840904f-0f19-4365-b14a-95295425482e_game_id_idx" (cost=0.00..39.43 rows=933 width=0) (actual time=0.102..0.102 rows=1,083 loops=1)

  • Index Cond: (game_id = '0a23a3f5-ff89-434e-8a83-0ff0bd01b56c'::uuid)
18. 45.670 278.920 ↓ 1.2 45,486 1

Sort (cost=4,617,083.27..4,617,181.24 rows=39,186 width=64) (actual time=273.543..278.920 rows=45,486 loops=1)

  • Sort Key: "values".id
  • Sort Method: external sort Disk: 2,616kB
19. 4.850 233.250 ↓ 1.2 45,486 1

Subquery Scan on values (cost=4,613,603.94..4,614,093.76 rows=39,186 width=64) (actual time=223.395..233.250 rows=45,486 loops=1)

20. 29.246 228.400 ↓ 1.2 45,486 1

Sort (cost=4,613,603.94..4,613,701.90 rows=39,186 width=98) (actual time=223.393..228.400 rows=45,486 loops=1)

  • Sort Key: attributes.attribute_index
  • Sort Method: external merge Disk: 2,552kB
21. 29.829 199.154 ↓ 1.2 45,486 1

GroupAggregate (cost=4,607,587.24..4,608,468.93 rows=39,186 width=98) (actual time=164.392..199.154 rows=45,486 loops=1)

  • Group Key: attributes.id, markings_1.id
22. 46.291 169.325 ↓ 1.2 45,486 1

Sort (cost=4,607,587.24..4,607,685.21 rows=39,186 width=59) (actual time=164.373..169.325 rows=45,486 loops=1)

  • Sort Key: attributes.id, markings_1.id
  • Sort Method: external merge Disk: 3,056kB
23. 23.601 123.034 ↓ 1.2 45,486 1

Nested Loop Left Join (cost=40.50..4,604,597.73 rows=39,186 width=59) (actual time=0.138..123.034 rows=45,486 loops=1)

24. 4.812 8.461 ↓ 1.2 45,486 1

Nested Loop (cost=39.94..3,909.54 rows=39,186 width=34) (actual time=0.115..8.461 rows=45,486 loops=1)

25. 0.331 0.400 ↓ 1.2 1,083 1

Bitmap Heap Scan on "markings_2840904f-0f19-4365-b14a-95295425482e" markings_1 (cost=39.66..3,356.50 rows=933 width=32) (actual time=0.074..0.400 rows=1,083 loops=1)

  • Recheck Cond: (game_id = '0a23a3f5-ff89-434e-8a83-0ff0bd01b56c'::uuid)
  • Filter: (schema_id = '2840904f-0f19-4365-b14a-95295425482e'::uuid)
  • Heap Blocks: exact=22
26. 0.069 0.069 ↓ 1.2 1,083 1

Bitmap Index Scan on "markings_2840904f-0f19-4365-b14a-95295425482e_game_id_idx" (cost=0.00..39.43 rows=933 width=0) (actual time=0.069..0.069 rows=1,083 loops=1)

  • Index Cond: (game_id = '0a23a3f5-ff89-434e-8a83-0ff0bd01b56c'::uuid)
27. 3.068 3.249 ↑ 1.0 42 1,083

Materialize (cost=0.28..63.32 rows=42 width=34) (actual time=0.000..0.003 rows=42 loops=1,083)

28. 0.007 0.181 ↑ 1.0 42 1

Nested Loop (cost=0.28..63.11 rows=42 width=34) (actual time=0.038..0.181 rows=42 loops=1)

29. 0.017 0.017 ↑ 1.0 1 1

Index Only Scan using schemas_pkey on schemas schemas_1 (cost=0.28..8.29 rows=1 width=16) (actual time=0.017..0.017 rows=1 loops=1)

  • Index Cond: (id = '2840904f-0f19-4365-b14a-95295425482e'::uuid)
  • Heap Fetches: 1
30. 0.157 0.157 ↑ 1.0 42 1

Seq Scan on attributes (cost=0.00..54.40 rows=42 width=34) (actual time=0.020..0.157 rows=42 loops=1)

  • Filter: (schema_id = '2840904f-0f19-4365-b14a-95295425482e'::uuid)
  • Rows Removed by Filter: 1,121
31. 90.972 90.972 ↑ 28.0 1 45,486

Index Scan using "marking_2840904f-0f19-4365-b14a-952_marking_id_attribute_id_idx" on "marking_2840904f-0f19-4365-b14a-95295425482e_attributes" marking_attributes (cost=0.57..117.13 rows=28 width=57) (actual time=0.002..0.002 rows=1 loops=45,486)

  • Index Cond: ((marking_id = markings_1.id) AND (attribute_id = attributes.id))
Planning time : 1.980 ms
Execution time : 321.243 ms