explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sX2b

Settings
# exclusive inclusive rows x rows loops node
1. 4.961 7,329.582 ↓ 714.0 714 1

Nested Loop (cost=27,900.36..29,375.14 rows=1 width=244) (actual time=6,263.104..7,329.582 rows=714 loops=1)

2.          

CTE max_status_effective

3. 393.680 774.991 ↓ 1.0 55,731 1

GroupAggregate (cost=0.42..20,043.21 rows=53,619 width=24) (actual time=0.047..774.991 rows=55,731 loops=1)

  • Group Key: task_execution_status.task_execution_id
4. 381.311 381.311 ↑ 1.0 177,761 1

Index Only Scan using te_unique_execution_id_effective on task_execution_status (cost=0.42..18,618.21 rows=177,761 width=24) (actual time=0.031..381.311 rows=177,761 loops=1)

  • Heap Fetches: 177761
5. 2.504 7,321.051 ↓ 2.7 714 1

Hash Join (cost=7,856.73..9,134.27 rows=268 width=168) (actual time=6,263.089..7,321.051 rows=714 loops=1)

  • Hash Cond: ((rd.environment_id = e.id) AND (t.id = e.tenant_id))
6. 94.378 7,318.489 ↓ 2.7 714 1

Hash Join (cost=7,830.48..9,106.62 rows=268 width=200) (actual time=6,263.032..7,318.489 rows=714 loops=1)

  • Hash Cond: (mse.task_execution_id = mte.id)
7. 962.736 962.736 ↓ 1.0 55,731 1

CTE Scan on max_status_effective mse (cost=0.00..1,072.38 rows=53,619 width=24) (actual time=0.050..962.736 rows=55,731 loops=1)

8. 1.929 6,261.375 ↓ 714.0 714 1

