explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6dYn

Settings
# exclusive inclusive rows x rows loops node
1. 3,866.787 17,155.586 ↑ 3.1 1,249,464 1

Merge Join (cost=223,321.06..284,021.03 rows=3,890,657 width=12) (actual time=10,672.922..17,155.586 rows=1,249,464 loops=1)

  • Output: atdc.transaction_id, atmc.metric_id, atdc.object_id
  • Merge Cond: (rc.object_id = atdc.object_id)
  • Buffers: shared hit=6471, local read=2019, temp read=9450 written=9033
2.          

CTE atdc

3. 1,583.657 1,583.657 ↓ 1.0 1,192,949 1

Seq Scan on castoncast_central.aed_transactions_details atdc (cost=0.00..21,428.10 rows=1,192,938 width=8) (actual time=0.167..1,583.657 rows=1,192,949 loops=1)

  • Output: atdc.transaction_id, atdc.object_id
  • Filter: (atdc.snapshot_id = 91)
  • Rows Removed by Filter: 3779
  • Buffers: shared hit=6469
4. 1,123.353 2,689.032 ↑ 1.2 401,089 1

Sort (cost=57,630.45..58,800.52 rows=468,028 width=8) (actual time=2,150.778..2,689.032 rows=401,089 loops=1)

  • Output: rc.object_id, atmc.metric_id
  • Sort Key: rc.object_id
  • Sort Method: quicksort Memory: 31090kB
  • Buffers: shared hit=2, local read=2019
5. 1,008.013 1,565.679 ↑ 1.2 401,089 1

Hash Join (cost=7.53..13,551.02 rows=468,028 width=8) (actual time=0.558..1,565.679 rows=401,089 loops=1)

  • Output: rc.object_id, atmc.metric_id
  • Hash Cond: (rc.metric_id = atmc.metric_id)
  • Buffers: shared hit=2, local read=2019
6. 557.226 557.226 ↑ 1.0 456,281 1

Seq Scan on pg_temp_3.tmp_res_cur rc (cost=0.00..6,581.81 rows=456,281 width=8) (actual time=0.089..557.226 rows=456,281 loops=1)

  • Output: rc.metric_id, rc.object_id
  • Buffers: local read=2019
7. 0.211 0.440 ↑ 1.0 179 1

Hash (cost=5.29..5.29 rows=179 width=4) (actual time=0.440..0.440 rows=179 loops=1)

  • Output: atmc.metric_id
  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
  • Buffers: shared hit=2
8. 0.229 0.229 ↑ 1.0 179 1

Seq Scan on castoncast_central.aed_transaction_metrics atmc (cost=0.00..5.29 rows=179 width=4) (actual time=0.009..0.229 rows=179 loops=1)

  • Output: atmc.metric_id
  • Filter: (atmc.snapshot_id = 91)
  • Rows Removed by Filter: 84
  • Buffers: shared hit=2
9. 5,912.518 10,599.767 ↓ 1.5 1,786,723 1

Sort (cost=144,262.52..147,244.86 rows=1,192,938 width=8) (actual time=8,517.504..10,599.767 rows=1,786,723 loops=1)

  • Output: atdc.transaction_id, atdc.object_id
  • Sort Key: atdc.object_id
  • Sort Method: external sort Disk: 25648kB
  • Buffers: shared hit=6469, temp read=7931 written=9033
10. 4,687.249 4,687.249 ↓ 1.0 1,192,949 1

CTE Scan on atdc (cost=0.00..23,858.76 rows=1,192,938 width=8) (actual time=0.177..4,687.249 rows=1,192,949 loops=1)

  • Output: atdc.transaction_id, atdc.object_id
  • Buffers: shared hit=6469, temp written=2621