explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xtuN

Settings
# exclusive inclusive rows x rows loops node
1. 161.998 4,857.857 ↑ 2.6 13 1

GroupAggregate (cost=20,332.17..34,696.15 rows=34 width=69) (actual time=2,309.886..4,857.857 rows=13 loops=1)

  • Group Key: mtc.test_case_id, tcg.release_id, r.name, tcg.platform_id, p.name, u.id, u.intel_alias, u.intel_name
2. 106.455 2,017.601 ↓ 2,917.3 99,188 1

Sort (cost=20,332.17..20,332.25 rows=34 width=69) (actual time=2,005.108..2,017.601 rows=99,188 loops=1)

  • Sort Key: mtc.test_case_id, tcg.release_id, r.name, tcg.platform_id, p.name, u.id, u.intel_alias, u.intel_name
  • Sort Method: quicksort Memory: 17021kB
3. 31.207 1,911.146 ↓ 2,917.3 99,188 1

Hash Join (cost=12,649.30..20,331.30 rows=34 width=69) (actual time=54.491..1,911.146 rows=99,188 loops=1)

  • Hash Cond: (tcg.platform_id = p.id)
4. 62.582 1,879.929 ↓ 2,917.3 99,188 1

Nested Loop (cost=12,648.06..20,329.59 rows=34 width=60) (actual time=54.472..1,879.929 rows=99,188 loops=1)

5. 76.408 706.348 ↓ 2,057.4 370,333 1

Nested Loop (cost=12,647.49..20,210.77 rows=180 width=56) (actual time=54.332..706.348 rows=370,333 loops=1)

6. 0.010 59.747 ↓ 4.3 13 1

Nested Loop (cost=12,647.06..19,381.37 rows=3 width=52) (actual time=54.279..59.747 rows=13 loops=1)

7. 0.024 0.024 ↑ 1.0 1 1

Seq Scan on releases r (cost=0.00..4.53 rows=1 width=16) (actual time=0.006..0.024 rows=1 loops=1)

  • Filter: (active AND (id = 0))
  • Rows Removed by Filter: 201
8. 0.011 59.713 ↓ 4.3 13 1

Nested Loop (cost=12,647.06..19,376.82 rows=3 width=36) (actual time=54.270..59.713 rows=13 loops=1)

9. 9.734 9.734 ↑ 1.0 1 1

Seq Scan on users u (cost=0.00..3,792.89 rows=1 width=28) (actual time=4.377..9.734 rows=1 loops=1)

  • Filter: ((intel_alias)::text = 'mohamad2'::text)
  • Rows Removed by Filter: 13115
10. 0.089 49.968 ↑ 3.2 13 1

Bitmap Heap Scan on test_case_details tcd (cost=12,647.06..15,583.51 rows=42 width=12) (actual time=49.889..49.968 rows=13 loops=1)

  • Recheck Cond: ((owner_id = u.id) AND (release_id = 0))
  • Filter: (official AND active)
  • Rows Removed by Filter: 7
  • Heap Blocks: exact=17
11. 0.118 49.879 ↓ 0.0 0 1

BitmapAnd (cost=12,647.06..12,647.06 rows=756 width=0) (actual time=49.879..49.879 rows=0 loops=1)

12. 0.209 0.209 ↑ 25.5 1,242 1

Bitmap Index Scan on test_case_details_owner_id_index (cost=0.00..602.39 rows=31,727 width=0) (actual time=0.209..0.209 rows=1,242 loops=1)

  • Index Cond: (owner_id = u.id)
13. 49.552 49.552 ↑ 1.0 601,136 1

Bitmap Index Scan on test_case_details_pkey (cost=0.00..12,035.77 rows=622,311 width=0) (actual time=49.552..49.552 rows=601,136 loops=1)

  • Index Cond: (release_id = 0)
14. 570.193 570.193 ↓ 4.9 28,487 13

Index Scan using fki_test_case_id_index on new_modular_test_cases mtc (cost=0.44..218.61 rows=5,786 width=8) (actual time=0.034..43.861 rows=28,487 loops=13)

  • Index Cond: (test_case_id = tcd.test_case_id)
15. 1,110.999 1,110.999 ↓ 0.0 0 370,333

Index Scan using test_case_groupings_pkey on test_case_groupings tcg (cost=0.56..0.65 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=370,333)

  • Index Cond: ((release_id = 0) AND (modular_test_case_id = mtc.id))
  • Filter: (official AND (platform_id = ANY ('{2,6}'::integer[])))
  • Rows Removed by Filter: 0
16. 0.003 0.010 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=13) (actual time=0.010..0.010 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.007 0.007 ↑ 1.0 11 1

Seq Scan on platforms p (cost=0.00..1.11 rows=11 width=13) (actual time=0.002..0.007 rows=11 loops=1)

18.          

SubPlan (for GroupAggregate)

19. 0.182 0.182 ↑ 1.0 1 13

Index Scan using test_cases_pkey on test_cases tc (cost=0.43..8.45 rows=1 width=74) (actual time=0.014..0.014 rows=1 loops=13)

  • Index Cond: (id = mtc.test_case_id)
20. 198.376 2,678.076 ↑ 1.0 1 99,188

Aggregate (cost=413.95..413.97 rows=1 width=12) (actual time=0.027..0.027 rows=1 loops=99,188)

21. 0.000 2,479.700 ↓ 0.0 0 99,188

Nested Loop (cost=1.29..413.75 rows=41 width=12) (actual time=0.025..0.025 rows=0 loops=99,188)

22. 198.376 198.376 ↑ 1.0 1 99,188

Index Only Scan using new_modular_test_cases_id_index on new_modular_test_cases mtc2 (cost=0.44..8.46 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=99,188)

  • Index Cond: (id = tcg.modular_test_case_id)
  • Heap Fetches: 0
23. 116.422 2,281.324 ↓ 0.0 0 99,188

Nested Loop (cost=0.85..404.88 rows=41 width=16) (actual time=0.023..0.023 rows=0 loops=99,188)

24. 1,686.196 1,686.196 ↑ 8.4 5 99,188

Index Scan using fki_modular_test_case_id_index on new_modular_test_case_tag_details mtctd (cost=0.57..131.65 rows=42 width=8) (actual time=0.007..0.017 rows=5 loops=99,188)

  • Index Cond: (modular_test_case_id = tcg.modular_test_case_id)
25. 478.706 478.706 ↓ 0.0 0 478,706

Index Scan using new_modular_tag_details_id_index on new_modular_tag_details mtd (cost=0.29..6.50 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=478,706)

  • Index Cond: (id = mtctd.modular_tag_detail_id)