explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hm4i

Settings
# exclusive inclusive rows x rows loops node
1. 19.291 79,141.141 ↓ 0.0 0 1

Insert on bcg_leaseplan_central.dss_tree_ranking2 (cost=1,394,779.85..1,595,024.60 rows=645,951 width=72) (actual time=79,141.141..79,141.141 rows=0 loops=1)

  • Buffers: shared hit=2515 read=4 dirtied=190, local hit=92038 read=416255, temp read=14222 written=14222
2. 3.551 79,121.850 ↑ 1,340.1 482 1

Subquery Scan on *SELECT* (cost=1,394,779.85..1,595,024.60 rows=645,951 width=72) (actual time=70,801.981..79,121.850 rows=482 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=63 read=3, local hit=92038 read=416255, temp read=14222 written=14222
3. 5,858.999 79,118.299 ↑ 1,340.1 482 1

GroupAggregate (cost=1,394,779.85..1,578,875.83 rows=645,951 width=28) (actual time=70,801.963..79,118.299 rows=482 loops=1)

  • Output: 8, 60016, 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=63 read=3, local hit=92038 read=416255, temp read=14222 written=14222
4.          

CTE node

5. 10,386.922 33,426.078 ↑ 43.8 482 1

HashAggregate (cost=212,704.03..212,967.67 rows=21,091 width=8) (actual time=32,958.776..33,426.078 rows=482 loops=1)

  • Output: ttv.tree_left_position
  • Filter: (count(ttv.diag_id) >= 1000)
  • Rows Removed by Filter: 883621
  • Buffers: shared hit=7, local read=41144
6. 14,991.286 23,039.156 ↓ 2.8 6,459,529 1

Hash Join (cost=10.53..201,275.71 rows=2,285,664 width=8) (actual time=0.354..23,039.156 rows=6,459,529 loops=1)

  • Output: ttv.tree_left_position, ttv.diag_id
  • Hash Cond: (ttv.diag_id = dbc.diag_id)
  • Buffers: shared hit=7, local read=41144
7. 8,047.589 8,047.589 ↓ 1.0 6,459,529 1

Seq Scan on pg_temp_4.tmp_tree_violations ttv (cost=0.00..105,739.08 rows=6,459,508 width=8) (actual time=0.048..8,047.589 rows=6,459,529 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=41144
8. 0.121 0.281 ↑ 1.0 100 1

Hash (cost=9.28..9.28 rows=100 width=4) (actual time=0.281..0.281 rows=100 loops=1)

  • Output: dbc.diag_id
  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
  • Buffers: shared hit=7
9. 0.139 0.160 ↑ 1.0 100 1

Bitmap Heap Scan on bcg_leaseplan_central.dss_business_criteria dbc (cost=3.03..9.28 rows=100 width=4) (actual time=0.032..0.160 rows=100 loops=1)

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

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

  • Index Cond: (dbc.business_criterion_id = 60016)
  • Buffers: shared hit=2
11. 12,975.507 73,259.300 ↑ 1.9 3,355,393 1

Sort (cost=1,181,812.18..1,197,960.95 rows=6,459,508 width=28) (actual time=68,646.227..73,259.300 rows=3,355,393 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: 113736kB
  • Buffers: shared hit=63 read=3, local hit=92038 read=416255, temp read=14222 written=14222
12. 7,622.835 60,283.793 ↑ 1.9 3,355,393 1

Hash Left Join (cost=54.99..340,752.67 rows=6,459,508 width=28) (actual time=32,981.936..60,283.793 rows=3,355,393 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=63 read=3, local hit=92038 read=416255
13. 7,463.503 52,638.029 ↑ 1.9 3,355,393 1

Nested Loop (cost=0.00..314,021.54 rows=6,459,508 width=24) (actual time=32,958.973..52,638.029 rows=3,355,393 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=7, local hit=92038 read=416255
14. 33,429.150 33,429.150 ↑ 43.8 482 1

CTE Scan on node (cost=0.00..421.82 rows=21,091 width=4) (actual time=32,958.786..33,429.150 rows=482 loops=1)

  • Output: node.tree_left_position
  • Buffers: shared hit=7, local read=41144
15. 11,745.376 11,745.376 ↓ 22.7 6,961 482

Index Scan using tmp_tree_violations_idx on pg_temp_4.tmp_tree_violations ttv (cost=0.00..11.81 rows=306 width=24) (actual time=0.119..24.368 rows=6,961 loops=482)

  • 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=92038 read=375111
16. 0.177 22.929 ↓ 48.7 146 1

Hash (cost=54.95..54.95 rows=3 width=4) (actual time=22.929..22.929 rows=146 loops=1)

  • Output: tcqr.metric_id
  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
  • Buffers: shared hit=56 read=3
17. 0.385 22.752 ↓ 48.7 146 1

HashAggregate (cost=54.89..54.92 rows=3 width=4) (actual time=22.592..22.752 rows=146 loops=1)

  • Output: tcqr.metric_id
  • Buffers: shared hit=56 read=3
18. 0.369 22.367 ↓ 48.7 146 1

Nested Loop (cost=0.00..54.89 rows=3 width=4) (actual time=0.057..22.367 rows=146 loops=1)

  • Output: tcqr.metric_id
  • Buffers: shared hit=56 read=3
19. 0.042 0.042 ↑ 1.0 11 1

Index Only Scan using dss_metr_hty_idx on bcg_leaseplan_central.dss_metric_histo_tree bctc (cost=0.00..2.47 rows=11 width=4) (actual time=0.023..0.042 rows=11 loops=1)

  • Output: bctc.snapshot_id, bctc.metric_parent_id, bctc.metric_id
  • Index Cond: ((bctc.snapshot_id = 8) AND (bctc.metric_parent_id = 60016))
  • Heap Fetches: 0
  • Buffers: shared hit=3
20. 21.956 21.956 ↓ 13.0 13 11

Index Scan using dss_metr_hty_idx on bcg_leaseplan_central.dss_metric_histo_tree tcqr (cost=0.00..4.76 rows=1 width=8) (actual time=0.012..1.996 rows=13 loops=11)

  • 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: 23
  • Buffers: shared hit=53 read=3