explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HOaE : count distinct

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 242.444 16,254.783 ↑ 15,634.9 8,239 1

Merge Join (cost=152,928.87..2,085,877.89 rows=128,815,973 width=12) (actual time=15,789.249..16,254.783 rows=8,239 loops=1)

  • Output: r.object_id, 67015, 1, r.metric_id, 91
  • Merge Cond: (cc.object_id = r.object_id)
  • Buffers: shared hit=36325
2.          

CTE c

3. 0.481 4.247 ↓ 4.7 207 1

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

  • Output: (ht.metric_id + 1)
  • Hash Cond: (ht.metric_id = t.metric_id)
  • Buffers: shared hit=888
4. 0.910 0.910 ↓ 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.378..0.910 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.416 2.856 ↓ 1.5 1,277 1

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

  • Output: t.metric_id
  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
  • Buffers: shared hit=17
6. 1.440 1.440 ↓ 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.018..1.440 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. 44.119 13,995.861 ↑ 14.5 12,550 1

GroupAggregate (cost=107,531.55..110,832.67 rows=181,578 width=8) (actual time=13,934.870..13,995.861 rows=12,550 loops=1)

  • Output: r.object_id, count(DISTINCT r.metric_id)
  • Buffers: shared hit=35576
9. 38.984 13,951.742 ↑ 12.3 16,144 1

Sort (cost=107,531.55..108,026.66 rows=198,045 width=8) (actual time=13,934.811..13,951.742 rows=16,144 loops=1)

  • Output: r.object_id, r.metric_id
  • Sort Key: r.object_id
  • Sort Method: quicksort Memory: 1141kB
  • Buffers: shared hit=35576
10. 3,452.956 13,912.758 ↑ 12.3 16,144 1

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

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

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

  • Buffers: shared hit=34688
12. 831.756 831.756 ↑ 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.521..831.756 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
13. 3,187.800 3,187.800 ↓ 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.058..3,187.800 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
14. 0.240 4.962 ↓ 4.7 207 1

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

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

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

  • Output: c.metric_id
  • Buffers: shared hit=888
16.          

CTE cc

17. 408.990 1,053.408 ↓ 1.4 193,627 1

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

  • Output: ra.object_id
  • Buffers: shared hit=749
18. 403.318 644.418 ↓ 1.4 193,627 1

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

  • Buffers: shared hit=749
19. 0.024 0.024 ↓ 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.024..0.024 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
20. 241.076 241.076 ↓ 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=0.192..241.076 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
21. 445.602 1,959.280 ↓ 1.4 193,570 1

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

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

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

  • Output: cc.object_id
  • Buffers: shared hit=749
23. 28.633 14,053.059 ↑ 14.5 12,550 1

Sort (cost=19,492.61..19,946.55 rows=181,578 width=12) (actual time=14,038.818..14,053.059 rows=12,550 loops=1)

  • Output: r.object_id, r.metric_id
  • Sort Key: r.object_id
  • Sort Method: quicksort Memory: 973kB
  • Buffers: shared hit=35576
24. 14,024.426 14,024.426 ↑ 14.5 12,550 1

CTE Scan on r (cost=0.00..3,631.56 rows=181,578 width=12) (actual time=13,934.876..14,024.426 rows=12,550 loops=1)

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