explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9AfB

Settings
# exclusive inclusive rows x rows loops node
1. 58.782 4,286.595 ↓ 0.0 0 1

Insert on pg_temp_3.tmp_df_updated (cost=643.74..643.83 rows=1 width=12) (actual time=4,286.595..4,286.595 rows=0 loops=1)

  • Buffers: shared hit=13402, local hit=1343 read=10 dirtied=8
2. 2,100.468 4,227.813 ↓ 1,332.0 1,332 1

Nested Loop (cost=643.74..643.82 rows=1 width=1,044) (actual time=0.193..4,227.813 rows=1,332 loops=1)

  • Output: current.object_id, CASE WHEN ((current.object_checksum <> current.prev_object_checksum) OR (((current.metric_char_value)::text ~~ '%Internal%'::text) AND ((previous.metric_char_value)::text ~~ '%External%'::text)) OR (((current.metric_char_value)::text ~~ '%External%'::text) AND ((previous.metric_char_value)::text ~~ '%Internal%'::text))) THEN 1 ELSE 0 END, (ltrim(split_part((current.metric_char_value)::text, ','::text, 1), 'DET: '::text))::integer
  • Join Filter: (current.object_id = previous.object_id)
  • Rows Removed by Join Filter: 1774224
  • Buffers: shared hit=13402
3.          

CTE currentdfp

4. 11.573 31.826 ↓ 1,332.0 1,332 1

Nested Loop (cost=0.00..321.88 rows=1 width=17) (actual time=0.114..31.826 rows=1,332 loops=1)

  • Output: dmr.object_id, t1.object_checksum, t1.prev_object_checksum, dmr.metric_char_value
  • Buffers: shared hit=6703
5. 2.924 2.924 ↓ 266.6 1,333 1

Index Scan using efp_dfinf_idx on castoncast_central.efp_df_info t1 (cost=0.00..10.24 rows=5 width=12) (actual time=0.030..2.924 rows=1,333 loops=1)

  • Output: t1.snapshot_id, t1.object_id, t1.object_checksum, t1.impact_factor, t1.status, t1.det, t1.calibration, t1.previous_snapshot_id, t1.prev_object_checksum
  • Index Cond: ((t1.snapshot_id = 225) AND (t1.previous_snapshot_id = 224))
  • Buffers: shared hit=31
6. 17.329 17.329 ↑ 1.0 1 1,333

Index Scan using dss_metr_res_idx2 on castoncast_central.dss_metric_results dmr (cost=0.00..62.32 rows=1 width=9) (actual time=0.011..0.013 rows=1 loops=1,333)

  • Output: dmr.metric_id, dmr.object_id, dmr.metric_value_index, dmr.metric_num_value, dmr.metric_char_value, dmr.metric_object_id, dmr.snapshot_id, dmr.position_id
  • Index Cond: ((dmr.snapshot_id = 225) AND (dmr.metric_id = 10203) AND (dmr.object_id = t1.object_id) AND (dmr.metric_value_index = 1))
  • Filter: ("position"((dmr.metric_char_value)::text, 'DET: '::text) <> 0)
  • Buffers: shared hit=6672
7.          

CTE previousdfp

8. 11.367 27.406 ↓ 1,333.0 1,333 1

Nested Loop (cost=0.00..321.86 rows=1 width=9) (actual time=0.040..27.406 rows=1,333 loops=1)

  • Output: d.object_id, d.metric_char_value
  • Buffers: shared hit=6699
9. 2.709 2.709 ↓ 266.6 1,333 1

Index Only Scan using efp_dfinf_idx on castoncast_central.efp_df_info i (cost=0.00..10.24 rows=5 width=4) (actual time=0.015..2.709 rows=1,333 loops=1)

  • Output: i.snapshot_id, i.previous_snapshot_id, i.object_id
  • Index Cond: ((i.snapshot_id = 224) AND (i.previous_snapshot_id = 223))
  • Heap Fetches: 1333
  • Buffers: shared hit=24
10. 13.330 13.330 ↑ 1.0 1 1,333

Index Scan using dss_metr_res_idx2 on castoncast_central.dss_metric_results d (cost=0.00..62.31 rows=1 width=9) (actual time=0.008..0.010 rows=1 loops=1,333)

  • Output: d.metric_id, d.object_id, d.metric_value_index, d.metric_num_value, d.metric_char_value, d.metric_object_id, d.snapshot_id, d.position_id
  • Index Cond: ((d.snapshot_id = 224) AND (d.metric_id = 10203) AND (d.object_id = i.object_id) AND (d.metric_value_index = 1))
  • Buffers: shared hit=6675
11. 36.105 36.105 ↓ 1,332.0 1,332 1

CTE Scan on currentdfp current (cost=0.00..0.02 rows=1 width=528) (actual time=0.122..36.105 rows=1,332 loops=1)

  • Output: current.object_id, current.object_checksum, current.prev_object_checksum, current.metric_char_value
  • Buffers: shared hit=6703
12. 2,091.240 2,091.240 ↓ 1,333.0 1,333 1,332

CTE Scan on previousdfp previous (cost=0.00..0.02 rows=1 width=520) (actual time=0.001..1.570 rows=1,333 loops=1,332)

  • Output: previous.object_id, previous.metric_char_value
  • Buffers: shared hit=6699