explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Kz6

Settings
# exclusive inclusive rows x rows loops node
1. 6.239 22,100.591 ↓ 685.0 685 1

Nested Loop (cost=23,638.71..25,193.23 rows=1 width=244) (actual time=7,120.798..22,100.591 rows=685 loops=1)

  • Output: ((jsonb_array_elements((release_plan.release_plan -> 'steps'::text))) ->> 'name'::text), ((jsonb_array_elements((release_plan.release_plan -> 'steps'::text))) ->> 'target'::text), ((jsonb_array_elements((release_plan.release_plan -> 'steps'::text))) ->> 'component'::text), ((jsonb_array_elements((release_plan.release_plan -> 'steps'::text))) ->> 'version'::text), COALESCE(ctes.status, 'NOTSTARTED'::execution_status), t.name, e.name, release_plan.id, cr.id, ctes.task_execution_id
  • Inner Unique: true
  • Join Filter: ((t.id = c.tenant_id) AND (((jsonb_array_elements((release_plan.release_plan -> 'steps'::text))) ->> 'component'::text) = (c.name)::text))
2.          

CTE max_status_effective

3. 532.632 1,007.327 ↓ 1.0 58,762 1

GroupAggregate (cost=0.42..15,075.58 rows=57,419 width=24) (actual time=0.074..1,007.327 rows=58,762 loops=1)

  • Output: max(task_execution_status.effective), task_execution_status.task_execution_id
  • Group Key: task_execution_status.task_execution_id
4. 474.695 474.695 ↑ 1.0 187,650 1

Index Only Scan using te_unique_execution_id_effective on public.task_execution_status (cost=0.42..13,563.14 rows=187,650 width=24) (actual time=0.041..474.695 rows=187,650 loops=1)

  • Output: task_execution_status.task_execution_id, task_execution_status.effective
  • Heap Fetches: 187,650
5. 2,090.897 22,091.612 ↓ 685.0 685 1

Nested Loop (cost=8,563.00..10,117.47 rows=1 width=196) (actual time=7,120.777..22,091.612 rows=685 loops=1)

  • Output: (jsonb_array_elements((release_plan.release_plan -> 'steps'::text))), release_plan.id, e.name, e.tenant_id, t.name, t.id, cr.id, cr.component_id, ctes.status, ctes.task_execution_id
  • Inner Unique: true
  • Join Filter: (((jsonb_array_elements((release_plan.release_plan -> 'steps'::text))) ->> 'version'::text) = (cr.version)::text)
  • Rows Removed by Join Filter: 305,053
6. 6,423.693 18,166.287 ↓ 305,738.0 305,738 1

Nested Loop (cost=8,562.72..10,109.15 rows=1 width=180) (actual time=7,114.951..18,166.287 rows=305,738 loops=1)

  • Output: (jsonb_array_elements((release_plan.release_plan -> 'steps'::text))), release_plan.id, e.name, e.tenant_id, t.name, t.id, mte.component_release_id, ctes.status, ctes.task_execution_id
  • Join Filter: (e.id = release_plan.environment_id)
  • Rows Removed by Join Filter: 2,445,904
7. 0.060 0.177 ↑ 1.0 9 1

Merge Join (cost=2.34..2.46 rows=9 width=112) (actual time=0.101..0.177 rows=9 loops=1)

  • Output: e.name, e.id, e.tenant_id, t.name, t.id
  • Inner Unique: true
  • Merge Cond: (e.tenant_id = t.id)
8. 0.051 0.080 ↑ 1.0 9 1

Sort (cost=1.23..1.26 rows=9 width=64) (actual time=0.058..0.080 rows=9 loops=1)

  • Output: e.name, e.id, e.tenant_id
  • Sort Key: e.tenant_id
  • Sort Method: quicksort Memory: 25kB
9. 0.029 0.029 ↑ 1.0 9 1

Seq Scan on public.environment e (cost=0.00..1.09 rows=9 width=64) (actual time=0.010..0.029 rows=9 loops=1)

  • Output: e.name, e.id, e.tenant_id
10. 0.022 0.037 ↑ 1.2 4 1

