explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7ogp

Settings
# exclusive inclusive rows x rows loops node
1. 2.216 291.447 ↓ 1.1 976 1

Sort (cost=4,681,260.02..4,681,262.30 rows=914 width=219) (actual time=291.316..291.447 rows=976 loops=1)

  • Sort Key: markings.start_time
  • Sort Method: quicksort Memory: 3,944kB
2. 21.661 289.231 ↓ 1.1 976 1

GroupAggregate (cost=4,681,002.40..4,681,215.07 rows=914 width=219) (actual time=254.493..289.231 rows=976 loops=1)

  • Group Key: markings.id
3. 8.049 267.570 ↓ 44.8 40,992 1

Merge Left Join (cost=4,681,002.40..4,681,199.07 rows=914 width=235) (actual time=254.436..267.570 rows=40,992 loops=1)

  • Merge Cond: (markings.id = "values".id)
4. 0.599 1.198 ↓ 1.1 976 1

Sort (cost=3,393.97..3,396.26 rows=914 width=187) (actual time=1.040..1.198 rows=976 loops=1)

  • Sort Key: markings.id
  • Sort Method: quicksort Memory: 284kB
5. 0.120 0.599 ↓ 1.1 976 1

Nested Loop (cost=56.53..3,349.02 rows=914 width=187) (actual time=0.169..0.599 rows=976 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.152 0.473 ↓ 1.1 976 1

Nested Loop (cost=56.25..3,331.59 rows=914 width=187) (actual time=0.162..0.473 rows=976 loops=1)

8. 0.026 0.095 ↑ 1.0 1 1

Index Scan using games_pkey on games (cost=16.74..24.76 rows=1 width=16) (actual time=0.094..0.095 rows=1 loops=1)

  • Index Cond: (id = 'c430904f-a904-48bb-9ac0-edc852110e48'::uuid)
  • Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2) OR (hashed SubPlan 3))
9.          

SubPlan (for Index Scan)

10. 0.015 0.015 ↓ 0.0 0 1

Index Only Scan using org_games_pkey on org_games (cost=0.28..4.29 rows=1 width=16) (actual time=0.015..0.015 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: 75
12. 0.015 0.047 ↑ 2.3 35 1

Hash Join (cost=4.44..9.92 rows=79 width=16) (actual time=0.034..0.047 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.022 ↑ 1.0 28 1

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

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

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

  • Filter: (org_id = '4946e15c-1d39-43e1-b3bc-49a2a7aa261c'::uuid)
  • Rows Removed by Filter: 140
16. 0.170 0.226 ↓ 1.1 976 1

Bitmap Heap Scan on "markings_2840904f-0f19-4365-b14a-95295425482e" markings (cost=39.51..3,297.68 rows=914 width=187) (actual time=0.066..0.226 rows=976 loops=1)

  • Recheck Cond: (game_id = 'c430904f-a904-48bb-9ac0-edc852110e48'::uuid)
  • Filter: (schema_id = '2840904f-0f19-4365-b14a-95295425482e'::uuid)
  • Heap Blocks: exact=20
17. 0.056 0.056 ↓ 1.1 976 1

Bitmap Index Scan on "markings_2840904f-0f19-4365-b14a-95295425482e_game_id_idx" (cost=0.00..39.28 rows=914 width=0) (actual time=0.056..0.056 rows=976 loops=1)

  • Index Cond: (game_id = 'c430904f-a904-48bb-9ac0-edc852110e48'::uuid)
18. 43.966 258.323 ↓ 1.1 40,992 1

Sort (cost=4,677,608.43..4,677,704.40 rows=38,388 width=64) (actual time=253.389..258.323 rows=40,992 loops=1)

  • Sort Key: "values".id
  • Sort Method: external sort Disk: 2,360kB
19. 4.488 214.357 ↓ 1.1 40,992 1

Subquery Scan on values (cost=4,674,205.65..4,674,685.50 rows=38,388 width=64) (actual time=205.250..214.357 rows=40,992 loops=1)

20. 27.380 209.869 ↓ 1.1 40,992 1

Sort (cost=4,674,205.65..4,674,301.62 rows=38,388 width=98) (actual time=205.248..209.869 rows=40,992 loops=1)

  • Sort Key: attributes.attribute_index
  • Sort Method: external sort Disk: 2,296kB
21. 27.364 182.489 ↓ 1.1 40,992 1

GroupAggregate (cost=4,668,318.99..4,669,182.72 rows=38,388 width=98) (actual time=150.559..182.489 rows=40,992 loops=1)

  • Group Key: attributes.id, markings_1.id
22. 44.954 155.125 ↓ 1.1 40,992 1

Sort (cost=4,668,318.99..4,668,414.96 rows=38,388 width=59) (actual time=150.541..155.125 rows=40,992 loops=1)

  • Sort Key: attributes.id, markings_1.id
  • Sort Method: external merge Disk: 2,768kB
23. 20.311 110.171 ↓ 1.1 40,992 1

Nested Loop Left Join (cost=40.36..4,665,396.05 rows=38,388 width=59) (actual time=0.112..110.171 rows=40,992 loops=1)

24. 4.557 7.876 ↓ 1.1 40,992 1

Nested Loop (cost=39.79..3,840.75 rows=38,388 width=34) (actual time=0.093..7.876 rows=40,992 loops=1)

25. 0.346 0.391 ↓ 1.1 976 1

Bitmap Heap Scan on "markings_2840904f-0f19-4365-b14a-95295425482e" markings_1 (cost=39.51..3,297.68 rows=914 width=32) (actual time=0.050..0.391 rows=976 loops=1)

  • Recheck Cond: (game_id = 'c430904f-a904-48bb-9ac0-edc852110e48'::uuid)
  • Filter: (schema_id = '2840904f-0f19-4365-b14a-95295425482e'::uuid)
  • Heap Blocks: exact=20
26. 0.045 0.045 ↓ 1.1 976 1

Bitmap Index Scan on "markings_2840904f-0f19-4365-b14a-95295425482e_game_id_idx" (cost=0.00..39.28 rows=914 width=0) (actual time=0.045..0.045 rows=976 loops=1)

  • Index Cond: (game_id = 'c430904f-a904-48bb-9ac0-edc852110e48'::uuid)
27. 2.745 2.928 ↑ 1.0 42 976

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

28. 0.006 0.183 ↑ 1.0 42 1

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

29. 0.018 0.018 ↑ 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.018 rows=1 loops=1)

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

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

  • Filter: (schema_id = '2840904f-0f19-4365-b14a-95295425482e'::uuid)
  • Rows Removed by Filter: 1,137
31. 81.984 81.984 ↑ 29.0 1 40,992

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..121.14 rows=29 width=57) (actual time=0.002..0.002 rows=1 loops=40,992)

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