explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HIdX

Settings
# exclusive inclusive rows x rows loops node
1. 2.180 1,095.684 ↑ 1.7 976 1

Sort (cost=3,836,325.20..3,836,329.45 rows=1,700 width=219) (actual time=1,095.555..1,095.684 rows=976 loops=1)

  • Sort Key: markings.start_time
  • Sort Method: quicksort Memory: 3,944kB
2. 21.064 1,093.504 ↑ 1.7 976 1

GroupAggregate (cost=3,835,838.18..3,836,233.98 rows=1,700 width=219) (actual time=1,059.781..1,093.504 rows=976 loops=1)

  • Group Key: markings.id
3. 7.818 1,072.440 ↓ 24.1 40,992 1

Merge Left Join (cost=3,835,838.18..3,836,204.23 rows=1,700 width=235) (actual time=1,059.721..1,072.440 rows=40,992 loops=1)

  • Merge Cond: (markings.id = "values".id)
4. 0.485 1.081 ↑ 1.7 976 1

Sort (cost=5,978.65..5,982.90 rows=1,700 width=187) (actual time=0.940..1.081 rows=976 loops=1)

  • Sort Key: markings.id
  • Sort Method: quicksort Memory: 284kB
5. 0.119 0.596 ↑ 1.7 976 1

Nested Loop (cost=86.62..5,887.44 rows=1,700 width=187) (actual time=0.166..0.596 rows=976 loops=1)

6. 0.012 0.012 ↑ 1.0 1 1

Index Scan using schemas_pkey on schemas (cost=0.28..8.29 rows=1 width=16) (actual time=0.012..0.012 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.153 0.465 ↑ 1.7 976 1

Nested Loop (cost=86.34..5,862.14 rows=1,700 width=187) (actual time=0.153..0.465 rows=976 loops=1)

8. 0.025 0.086 ↑ 1.0 1 1

Index Scan using games_pkey on games (cost=16.74..24.76 rows=1 width=16) (actual time=0.085..0.086 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.011 0.011 ↓ 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.011..0.011 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.011 0.043 ↑ 2.3 35 1

Hash Join (cost=4.44..9.92 rows=79 width=16) (actual time=0.030..0.043 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.007 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.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.169 0.226 ↑ 1.7 976 1

Bitmap Heap Scan on "markings_2840904f-0f19-4365-b14a-95295425482e" markings (cost=69.61..5,820.38 rows=1,700 width=187) (actual time=0.067..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.057 0.057 ↑ 1.7 976 1

Bitmap Index Scan on "markings_2840904f-0f19-4365-b14a-95295425482e_game_id_idx" (cost=0.00..69.18 rows=1,700 width=0) (actual time=0.057..0.057 rows=976 loops=1)

  • Index Cond: (game_id = 'c430904f-a904-48bb-9ac0-edc852110e48'::uuid)
18. 43.130 1,063.541 ↑ 1.7 40,992 1

Sort (cost=3,829,859.53..3,830,038.03 rows=71,400 width=64) (actual time=1,058.775..1,063.541 rows=40,992 loops=1)

  • Sort Key: "values".id
  • Sort Method: external sort Disk: 2,360kB
19. 4.735 1,020.411 ↑ 1.7 40,992 1

Subquery Scan on values (cost=3,820,526.39..3,821,418.89 rows=71,400 width=64) (actual time=1,011.315..1,020.411 rows=40,992 loops=1)

20. 26.612 1,015.676 ↑ 1.7 40,992 1

Sort (cost=3,820,526.39..3,820,704.89 rows=71,400 width=98) (actual time=1,011.313..1,015.676 rows=40,992 loops=1)

  • Sort Key: attributes.attribute_index
  • Sort Method: external sort Disk: 2,296kB
21. 27.135 989.064 ↑ 1.7 40,992 1

GroupAggregate (cost=3,809,257.75..3,810,864.25 rows=71,400 width=98) (actual time=957.467..989.064 rows=40,992 loops=1)

  • Group Key: attributes.id, markings_1.id
22. 57.284 961.929 ↑ 1.7 40,992 1

Sort (cost=3,809,257.75..3,809,436.25 rows=71,400 width=59) (actual time=957.450..961.929 rows=40,992 loops=1)

  • Sort Key: attributes.id, markings_1.id
  • Sort Method: external merge Disk: 2,760kB
23. 5.629 904.645 ↑ 1.7 40,992 1

Nested Loop (cost=1,001.27..3,800,817.11 rows=71,400 width=59) (actual time=12.384..904.645 rows=40,992 loops=1)

24. 0.008 0.008 ↑ 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.006..0.008 rows=1 loops=1)

  • Index Cond: (id = '2840904f-0f19-4365-b14a-95295425482e'::uuid)
  • Heap Fetches: 1
25. 0.000 899.008 ↑ 1.7 40,992 1

Gather (cost=1,001.00..3,800,094.82 rows=71,400 width=91) (actual time=12.376..899.008 rows=40,992 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
26. 15.103 915.143 ↑ 2.2 13,664 3 / 3

Nested Loop Left Join (cost=1.00..3,791,954.82 rows=29,750 width=91) (actual time=7.213..915.143 rows=13,664 loops=3)

27. 2.660 859.048 ↑ 2.2 13,664 3 / 3

Nested Loop (cost=0.43..299,113.67 rows=29,750 width=66) (actual time=7.193..859.048 rows=13,664 loops=3)

28. 815.721 815.721 ↑ 2.2 325 3 / 3

Parallel Index Scan using "markings_2840904f-0f19-4365-b14a-95295425482e_pkey" on "markings_2840904f-0f19-4365-b14a-95295425482e" markings_1 (cost=0.43..260,301.11 rows=708 width=32) (actual time=7.157..815.721 rows=325 loops=3)

  • Filter: ((schema_id = '2840904f-0f19-4365-b14a-95295425482e'::uuid) AND (game_id = 'c430904f-a904-48bb-9ac0-edc852110e48'::uuid))
  • Rows Removed by Filter: 732,151
29. 40.667 40.667 ↑ 1.0 42 976 / 3

Seq Scan on attributes (cost=0.00..54.40 rows=42 width=34) (actual time=0.017..0.125 rows=42 loops=976)

  • Filter: (schema_id = '2840904f-0f19-4365-b14a-95295425482e'::uuid)
  • Rows Removed by Filter: 1,137
30. 40.992 40.992 ↑ 28.0 1 40,992 / 3

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.003..0.003 rows=1 loops=40,992)

  • Index Cond: ((marking_id = markings_1.id) AND (attribute_id = attributes.id))
Planning time : 0.861 ms
Execution time : 1,097.630 ms