explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LTgH

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=25,043,543.17..26,526,483.21 rows=74,147,002 width=116) (actual rows= loops=)

2.          

CTE last_msg

3. 0.000 0.000 ↓ 0.0

Unique (cost=1,515.89..1,587.96 rows=7,211 width=44) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=1,515.89..1,551.92 rows=14,415 width=44) (actual rows= loops=)

  • Sort Key: build_message.build_request_id, build_message.message_timestamp DESC
5. 0.000 0.000 ↓ 0.0

Seq Scan on build_message (cost=0.00..520.15 rows=14,415 width=44) (actual rows= loops=)

6.          

CTE build_msg

7. 0.000 0.000 ↓ 0.0

HashAggregate (cost=736.38..826.51 rows=7,211 width=64) (actual rows= loops=)

  • Group Key: build_message_1.build_request_id, build_message_1.recipe_id
8. 0.000 0.000 ↓ 0.0

Seq Scan on build_message build_message_1 (cost=0.00..520.15 rows=14,415 width=40) (actual rows= loops=)

9.          

CTE build_stats

10. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,212.60..10,348.41 rows=259,993 width=80) (actual rows= loops=)

  • Merge Cond: (build_msg.build_request_id = last_msg.build_request_id)
11. 0.000 0.000 ↓ 0.0

Sort (cost=606.30..624.33 rows=7,211 width=64) (actual rows= loops=)

  • Sort Key: build_msg.build_request_id
12. 0.000 0.000 ↓ 0.0

CTE Scan on build_msg (cost=0.00..144.22 rows=7,211 width=64) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Sort (cost=606.30..624.33 rows=7,211 width=20) (actual rows= loops=)

  • Sort Key: last_msg.build_request_id
14. 0.000 0.000 ↓ 0.0

CTE Scan on last_msg (cost=0.00..144.22 rows=7,211 width=20) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Sort (cost=25,030,780.28..25,216,147.79 rows=74,147,002 width=116) (actual rows= loops=)

  • Sort Key: build_stats.build_request_id, build_request_log.organization_id, build_request_log.project_id, build_request_log.user_id, build_request_log.recipe_id, ((build_request_log.recipe ->> 'platform_id'::text)), build_stats.status
16. 0.000 0.000 ↓ 0.0

Merge Join (cost=35,696.20..1,652,983.81 rows=74,147,002 width=116) (actual rows= loops=)

  • Merge Cond: (build_request_log.recipe_id = build_stats.recipe_id)
17. 0.000 0.000 ↓ 0.0

Index Scan using build_request_log_recipe_id on build_request_log (cost=0.42..317,756.50 rows=523,606 width=669) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Materialize (cost=35,695.78..36,995.74 rows=259,993 width=36) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Sort (cost=35,695.78..36,345.76 rows=259,993 width=36) (actual rows= loops=)

  • Sort Key: build_stats.recipe_id
20. 0.000 0.000 ↓ 0.0

CTE Scan on build_stats (cost=0.00..5,199.86 rows=259,993 width=36) (actual rows= loops=)