explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mvQA

Settings
# exclusive inclusive rows x rows loops node
1. 3,915.174 16,859.227 ↑ 3.1 1,249,464 1

Merge Join (cost=223,651.86..284,585.85 rows=3,905,267 width=12) (actual time=10,296.007..16,859.227 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 hit=2019, temp read=9450 written=9033
2.          

CTE atdc

3. 1,542.991 1,542.991 ↓ 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.021..1,542.991 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,138.026 2,678.801 ↑ 1.2 401,089 1

Sort (cost=57,961.25..59,138.74 rows=470,997 width=8) (actual time=2,127.569..2,678.801 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 hit=2019
5. 1,024.037 1,540.775 ↑ 1.2 401,089 1

Hash Join (cost=7.53..13,580.71 rows=470,997 width=8) (actual time=0.736..1,540.775 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 hit=2019
6. 516.054 516.054 ↑ 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.015..516.054 rows=456,281 loops=1)

  • Output: rc.metric_id, rc.object_id
  • Buffers: local hit=2019
7. 0.336 0.684 ↑ 1.0 179 1

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

  • Output: atmc.metric_id
  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
  • Buffers: shared hit=2
8. 0.348 0.348 ↑ 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.016..0.348 rows=179 loops=1)

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

Sort (cost=144,262.52..147,244.86 rows=1,192,938 width=8) (actual time=8,163.881..10,265.252 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,551.526 4,551.526 ↓ 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.027..4,551.526 rows=1,192,949 loops=1)

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