explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0QkX

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 20.521 17,795.737 ↑ 17.2 8,239 1

GroupAggregate (cost=115,867.34..128,946.30 rows=141,885 width=8) (actual time=16,741.829..17,795.737 rows=8,239 loops=1)

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

CTE c

3. 0.474 4.244 ↓ 4.7 207 1

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

  • Output: (ht.metric_id + 1)
  • Hash Cond: (ht.metric_id = t.metric_id)
  • Buffers: shared hit=888
4. 0.832 0.832 ↓ 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.331..0.832 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.512 2.938 ↓ 1.5 1,277 1

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

  • Output: t.metric_id
  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
  • Buffers: shared hit=17
6. 1.426 1.426 ↓ 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.010..1.426 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. 41.306 16,690.093 ↑ 14.7 13,433 1

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

  • Output: r.object_id, r.metric_id
  • Buffers: shared hit=9274 read=26302
9. 3,320.707 16,648.787 ↑ 12.3 16,144 1

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

  • Output: r.object_id, r.metric_id
  • Hash Cond: (r.metric_id = c.metric_id)
  • Buffers: shared hit=9274 read=26302
10. 6,136.289 13,323.137 ↓ 1.2 3,116,620 1

Append (cost=0.00..78,454.63 rows=2,579,086 width=8) (actual time=3.262..13,323.137 rows=3,116,620 loops=1)

  • Buffers: shared hit=8386 read=26302
11. 812.892 812.892 ↑ 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.258..812.892 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. 6,373.956 6,373.956 ↓ 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.167..6,373.956 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 read=26302
13. 0.251 4.943 ↓ 4.7 207 1

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

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

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

  • Output: c.metric_id
  • Buffers: shared hit=888
15. 215.950 17,775.216 ↑ 17.4 8,915 1

Merge Join (cost=21,384.38..32,270.74 rows=154,752 width=8) (actual time=16,738.100..17,775.216 rows=8,915 loops=1)

  • Output: r.metric_id, ra.object_id
  • Merge Cond: (ra.object_id = r.object_id)
  • Buffers: shared hit=9281 read=27044
16. 371.174 805.816 ↓ 1.4 193,570 1

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

  • Sort Key: ra.object_id
  • Buffers: shared hit=7 read=742
17. 0.020 0.020 ↓ 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.020..0.020 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. 434.622 434.622 ↓ 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.017..434.622 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=4 read=742
19. 33.288 16,753.450 ↑ 14.7 13,433 1

Sort (cost=21,384.37..21,879.49 rows=198,045 width=8) (actual time=16,737.879..16,753.450 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=9274 read=26302
20. 16,720.162 16,720.162 ↑ 14.7 13,433 1

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

  • Output: r.metric_id, r.object_id
  • Buffers: shared hit=9274 read=26302