explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VO7X

Settings
# exclusive inclusive rows x rows loops node
1. 9.010 500,858.180 ↓ 1.4 8,807 1

Unique (cost=101,277.76..101,399.88 rows=6,106 width=116) (actual time=500,844.888..500,858.180 rows=8,807 loops=1)

  • Buffers: shared hit=81926 read=547429 dirtied=2, temp read=199 written=185
2.          

CTE last_msg

3. 10.863 57.132 ↓ 1.0 6,615 1

Unique (cost=1,374.05..1,437.86 rows=6,385 width=44) (actual time=37.796..57.132 rows=6,615 loops=1)

  • Buffers: shared hit=376
4. 37.720 46.269 ↓ 1.0 13,224 1

Sort (cost=1,374.05..1,405.95 rows=12,763 width=44) (actual time=37.791..46.269 rows=13,224 loops=1)

  • Sort Key: build_message.build_request_id, build_message.message_timestamp DESC
  • Sort Method: quicksort Memory: 1418kB
  • Buffers: shared hit=376
5. 8.549 8.549 ↓ 1.0 13,224 1

Seq Scan on build_message (cost=0.00..503.63 rows=12,763 width=44) (actual time=0.015..8.549 rows=13,224 loops=1)

  • Buffers: shared hit=376
6.          

CTE build_msg

7. 23.847 32.780 ↓ 1.0 6,615 1

HashAggregate (cost=695.08..774.89 rows=6,385 width=64) (actual time=27.701..32.780 rows=6,615 loops=1)

  • Group Key: build_message_1.build_request_id, build_message_1.recipe_id
  • Buffers: shared hit=376
8. 8.933 8.933 ↓ 1.0 13,224 1

Seq Scan on build_message build_message_1 (cost=0.00..503.63 rows=12,763 width=40) (actual time=0.013..8.933 rows=13,224 loops=1)

  • Buffers: shared hit=376
9.          

CTE build_stats

10. 15.453 142.045 ↑ 30.8 6,615 1

Merge Join (cost=1,062.49..8,228.85 rows=203,841 width=80) (actual time=120.552..142.045 rows=6,615 loops=1)

  • Merge Cond: (build_msg.build_request_id = last_msg.build_request_id)
  • Buffers: shared hit=752
11. 9.148 50.291 ↓ 1.0 6,615 1

Sort (cost=531.25..547.21 rows=6,385 width=64) (actual time=46.924..50.291 rows=6,615 loops=1)

  • Sort Key: build_msg.build_request_id
  • Sort Method: quicksort Memory: 1123kB
  • Buffers: shared hit=376
12. 41.143 41.143 ↓ 1.0 6,615 1

CTE Scan on build_msg (cost=0.00..127.70 rows=6,385 width=64) (actual time=27.705..41.143 rows=6,615 loops=1)

  • Buffers: shared hit=376
13. 8.866 76.301 ↓ 1.0 6,615 1

Sort (cost=531.25..547.21 rows=6,385 width=20) (actual time=73.617..76.301 rows=6,615 loops=1)

  • Sort Key: last_msg.build_request_id
  • Sort Method: quicksort Memory: 709kB
  • Buffers: shared hit=376
14. 67.435 67.435 ↓ 1.0 6,615 1

CTE Scan on last_msg (cost=0.00..127.70 rows=6,385 width=20) (actual time=37.939..67.435 rows=6,615 loops=1)

  • Buffers: shared hit=376
15. 77.008 500,849.170 ↓ 2.4 14,455 1

Sort (cost=90,836.16..90,851.42 rows=6,106 width=116) (actual time=500,844.886..500,849.170 rows=14,455 loops=1)

  • 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
  • Sort Method: quicksort Memory: 2409kB
  • Buffers: shared hit=81926 read=547429 dirtied=2, temp read=199 written=185
16. 498,251.846 500,772.162 ↓ 2.4 14,455 1

Hash Join (cost=9,217.83..90,452.21 rows=6,106 width=116) (actual time=247.133..500,772.162 rows=14,455 loops=1)

  • Hash Cond: (build_request_log.recipe_id = build_stats.recipe_id)
  • Buffers: shared hit=81926 read=547429 dirtied=2, temp read=199 written=185
17. 0.000 2,361.900 ↓ 303.9 15,801 1

Gather (cost=1,000.00..79,229.97 rows=52 width=699) (actual time=74.503..2,361.900 rows=15,801 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=1 read=75522 dirtied=2
18. 7,870.506 7,870.506 ↓ 239.4 5,267 3

Parallel Seq Scan on build_request_log (cost=0.00..78,224.77 rows=22 width=699) (actual time=58.487..7,870.506 rows=5,267 loops=3)

  • Filter: (request_timestamp >= '2019-11-01 00:00:00+00'::timestamp with time zone)
  • Rows Removed by Filter: 167843
  • Buffers: shared hit=1 read=75522 dirtied=2
19. 8.075 158.416 ↑ 30.8 6,615 1

Hash (cost=4,076.82..4,076.82 rows=203,841 width=36) (actual time=158.416..158.416 rows=6,615 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 575kB
  • Buffers: shared hit=752, temp written=36
20. 150.341 150.341 ↑ 30.8 6,615 1

CTE Scan on build_stats (cost=0.00..4,076.82 rows=203,841 width=36) (actual time=120.556..150.341 rows=6,615 loops=1)

  • Buffers: shared hit=752
Planning time : 0.577 ms
Execution time : 500,861.493 ms