explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jpqE

Settings
# exclusive inclusive rows x rows loops node
1. 5.715 21,992.559 ↓ 685.0 685 1

Nested Loop (cost=23,638.71..25,193.23 rows=1 width=244) (actual time=7,097.637..21,992.559 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. 530.153 1,007.345 ↓ 1.0 58,762 1

GroupAggregate (cost=0.42..15,075.58 rows=57,419 width=24) (actual time=0.036..1,007.345 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. 477.192 477.192 ↑ 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.016..477.192 rows=187,650 loops=1)

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

Nested Loop (cost=8,563.00..10,117.47 rows=1 width=196) (actual time=7,097.623..21,984.104 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,422.478 18,175.520 ↓ 305,738.0 305,738 1

Nested Loop (cost=8,562.72..10,109.15 rows=1 width=180) (actual time=7,091.910..18,175.520 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.059 0.194 ↑ 1.0 9 1

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

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

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

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

  • Output: e.name, e.id, e.tenant_id
10. 0.010 0.038 ↑ 1.2 4 1

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

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

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

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

Materialize (cost=8,560.38..10,106.56 rows=1 width=116) (actual time=211.773..1,305.872 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. 994.085 5,099.061 ↓ 305,738.0 305,738 1

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

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

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

  • Output: mse.max_effective, mse.task_execution_id
17. 131.745 1,901.433 ↓ 56,519.0 56,519 1

Hash (cost=8,548.39..8,548.39 rows=1 width=65) (actual time=1,901.431..1,901.433 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. 230.161 1,769.688 ↓ 56,519.0 56,519 1

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

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

Hash Join (cost=7,389.67..8,548.38 rows=1 width=137) (actual time=698.856..1,539.527 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. 362.960 491.427 ↓ 9.6 56,519 1

HashAggregate (cost=2,998.14..3,056.91 rows=5,876 width=57) (actual time=361.366..491.427 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.467 128.467 ↑ 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.008..128.467 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. 739.791 739.791 ↓ 9.6 56,519 1

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

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

Hash (cost=2,263.62..2,263.62 rows=58,762 width=73) (actual time=337.443..337.445 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. 131.250 131.250 ↑ 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..131.250 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.256 4.404 ↓ 7.5 749 1

Hash (cost=9.81..9.81 rows=100 width=64) (actual time=4.389..4.404 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.129 2.148 ↓ 7.5 749 1

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

  • Output: jsonb_array_elements((release_plan.release_plan -> 'steps'::text)), release_plan.id, release_plan.environment_id
29. 0.019 0.019 ↑ 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.012..0.019 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 : 12.568 ms
Execution time : 22,007.606 ms