explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c3FK

Settings
# exclusive inclusive rows x rows loops node
1. 528.512 3,516.470 ↓ 0.0 0 1

Insert on pg_temp_3.tmp_df_updated (cost=39,472.43..39,472.56 rows=1 width=12) (actual time=3,516.470..3,516.470 rows=0 loops=1)

  • Buffers: shared hit=279511, local hit=251334 read=1347 dirtied=1345
2. 935.983 2,987.958 ↓ 248,649.0 248,649 1

Hash Join (cost=39,472.43..39,472.55 rows=1 width=1,044) (actual time=57.990..2,987.958 rows=248,649 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
  • Hash Cond: (previous.object_id = current.object_id)
  • Buffers: shared hit=279511
3.          

CTE currentdfp

4. 9.235 27.159 ↓ 1,332.0 1,332 1

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

  • Output: dmr.object_id, t1.object_checksum, t1.prev_object_checksum, dmr.metric_char_value
  • Buffers: shared hit=6704
5. 1.928 1.928 ↓ 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.017..1.928 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. 15.996 15.996 ↑ 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.012 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=6673
7.          

CTE previousdfp

8. 562.989 1,361.014 ↓ 62,168.8 248,675 1

Nested Loop (cost=0.00..39,150.51 rows=4 width=9) (actual time=0.791..1,361.014 rows=248,675 loops=1)

  • Output: d.object_id, d.metric_char_value
  • Buffers: shared hit=272804
9. 3.557 3.557 ↓ 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.722..3.557 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=25
10. 794.468 794.468 ↓ 187.0 187 1,333

Index Scan using dss_metr_res_idx on castoncast_central.dss_metric_results d (cost=0.00..7,828.04 rows=1 width=9) (actual time=0.013..0.596 rows=187 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.object_id = i.object_id)
  • Filter: ((d.metric_id = 10203) AND (d.metric_value_index = 1))
  • Rows Removed by Filter: 14
  • Buffers: shared hit=272779
11. 2,019.193 2,019.193 ↓ 62,168.8 248,675 1

CTE Scan on previousdfp previous (cost=0.00..0.08 rows=4 width=520) (actual time=8.579..2,019.193 rows=248,675 loops=1)

  • Output: previous.object_id, previous.metric_char_value
  • Buffers: shared hit=272804
12. 2.087 32.782 ↓ 1,332.0 1,332 1

Hash (cost=0.02..0.02 rows=1 width=528) (actual time=32.782..32.782 rows=1,332 loops=1)

  • Output: current.object_id, current.object_checksum, current.prev_object_checksum, current.metric_char_value
  • Buckets: 1024 Batches: 1 Memory Usage: 102kB
  • Buffers: shared hit=6704
13. 30.695 30.695 ↓ 1,332.0 1,332 1

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

  • Output: current.object_id, current.object_checksum, current.prev_object_checksum, current.metric_char_value
  • Buffers: shared hit=6704
Total runtime : 3,523.999 ms