explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9ya8 : Optimization for: plan #0QkX

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 22.309 16,762.843 ↓ 41.2 8,239 1

GroupAggregate (cost=137,066.72..2,947,739.62 rows=200 width=8) (actual time=16,274.035..16,762.843 rows=8,239 loops=1)

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

CTE c

3. 0.523 15.337 ↓ 4.7 207 1

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

  • Output: (ht.metric_id + 1)
  • Hash Cond: (ht.metric_id = t.metric_id)
  • Buffers: shared hit=888 read=2
4. 1.609 1.609 ↓ 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.983..1.609 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 read=1
5. 1.658 13.205 ↓ 1.5 1,277 1

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

  • Output: t.metric_id
  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
  • Buffers: shared hit=17 read=1
6. 11.547 11.547 ↓ 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=9.881..11.547 rows=1,277 loops=1)

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

CTE r

8. 44.971 14,362.760 ↑ 14.7 13,433 1

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

  • Output: r.object_id, r.metric_id
  • Buffers: shared hit=35576 read=2
9. 3,543.854 14,317.789 ↑ 12.3 16,144 1

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

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

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

  • Buffers: shared hit=34688
11. 888.884 888.884 ↑ 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=5.934..888.884 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,262.258 3,262.258 ↓ 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.057..3,262.258 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.261 16.099 ↓ 4.7 207 1

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

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

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

  • Output: c.metric_id
  • Buffers: shared hit=888 read=2
15.          

CTE cc

16. 428.532 1,112.344 ↓ 1.4 193,627 1

Result (cost=0.00..6,220.33 rows=141,885 width=4) (actual time=3.235..1,112.344 rows=193,627 loops=1)

  • Output: ra.object_id
  • Buffers: shared hit=749 read=2
17. 424.063 683.812 ↓ 1.4 193,627 1

Append (cost=0.00..6,220.33 rows=141,885 width=4) (actual time=3.231..683.812 rows=193,627 loops=1)

  • Buffers: shared hit=749 read=2
18. 0.104 0.104 ↓ 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.104..0.104 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 read=1
19. 259.645 259.645 ↓ 1.4 193,627 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=3.119..259.645 rows=193,627 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 read=1
20. 242.598 16,740.534 ↑ 15,759.7 8,915 1

Merge Join (cost=36,363.43..2,144,543.96 rows=140,498,074 width=8) (actual time=16,273.789..16,740.534 rows=8,915 loops=1)

  • Output: r.object_id, r.metric_id
  • Merge Cond: (cc.object_id = r.object_id)
  • Buffers: shared hit=36325 read=4
21. 468.937 2,064.738 ↓ 1.4 193,570 1

Sort (cost=14,979.06..15,333.77 rows=141,885 width=4) (actual time=1,856.629..2,064.738 rows=193,570 loops=1)

  • Output: cc.object_id
  • Sort Key: cc.object_id
  • Sort Method: quicksort Memory: 15221kB
  • Buffers: shared hit=749 read=2
22. 1,595.801 1,595.801 ↓ 1.4 193,627 1

CTE Scan on cc (cost=0.00..2,837.70 rows=141,885 width=4) (actual time=3.252..1,595.801 rows=193,627 loops=1)

  • Output: cc.object_id
  • Buffers: shared hit=749 read=2
23. 35.934 14,433.198 ↑ 14.7 13,433 1

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

  • Output: r.object_id, r.metric_id
  • Sort Key: r.object_id
  • Sort Method: quicksort Memory: 1014kB
  • Buffers: shared hit=35576 read=2
24. 14,397.264 14,397.264 ↑ 14.7 13,433 1

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

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