explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5YeK : Optimization for: count distinct; plan #HOaE

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 227.138 14,532.815 ↑ 17.2 8,239 1

Merge Join (cost=131,729.49..142,404.84 rows=141,885 width=12) (actual time=13,672.973..14,532.815 rows=8,239 loops=1)

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

CTE c

3. 0.503 4.463 ↓ 4.7 207 1

Hash Join (cost=55.69..1,404.20 rows=44 width=4) (actual time=3.432..4.463 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.357..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.594 3.050 ↓ 1.5 1,277 1

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

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

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

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

Sort (cost=107,531.55..108,026.66 rows=198,045 width=8) (actual time=13,569.022..13,585.791 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,382.485 13,546.865 ↑ 12.3 16,144 1

Hash Join (cost=1.43..90,108.08 rows=198,045 width=8) (actual time=2,671.639..13,546.865 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,301.426 10,159.183 ↓ 1.2 3,116,620 1

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

  • Buffers: shared hit=34688
12. 775.016 775.016 ↑ 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.490..775.016 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,082.741 3,082.741 ↓ 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.013..3,082.741 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.260 5.197 ↓ 4.7 207 1

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

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

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

  • Output: c.metric_id
  • Buffers: shared hit=888
16. 393.364 619.725 ↓ 1.4 193,570 1

Merge Append (cost=0.01..7,993.90 rows=141,885 width=4) (actual time=0.048..619.725 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. 226.342 226.342 ↓ 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.023..226.342 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. 27.663 13,685.952 ↑ 14.5 12,550 1

Sort (cost=19,492.61..19,946.55 rows=181,578 width=12) (actual time=13,672.752..13,685.952 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
20. 13,658.289 13,658.289 ↑ 14.5 12,550 1

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

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