Hash (cost=7,830.47..7,830.47 rows=1 width=176) (actual time=6,261.357..6,261.375 rows=714 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 266kB
9. 4.010 6,259.446 ↓ 714.0 714 1

Nested Loop (cost=7,820.77..7,830.47 rows=1 width=176) (actual time=1,736.862..6,259.446 rows=714 loops=1)

10. 4.502 6,253.294 ↓ 714.0 714 1

Nested Loop (cost=7,820.62..7,830.23 rows=1 width=128) (actual time=1,736.853..6,253.294 rows=714 loops=1)

  • Join Filter: ((rd.step ->> 'component'::text) = (c.name)::text)
11. 1,579.570 6,246.650 ↓ 714.0 714 1

Nested Loop (cost=7,820.47..7,830.03 rows=1 width=128) (actual time=1,736.841..6,246.650 rows=714 loops=1)

  • Join Filter: ((rd.step ->> 'version'::text) = (cr.version)::text)
  • Rows Removed by Join Filter: 290482
12. 997.099 3,211.100 ↓ 291,196.0 291,196 1

Merge Join (cost=7,820.19..7,821.72 rows=1 width=112) (actual time=1,736.784..3,211.100 rows=291,196 loops=1)

  • Merge Cond: ((rd.environment_id = mte.environment_id) AND (((rd.step ->> 'target'::text)) = (mte.target)::text) AND (((rd.step ->> 'name'::text)) = (mte.name)::text))
13. 4.805 10.086 ↓ 7.5 749 1

Sort (cost=13.27..13.52 rows=100 width=64) (actual time=8.856..10.086 rows=749 loops=1)

  • Sort Key: rd.environment_id, ((rd.step ->> 'target'::text)), ((rd.step ->> 'name'::text))
  • Sort Method: quicksort Memory: 291kB
14. 2.936 5.281 ↓ 7.5 749 1

Subquery Scan on rd (cost=0.42..9.95 rows=100 width=64) (actual time=1.018..5.281 rows=749 loops=1)

15. 2.315 2.345 ↓ 7.5 749 1

ProjectSet (cost=0.42..8.95 rows=100 width=64) (actual time=0.999..2.345 rows=749 loops=1)

16. 0.030 0.030 ↑ 1.0 1 1

Index Scan using release_plan_pkey on release_plan (cost=0.42..8.44 rows=1 width=348) (actual time=0.025..0.030 rows=1 loops=1)

  • Index Cond: (id = 'b9e0d055-3e03-4b55-9795-8f5d406252cb'::uuid)
17. 812.806 2,203.915 ↓ 291,197.0 291,197 1

Sort (cost=7,806.92..7,806.92 rows=1 width=65) (actual time=1,727.916..2,203.915 rows=291,197 loops=1)

  • Sort Key: mte.environment_id, mte.target USING <, mte.name USING <
  • Sort Method: external sort Disk: 4216kB
18. 168.469 1,391.109 ↓ 53,523.0 53,523 1

Subquery Scan on mte (cost=6,706.93..7,806.91 rows=1 width=65) (actual time=539.019..1,391.109 rows=53,523 loops=1)

19. 393.564 1,222.640 ↓ 53,523.0 53,523 1

Hash Join (cost=6,706.93..7,806.90 rows=1 width=137) (actual time=539.016..1,222.640 rows=53,523 loops=1)

  • 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. 280.408 372.284 ↓ 9.6 53,523 1

HashAggregate (cost=2,692.95..2,748.68 rows=5,573 width=57) (actual time=275.168..372.284 rows=53,523 loops=1)

  • Group Key: task_execution.component_release_id, task_execution.environment_id, task_execution.target, task_execution.name
22. 91.876 91.876 ↑ 1.0 55,731 1

Seq Scan on task_execution (cost=0.00..1,996.31 rows=55,731 width=57) (actual time=0.011..91.876 rows=55,731 loops=1)

23. 565.273 565.273 ↓ 9.6 53,523 1

CTE Scan on me (cost=0.00..111.46 rows=5,573 width=104) (actual time=275.173..565.273 rows=53,523 loops=1)

24. 166.355 263.803 ↑ 1.0 55,731 1

Hash (cost=1,996.31..1,996.31 rows=55,731 width=73) (actual time=263.802..263.803 rows=55,731 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3536kB
25. 97.448 97.448 ↑ 1.0 55,731 1

Seq Scan on task_execution te (cost=0.00..1,996.31 rows=55,731 width=73) (actual time=0.007..97.448 rows=55,731 loops=1)

26. 1,455.980 1,455.980 ↑ 1.0 1 291,196

Index Scan using componentrelease_pkey on component_release cr (cost=0.28..8.30 rows=1 width=41) (actual time=0.004..0.005 rows=1 loops=291,196)

  • Index Cond: (id = mte.component_release_id)
27. 2.142 2.142 ↑ 1.0 1 714

Index Scan using component_pkey on component c (cost=0.15..0.18 rows=1 width=64) (actual time=0.003..0.003 rows=1 loops=714)

  • Index Cond: (id = cr.component_id)
28. 2.142 2.142 ↑ 1.0 1 714

Index Scan using tenant_pkey on tenant t (cost=0.15..0.24 rows=1 width=48) (actual time=0.003..0.003 rows=1 loops=714)

  • Index Cond: (id = c.tenant_id)
29. 0.033 0.058 ↑ 72.2 9 1

Hash (cost=16.50..16.50 rows=650 width=64) (actual time=0.044..0.058 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.025 0.025 ↑ 72.2 9 1

Seq Scan on environment e (cost=0.00..16.50 rows=650 width=64) (actual time=0.009..0.025 rows=9 loops=1)

31. 3.570 3.570 ↑ 1.0 1 714

Index Scan using te_unique_execution_id_effective on task_execution_status ctes (cost=0.42..0.74 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=714)

  • Index Cond: ((task_execution_id = mse.task_execution_id) AND (effective = mse.max_effective))
Planning time : 11.581 ms
Execution time : 7,333.018 ms