Sort (cost=1.11..1.12 rows=5 width=48) (actual time=0.030..0.037 rows=4 loops=1)

  • Output: t.name, t.id
  • Sort Key: t.id
  • Sort Method: quicksort Memory: 25kB
11. 0.015 0.015 ↑ 1.0 5 1

Seq Scan on public.tenant t (cost=0.00..1.05 rows=5 width=48) (actual time=0.004..0.015 rows=5 loops=1)

  • Output: t.name, t.id
12. 6,629.112 11,742.417 ↓ 305,738.0 305,738 9

Materialize (cost=8,560.38..10,106.56 rows=1 width=116) (actual time=211.458..1,304.713 rows=305,738 loops=9)

  • Output: (jsonb_array_elements((release_plan.release_plan -> 'steps'::text))), release_plan.id, release_plan.environment_id, mte.component_release_id, mte.environment_id, ctes.status, ctes.task_execution_id
13. 1,001.171 5,113.305 ↓ 305,738.0 305,738 1

Hash Join (cost=8,560.38..10,106.55 rows=1 width=116) (actual time=1,903.082..5,113.305 rows=305,738 loops=1)

  • Output: (jsonb_array_elements((release_plan.release_plan -> 'steps'::text))), release_plan.id, release_plan.environment_id, mte.component_release_id, mte.environment_id, ctes.status, ctes.task_execution_id
  • Hash Cond: ((mte.environment_id = release_plan.environment_id) AND ((mte.target)::text = ((jsonb_array_elements((release_plan.release_plan -> 'steps'::text))) ->> 'target'::text)) AND ((mte.name)::text = ((jsonb_array_elements((release_plan.release_plan -> 'steps'::text))) ->> 'name'::text)))
14. 380.945 4,107.936 ↓ 56,519.0 56,519 1

Nested Loop (cost=8,548.82..10,094.97 rows=1 width=69) (actual time=1,898.870..4,107.936 rows=56,519 loops=1)

  • Output: mte.component_release_id, mte.environment_id, mte.target, mte.name, ctes.status, ctes.task_execution_id
  • Inner Unique: true
15. 286.573 3,444.396 ↓ 196.9 56,519 1

Hash Join (cost=8,548.40..9,914.97 rows=287 width=89) (actual time=1,898.848..3,444.396 rows=56,519 loops=1)

  • Output: mte.component_release_id, mte.environment_id, mte.target, mte.name, mte.id, mse.task_execution_id, mse.max_effective
  • Hash Cond: (mse.task_execution_id = mte.id)
16. 1,259.065 1,259.065 ↓ 1.0 58,762 1

CTE Scan on max_status_effective mse (cost=0.00..1,148.38 rows=57,419 width=24) (actual time=0.079..1,259.065 rows=58,762 loops=1)

  • Output: mse.max_effective, mse.task_execution_id
17. 131.574 1,898.758 ↓ 56,519.0 56,519 1

Hash (cost=8,548.39..8,548.39 rows=1 width=65) (actual time=1,898.757..1,898.758 rows=56,519 loops=1)

  • Output: mte.component_release_id, mte.environment_id, mte.target, mte.name, mte.id
  • Buckets: 65,536 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3,585kB
18. 231.345 1,767.184 ↓ 56,519.0 56,519 1

Subquery Scan on mte (cost=7,389.67..8,548.39 rows=1 width=65) (actual time=686.574..1,767.184 rows=56,519 loops=1)

  • Output: mte.component_release_id, mte.environment_id, mte.target, mte.name, mte.id
19. 468.790 1,535.839 ↓ 56,519.0 56,519 1

Hash Join (cost=7,389.67..8,548.38 rows=1 width=137) (actual time=686.570..1,535.839 rows=56,519 loops=1)

  • Output: te.id, te.component_release_id, te.environment_id, te.name, te.target, NULL::text, NULL::text, NULL::timestamp with time zone
  • Hash Cond: ((me.component_release_id = te.component_release_id) AND (me.environment_id = te.environment_id) AND (me.target = te.target) AND (me.name = te.name) AND (me.max_created = te.created))
20.          

CTE me

21. 347.384 475.746 ↓ 9.6 56,519 1

