explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oL3j

Settings
# exclusive inclusive rows x rows loops node
1. 126.035 369,178.943 ↓ 0.0 0 1

Insert on bcg_leaseplan_central.dss_tree_ranking2 (cost=2,557,313.29..3,024,467.66 rows=2,626,506 width=72) (actual time=369,178.943..369,178.943 rows=0 loops=1)

  • Buffers: shared hit=8280 read=7 dirtied=264, local read=167294, temp read=116686 written=116686
2. 11.319 369,052.908 ↑ 1,677.2 1,566 1

Subquery Scan on *SELECT* (cost=2,557,313.29..3,024,467.66 rows=2,626,506 width=72) (actual time=261,919.116..369,052.908 rows=1,566 loops=1)

  • Output: "*SELECT*".i_snapshot_id, "*SELECT*".i_business_criterion_id, "*SELECT*".object_id, "*SELECT*".tree_level, "*SELECT*".tree_left_position, "*SELECT*".tree_right_position, "*SELECT*".count, "*SELECT*".count, "*SELECT*".count, "*SELECT*".count, "*SELECT*".count, "*SELECT*".count
  • Buffers: shared hit=80, local read=167294, temp read=116686 written=116686
3. 56,262.528 369,041.589 ↑ 1,677.2 1,566 1

GroupAggregate (cost=2,557,313.29..2,958,805.01 rows=2,626,506 width=28) (actual time=261,919.105..369,041.589 rows=1,566 loops=1)

  • Output: 8, 60012, ttv.object_id, ttv.tree_level, ttv.tree_left_position, ttv.tree_right_position, count(DISTINCT ttv.diag_id), count(DISTINCT ttv.initial_object_id), count(ttv.diag_id), count(DISTINCT CASE WHEN (tcqr.metric_id IS NOT NULL) THEN ttv.diag_id ELSE NULL::integer END), count(DISTINCT CASE WHEN (tcqr.metric_id IS NOT NULL) THEN ttv.initial_object_id ELSE NULL::integer END), count(CASE WHEN (tcqr.metric_id IS NOT NULL) THEN ttv.diag_id ELSE NULL::integer END)
  • Filter: (count(ttv.diag_id) >= 1000)
  • Rows Removed by Filter: 1908259
  • Buffers: shared hit=80, local read=167294, temp read=116686 written=116686
4. 152,179.130 312,779.061 ↓ 2.1 26,265,028 1

Sort (cost=2,557,313.29..2,588,034.99 rows=12,288,680 width=28) (actual time=248,762.946..312,779.061 rows=26,265,028 loops=1)

  • Output: ttv.object_id, ttv.tree_level, ttv.tree_left_position, ttv.tree_right_position, ttv.diag_id, ttv.initial_object_id, tcqr.metric_id
  • Sort Key: ttv.object_id, ttv.tree_level, ttv.tree_left_position, ttv.tree_right_position
  • Sort Method: external merge Disk: 872576kB
  • Buffers: shared hit=80, local read=167294, temp read=109116 written=109116
5. 58,719.216 160,599.931 ↓ 2.1 26,265,028 1

Hash Left Join (cost=79.31..900,257.93 rows=12,288,680 width=28) (actual time=0.831..160,599.931 rows=26,265,028 loops=1)

  • Output: ttv.object_id, ttv.tree_level, ttv.tree_left_position, ttv.tree_right_position, ttv.diag_id, ttv.initial_object_id, tcqr.metric_id
  • Hash Cond: (ttv.diag_id = tcqr.metric_id)
  • Buffers: shared hit=80, local read=167294
6. 62,468.353 101,880.308 ↓ 2.1 26,265,028 1

Hash Join (cost=11.21..848,324.37 rows=12,288,680 width=24) (actual time=0.404..101,880.308 rows=26,265,028 loops=1)

  • Output: ttv.object_id, ttv.tree_level, ttv.tree_left_position, ttv.tree_right_position, ttv.diag_id, ttv.initial_object_id
  • Hash Cond: (ttv.diag_id = dbc.diag_id)
  • Buffers: shared hit=7, local read=167294
7. 39,411.615 39,411.615 ↑ 1.0 26,265,028 1

Seq Scan on pg_temp_3.tmp_tree_violations ttv (cost=0.00..429,944.52 rows=26,265,052 width=24) (actual time=0.042..39,411.615 rows=26,265,028 loops=1)

  • Output: ttv.diag_id, ttv.initial_object_id, ttv.object_id, ttv.parent_object_id, ttv.tree_level, ttv.tree_left_position, ttv.tree_right_position
  • Buffers: local read=167294
8. 0.163 0.340 ↑ 1.0 121 1

Hash (cost=9.70..9.70 rows=121 width=4) (actual time=0.340..0.340 rows=121 loops=1)

  • Output: dbc.diag_id
  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
  • Buffers: shared hit=7
9. 0.156 0.177 ↑ 1.0 121 1

Bitmap Heap Scan on bcg_leaseplan_central.dss_business_criteria dbc (cost=3.19..9.70 rows=121 width=4) (actual time=0.032..0.177 rows=121 loops=1)

  • Output: dbc.diag_id
  • Recheck Cond: (dbc.business_criterion_id = 60012)
  • Buffers: shared hit=7
10. 0.021 0.021 ↑ 1.0 121 1

Bitmap Index Scan on dss_business_criteria_idx (cost=0.00..3.16 rows=121 width=0) (actual time=0.021..0.021 rows=121 loops=1)

  • Index Cond: (dbc.business_criterion_id = 60012)
  • Buffers: shared hit=2
11. 0.027 0.407 ↓ 5.2 21 1

Hash (cost=68.05..68.05 rows=4 width=4) (actual time=0.407..0.407 rows=21 loops=1)

  • Output: tcqr.metric_id
  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=73
12. 0.055 0.380 ↓ 5.2 21 1

HashAggregate (cost=67.97..68.01 rows=4 width=4) (actual time=0.357..0.380 rows=21 loops=1)

  • Output: tcqr.metric_id
  • Buffers: shared hit=73
13. 0.103 0.325 ↓ 5.2 21 1

Nested Loop (cost=0.00..67.96 rows=4 width=4) (actual time=0.046..0.325 rows=21 loops=1)

  • Output: tcqr.metric_id
  • Buffers: shared hit=73
14. 0.042 0.042 ↑ 1.0 15 1

Index Only Scan using dss_metr_hty_idx on bcg_leaseplan_central.dss_metric_histo_tree bctc (cost=0.00..2.55 rows=15 width=4) (actual time=0.025..0.042 rows=15 loops=1)

  • Output: bctc.snapshot_id, bctc.metric_parent_id, bctc.metric_id
  • Index Cond: ((bctc.snapshot_id = 8) AND (bctc.metric_parent_id = 60012))
  • Heap Fetches: 0
  • Buffers: shared hit=3
15. 0.180 0.180 ↑ 1.0 1 15

Index Scan using dss_metr_hty_idx on bcg_leaseplan_central.dss_metric_histo_tree tcqr (cost=0.00..4.35 rows=1 width=8) (actual time=0.007..0.012 rows=1 loops=15)

  • Output: tcqr.snapshot_id, tcqr.metric_parent_id, tcqr.metric_id, tcqr.metric_index, tcqr.metric_type, tcqr.aggregate_weight, tcqr.metric_critical
  • Index Cond: ((tcqr.snapshot_id = 8) AND (tcqr.metric_parent_id = bctc.metric_id))
  • Filter: (tcqr.metric_critical = 1)
  • Rows Removed by Filter: 22
  • Buffers: shared hit=70