explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8gkM

Settings
# exclusive inclusive rows x rows loops node
1. 5.630 21,935.157 ↓ 685.0 685 1

Nested Loop (cost=23,638.71..25,193.23 rows=1 width=244) (actual time=7,107.709..21,935.157 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. 526.734 1,015.259 ↓ 1.0 58,762 1

GroupAggregate (cost=0.42..15,075.58 rows=57,419 width=24) (actual time=0.030..1,015.259 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. 488.525 488.525 ↑ 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.011..488.525 rows=187,650 loops=1)

  • Output: task_execution_status.task_execution_id, task_execution_status.effective
  • Heap Fetches: 187650
5. 1,993.170 21,926.787 ↓ 685.0 685 1

Nested Loop (cost=8,563.00..10,117.47 rows=1 width=196) (actual time=7,107.695..21,926.787 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: 305053
6. 6,383.462 18,099.189 ↓ 305,738.0 305,738 1

Nested Loop (cost=8,562.72..10,109.15 rows=1 width=180) (actual time=7,102.041..18,099.189 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: 2445904
7. 0.107 0.220 ↑ 1.0 9 1

Merge Join (cost=2.34..2.46 rows=9 width=112) (actual time=0.095..0.220 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.049 0.078 ↑ 1.0 9 1

Sort (cost=1.23..1.26 rows=9 width=64) (actual time=0.055..0.078 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.020 0.035 ↑ 1.2 4 1

Sort (cost=1.11..1.12 rows=5 width=48) (actual time=0.029..0.035 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,630.906 11,715.507 ↓ 305,738.0 305,738 9

Materialize (cost=8,560.38..10,106.56 rows=1 width=116) (actual time=209.619..1,301.723 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,003.863 5,084.601 ↓ 305,738.0 305,738 1

Hash Join (cost=8,560.38..10,106.55 rows=1 width=116) (actual time=1,886.527..5,084.601 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. 365.008 4,076.547 ↓ 56,519.0 56,519 1

Nested Loop (cost=8,548.82..10,094.97 rows=1 width=69) (actual time=1,882.324..4,076.547 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. 280.155 3,428.944 ↓ 196.9 56,519 1

Hash Join (cost=8,548.40..9,914.97 rows=287 width=89) (actual time=1,882.300..3,428.944 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,266.532 1,266.532 ↓ 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.034..1,266.532 rows=58,762 loops=1)

  • Output: mse.max_effective, mse.task_execution_id
17. 131.463 1,882.257 ↓ 56,519.0 56,519 1

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

  • Output: mte.component_release_id, mte.environment_id, mte.target, mte.name, mte.id
  • Buckets: 65536 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3585kB
18. 227.410 1,750.794 ↓ 56,519.0 56,519 1

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

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

Hash Join (cost=7,389.67..8,548.38 rows=1 width=137) (actual time=676.857..1,523.384 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. 348.072 473.552 ↓ 9.6 56,519 1

HashAggregate (cost=2,998.14..3,056.91 rows=5,876 width=57) (actual time=343.525..473.552 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. 125.480 125.480 ↑ 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.006..125.480 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. 721.780 721.780 ↓ 9.6 56,519 1

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

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

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

  • Output: te.id, te.component_release_id, te.environment_id, te.name, te.target, te.created
  • Buckets: 65536 Batches: 2 Memory Usage: 3702kB
25. 130.545 130.545 ↑ 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..130.545 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.172 4.191 ↓ 7.5 749 1

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

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

ProjectSet (cost=0.28..8.81 rows=100 width=64) (actual time=0.395..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.014 0.014 ↑ 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.014 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 : 13.459 ms
Execution time : 21,946.595 ms