HashAggregate (cost=2,998.14..3,056.91 rows=5,876 width=57) (actual time=346.625..475.746 rows=56,519 loops=1)

  • Output: task_execution.component_release_id, task_execution.environment_id, task_execution.target, task_execution.name, max(task_execution.created)
  • Group Key: task_execution.component_release_id, task_execution.environment_id, task_execution.target, task_execution.name
22. 128.362 128.362 ↑ 1.0 58,762 1

Seq Scan on public.task_execution (cost=0.00..2,263.62 rows=58,762 width=57) (actual time=0.007..128.362 rows=58,762 loops=1)

  • Output: task_execution.id, task_execution.component_release_id, task_execution.environment_id, task_execution.name, task_execution.target, task_execution.executor_id, task_execution.information, task_execution.created, task_execution.environment_configuration_revision_id, task_execution.executor_metadata
23. 727.148 727.148 ↓ 9.6 56,519 1

CTE Scan on me (cost=0.00..117.52 rows=5,876 width=104) (actual time=346.630..727.148 rows=56,519 loops=1)

  • Output: me.component_release_id, me.environment_id, me.target, me.name, me.max_created
24. 205.954 339.901 ↑ 1.0 58,762 1

Hash (cost=2,263.62..2,263.62 rows=58,762 width=73) (actual time=339.900..339.901 rows=58,762 loops=1)

  • Output: te.id, te.component_release_id, te.environment_id, te.name, te.target, te.created
  • Buckets: 65,536 Batches: 2 Memory Usage: 3,702kB
25. 133.947 133.947 ↑ 1.0 58,762 1

Seq Scan on public.task_execution te (cost=0.00..2,263.62 rows=58,762 width=73) (actual time=0.008..133.947 rows=58,762 loops=1)

  • Output: te.id, te.component_release_id, te.environment_id, te.name, te.target, te.created
26. 282.595 282.595 ↑ 1.0 1 56,519

Index Scan using te_unique_execution_id_effective on public.task_execution_status ctes (cost=0.42..0.63 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=56,519)

  • Output: ctes.id, ctes.task_execution_id, ctes.status, ctes.effective
  • Index Cond: ((ctes.task_execution_id = mse.task_execution_id) AND (ctes.effective = mse.max_effective))
27. 2.179 4.198 ↓ 7.5 749 1

Hash (cost=9.81..9.81 rows=100 width=64) (actual time=4.196..4.198 rows=749 loops=1)

  • Output: (jsonb_array_elements((release_plan.release_plan -> 'steps'::text))), release_plan.id, release_plan.environment_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 215kB
28. 1.988 2.019 ↓ 7.5 749 1

ProjectSet (cost=0.28..8.81 rows=100 width=64) (actual time=0.353..2.019 rows=749 loops=1)

  • Output: jsonb_array_elements((release_plan.release_plan -> 'steps'::text)), release_plan.id, release_plan.environment_id
29. 0.031 0.031 ↑ 1.0 1 1

Index Scan using release_plan_pkey on public.release_plan (cost=0.28..8.30 rows=1 width=459) (actual time=0.010..0.031 rows=1 loops=1)

  • Output: release_plan.id, release_plan.environment_id, release_plan.release_plan, release_plan.effective
  • Index Cond: (release_plan.id = '767a3749-b475-42ee-98f6-bd21c824dcb2'::uuid)
30. 1,834.428 1,834.428 ↑ 1.0 1 305,738

Index Scan using componentrelease_pkey on public.component_release cr (cost=0.28..8.30 rows=1 width=41) (actual time=0.004..0.006 rows=1 loops=305,738)

  • Output: cr.id, cr.component_id, cr.version, cr.release_metadata, cr.version_sort
  • Index Cond: (cr.id = mte.component_release_id)
31. 2.740 2.740 ↑ 1.0 1 685

Index Scan using component_pkey on public.component c (cost=0.14..0.16 rows=1 width=64) (actual time=0.004..0.004 rows=1 loops=685)

  • Output: c.id, c.name, c.description, c.tenant_id
  • Index Cond: (c.id = cr.component_id)
Planning time : 14.674 ms
Execution time : 22,115.474 ms