explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Mcxs : original

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 21.476 14,302.481 ↑ 17.2 8,239 1

GroupAggregate (cost=115,867.34..128,946.30 rows=141,885 width=8) (actual time=13,444.058..14,302.481 rows=8,239 loops=1)

  • Output: ra.object_id, 67015, 1, count(r.metric_id), 91
  • Buffers: shared hit=36325
2.          

CTE c

3. 0.483 4.314 ↓ 4.7 207 1

Hash Join (cost=55.69..1,404.20 rows=44 width=4) (actual time=3.346..4.314 rows=207 loops=1)

  • Output: (ht.metric_id + 1)
  • Hash Cond: (ht.metric_id = t.metric_id)
  • Buffers: shared hit=888
4. 0.886 0.886 ↓ 1.4 207 1

Index Scan using dss_metr_hty_idx on castoncast_central.dss_metric_histo_tree ht (cost=0.00..1,347.43 rows=143 width=4) (actual time=0.373..0.886 rows=207 loops=1)

  • Output: ht.snapshot_id, ht.metric_parent_id, ht.metric_id, ht.metric_index, ht.metric_type, ht.aggregate_weight, ht.metric_critical
  • Index Cond: (ht.snapshot_id = 91)
  • Filter: (ht.metric_critical = 1)
  • Rows Removed by Filter: 1973
  • Buffers: shared hit=871
5. 1.542 2.945 ↓ 1.5 1,277 1

Hash (cost=45.09..45.09 rows=848 width=4) (actual time=2.945..2.945 rows=1,277 loops=1)

  • Output: t.metric_id
  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
  • Buffers: shared hit=17
6. 1.403 1.403 ↓ 1.5 1,277 1

Index Only Scan using dss_met_typ_idx2 on castoncast_central.dss_metric_types t (cost=0.00..45.09 rows=848 width=4) (actual time=0.013..1.403 rows=1,277 loops=1)

  • Output: t.metric_id
  • Index Cond: (t.metric_group = 1)
  • Heap Fetches: 0
  • Buffers: shared hit=17
7.          

CTE r

8. 40.592 13,395.541 ↑ 14.7 13,433 1

HashAggregate (cost=91,098.30..93,078.75 rows=198,045 width=8) (actual time=13,378.512..13,395.541 rows=13,433 loops=1)

  • Output: r.object_id, r.metric_id
  • Buffers: shared hit=35576
9. 3,335.590 13,354.949 ↑ 12.3 16,144 1

Hash Join (cost=1.43..90,108.08 rows=198,045 width=8) (actual time=2,681.753..13,354.949 rows=16,144 loops=1)

  • Output: r.object_id, r.metric_id
  • Hash Cond: (r.metric_id = c.metric_id)
  • Buffers: shared hit=35576
10. 6,201.252 10,014.345 ↓ 1.2 3,116,620 1

Append (cost=0.00..78,454.63 rows=2,579,086 width=8) (actual time=3.515..10,014.345 rows=3,116,620 loops=1)

  • Buffers: shared hit=34688
11. 790.490 790.490 ↑ 1.0 619,978 1

Seq Scan on castoncast_central.dss_metric_results r (cost=0.00..21,848.24 rows=624,319 width=8) (actual time=3.511..790.490 rows=619,978 loops=1)

  • Output: r.object_id, r.metric_id
  • Filter: ((r.snapshot_id = 91) AND (r.metric_value_index = 1))
  • Rows Removed by Filter: 277505
  • Buffers: shared hit=8386
12. 3,022.603 3,022.603 ↓ 1.3 2,496,642 1

Seq Scan on castoncast_central.dss_metric_results_91 r (cost=0.00..56,606.38 rows=1,954,767 width=8) (actual time=0.018..3,022.603 rows=2,496,642 loops=1)

  • Output: r.object_id, r.metric_id
  • Filter: ((r.snapshot_id = 91) AND (r.metric_value_index = 1))
  • Rows Removed by Filter: 63355
  • Buffers: shared hit=26302
13. 0.250 5.014 ↓ 4.7 207 1

Hash (cost=0.88..0.88 rows=44 width=4) (actual time=5.014..5.014 rows=207 loops=1)

  • Output: c.metric_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=888
14. 4.764 4.764 ↓ 4.7 207 1

CTE Scan on c (cost=0.00..0.88 rows=44 width=4) (actual time=3.352..4.764 rows=207 loops=1)

  • Output: c.metric_id
  • Buffers: shared hit=888
15. 222.409 14,281.005 ↑ 17.4 8,915 1

Merge Join (cost=21,384.38..32,270.74 rows=154,752 width=8) (actual time=13,443.601..14,281.005 rows=8,915 loops=1)

  • Output: r.metric_id, ra.object_id
  • Merge Cond: (ra.object_id = r.object_id)
  • Buffers: shared hit=36325
16. 381.492 599.496 ↓ 1.4 193,570 1

Merge Append (cost=0.01..7,993.90 rows=141,885 width=4) (actual time=0.046..599.496 rows=193,570 loops=1)

  • Sort Key: ra.object_id
  • Buffers: shared hit=749
17. 0.019 0.019 ↓ 0.0 0 1

Index Only Scan using dss_metr_res_idx2 on castoncast_central.dss_metric_results ra (cost=0.00..625.66 rows=164 width=4) (actual time=0.019..0.019 rows=0 loops=1)

  • Output: ra.object_id
  • Index Cond: ((ra.snapshot_id = 91) AND (ra.metric_id = 65005) AND (ra.metric_value_index = 1))
  • Heap Fetches: 0
  • Buffers: shared hit=3
18. 217.985 217.985 ↓ 1.4 193,570 1

Index Only Scan using dss_metric_results_91_snapshot_id_metric_id_object_id_metri_idx on castoncast_central.dss_metric_results_91 ra (cost=0.00..5,594.67 rows=141,721 width=4) (actual time=0.021..217.985 rows=193,570 loops=1)

  • Output: ra.object_id
  • Index Cond: ((ra.snapshot_id = 91) AND (ra.metric_id = 65005) AND (ra.metric_value_index = 1))
  • Heap Fetches: 0
  • Buffers: shared hit=746
19. 33.168 13,459.100 ↑ 14.7 13,433 1

Sort (cost=21,384.37..21,879.49 rows=198,045 width=8) (actual time=13,443.377..13,459.100 rows=13,433 loops=1)

  • Output: r.metric_id, r.object_id
  • Sort Key: r.object_id
  • Sort Method: quicksort Memory: 1014kB
  • Buffers: shared hit=35576
20. 13,425.932 13,425.932 ↑ 14.7 13,433 1

CTE Scan on r (cost=0.00..3,960.90 rows=198,045 width=8) (actual time=13,378.519..13,425.932 rows=13,433 loops=1)

  • Output: r.metric_id, r.object_id
  • Buffers: shared hit=35576