explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rfXq

Settings
# exclusive inclusive rows x rows loops node
1. 5.970 22,198.350 ↓ 685.0 685 1

Nested Loop (cost=23,638.71..25,193.23 rows=1 width=244) (actual time=7,161.326..22,198.350 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. 547.028 1,025.774 ↓ 1.0 58,762 1

GroupAggregate (cost=0.42..15,075.58 rows=57,419 width=24) (actual time=0.033..1,025.774 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. 478.746 478.746 ↑ 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.013..478.746 rows=187,650 loops=1)

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

Nested Loop (cost=8,563.00..10,117.47 rows=1 width=196) (actual time=7,161.311..22,189.640 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,466.624 18,329.801 ↓ 305,738.0 305,738 1

Nested Loop (cost=8,562.72..10,109.15 rows=1 width=180) (actual time=7,155.585..18,329.801 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.070 0.187 ↑ 1.0 9 1

Merge Join (cost=2.34..2.46 rows=9 width=112) (actual time=0.097..0.187 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.050 0.080 ↑ 1.0 9 1

Sort (cost=1.23..1.26 rows=9 width=64) (actual time=0.057..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.030 0.030 ↑ 1.0 9 1

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

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

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

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

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

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

Materialize (cost=8,560.38..10,106.56 rows=1 width=116) (actual time=211.832..1,318.110 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,016.686 5,150.904 ↓ 305,738.0 305,738 1

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

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

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

  • Output: mse.max_effective, mse.task_execution_id
17. 132.109 1,901.995 ↓ 56,519.0 56,519 1

Hash (cost=8,548.39..8,548.39 rows=1 width=65) (actual time=1,901.993..1,901.995 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. 230.587 1,769.886 ↓ 56,519.0 56,519 1

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

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

Hash Join (cost=7,389.67..8,548.38 rows=1 width=137) (actual time=687.523..1,539.299 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. 349.620 475.509 ↓ 9.6 56,519 1

HashAggregate (cost=2,998.14..3,056.91 rows=5,876 width=57) (actual time=345.268..475.509 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.889 125.889 ↑ 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.889 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. 728.650 728.650 ↓ 9.6 56,519 1

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

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

Hash (cost=2,263.62..2,263.62 rows=58,762 width=73) (actual time=342.209..342.211 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. 134.635 134.635 ↑ 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..134.635 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.353 4.364 ↓ 7.5 749 1

Hash (cost=9.81..9.81 rows=100 width=64) (actual time=4.362..4.364 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.996 2.011 ↓ 7.5 749 1

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

  • Output: jsonb_array_elements((release_plan.release_plan -> 'steps'::text)), release_plan.id, release_plan.environment_id
29. 0.015 0.015 ↑ 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.015 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.857 ms
Execution time : 22,213.892 ms