explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aPGA

Settings
# exclusive inclusive rows x rows loops node
1. 1,976.540 428,628.044 ↓ 0.0 0 1

Insert on bcg_leaseplan_central.dss_tree_ranking2 (cost=5,960,478.77..6,774,695.41 rows=2,626,506 width=72) (actual time=428,628.044..428,628.044 rows=0 loops=1)

  • Buffers: shared hit=7999 read=271 dirtied=252, local hit=239217 read=2728274, temp read=71605 written=71605
2. 12.791 426,651.504 ↑ 1,677.2 1,566 1

Subquery Scan on *SELECT* (cost=5,960,478.77..6,774,695.41 rows=2,626,506 width=72) (actual time=365,901.047..426,651.504 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=53 read=28, local hit=239217 read=2728274, temp read=71605 written=71605
3. 29,282.670 426,638.713 ↑ 1,677.2 1,566 1

GroupAggregate (cost=5,960,478.77..6,709,032.76 rows=2,626,506 width=28) (actual time=365,901.028..426,638.713 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)
  • Buffers: shared hit=53 read=28, local hit=239217 read=2728274, temp read=71605 written=71605
4.          

CTE node

5. 43,163.537 144,886.817 ↑ 13.5 1,566 1

HashAggregate (cost=909,242.24..909,507.02 rows=21,182 width=8) (actual time=143,614.727..144,886.817 rows=1,566 loops=1)

  • Output: ttv.tree_left_position
  • Filter: (count(ttv.diag_id) >= 1000)
  • Rows Removed by Filter: 1908259
  • Buffers: shared hit=1 read=6, local read=167294
6. 59,373.016 101,723.280 ↓ 2.1 26,265,028 1

Hash Join (cost=11.21..847,974.02 rows=12,253,645 width=8) (actual time=0.661..101,723.280 rows=26,265,028 loops=1)

  • Output: ttv.tree_left_position, ttv.diag_id
  • Hash Cond: (ttv.diag_id = dbc.diag_id)
  • Buffers: shared hit=1 read=6, local read=167294
7. 42,349.775 42,349.775 ↑ 1.0 26,265,028 1

Seq Scan on pg_temp_4.tmp_tree_violations ttv (cost=0.00..429,944.52 rows=26,265,052 width=8) (actual time=0.144..42,349.775 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.145 0.489 ↑ 1.0 121 1

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

  • Output: dbc.diag_id
  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
  • Buffers: shared hit=1 read=6
9. 0.249 0.344 ↑ 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.132..0.344 rows=121 loops=1)

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

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

  • Index Cond: (dbc.business_criterion_id = 60012)
  • Buffers: shared read=2
11. 98,917.728 397,356.043 ↑ 1.7 15,414,668 1

Sort (cost=5,050,971.75..5,116,634.38 rows=26,265,052 width=28) (actual time=354,628.851..397,356.043 rows=15,414,668 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: 512136kB
  • Buffers: shared hit=53 read=28, local hit=239217 read=2728274, temp read=64036 written=64036
12. 33,765.094 298,438.315 ↑ 1.7 15,414,668 1

Hash Left Join (cost=68.10..1,365,377.70 rows=26,265,052 width=28) (actual time=143,624.720..298,438.315 rows=15,414,668 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=53 read=28, local hit=239217 read=2728274
13. 33,266.472 264,663.538 ↑ 1.7 15,414,668 1

Nested Loop (cost=0.00..1,255,518.86 rows=26,265,052 width=24) (actual time=143,614.997..264,663.538 rows=15,414,668 loops=1)

  • Output: ttv.object_id, ttv.tree_level, ttv.tree_left_position, ttv.tree_right_position, ttv.diag_id, ttv.initial_object_id
  • Buffers: shared hit=1 read=6, local hit=239217 read=2728274
14. 144,895.924 144,895.924 ↑ 13.5 1,566 1

CTE Scan on node (cost=0.00..423.64 rows=21,182 width=4) (actual time=143,614.736..144,895.924 rows=1,566 loops=1)

  • Output: node.tree_left_position
  • Buffers: shared hit=1 read=6, local read=167294
15. 86,501.142 86,501.142 ↓ 7.9 9,843 1,566

Index Scan using tmp_tree_violations_idx on pg_temp_4.tmp_tree_violations ttv (cost=0.00..46.85 rows=1,240 width=24) (actual time=3.057..55.237 rows=9,843 loops=1,566)

  • 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
  • Index Cond: (ttv.tree_left_position = node.tree_left_position)
  • Buffers: local hit=239217 read=2560980
16. 0.030 9.683 ↓ 5.2 21 1

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

  • Output: tcqr.metric_id
  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=52 read=22
17. 0.061 9.653 ↓ 5.2 21 1

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

  • Output: tcqr.metric_id
  • Buffers: shared hit=52 read=22
18. 0.116 9.592 ↓ 5.2 21 1

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

  • Output: tcqr.metric_id
  • Buffers: shared hit=52 read=22
19. 8.756 8.756 ↑ 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=8.738..8.756 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 read=4
20. 0.720 0.720 ↑ 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.024..0.048 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=52 read=18