explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 05U4

Settings
# exclusive inclusive rows x rows loops node
1. 5,145.010 117,731.403 ↓ 144.2 410,983 1

HashAggregate (cost=769,183.43..769,219.05 rows=2,850 width=878) (actual time=116,869.488..117,731.403 rows=410,983 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, os.object_status, p.pri_60011, p.pri_60012, p.pri_60013, p.pri_60014, p.pri_60016, t.metric_name, o.object_full_name, o.object_name, temp.techno_type_id, string_agg((os.module_id)::text, ' '::text), tran.transaction_ids, er.user_name, ap.first_snapshot_date, ap.last_snapshot_date
  • Buffers: shared hit=6579548, local hit=3998743 read=15537 written=2285
2. 40,443.952 112,586.393 ↓ 355.5 1,013,206 1

Nested Loop Left Join (cost=427,135.68..769,026.68 rows=2,850 width=878) (actual time=1,417.734..112,586.393 rows=1,013,206 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, p.pri_60011, p.pri_60012, p.pri_60013, p.pri_60014, p.pri_60016, t.metric_name, o.object_full_name, o.object_name, temp.techno_type_id, os.object_status, os.module_id, tran.transaction_ids, ap.first_snapshot_date, ap.last_snapshot_date, er.user_name
  • Join Filter: ((er.metric_id = vs.diag_id) AND (er.object_id = vs.object_id))
  • Rows Removed by Join Filter: 30396180
  • Buffers: shared hit=6579548, local hit=3984809 read=15263 written=2280
3. 4,491.394 37,693.437 ↓ 355.5 1,013,206 1

Nested Loop (cost=427,135.68..767,529.05 rows=2,850 width=362) (actual time=1,417.521..37,693.437 rows=1,013,206 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, p.pri_60011, p.pri_60012, p.pri_60013, p.pri_60014, p.pri_60016, t.metric_name, o.object_full_name, o.object_name, temp.techno_type_id, os.object_status, os.module_id, tran.transaction_ids, ap.first_snapshot_date, ap.last_snapshot_date
  • Buffers: shared hit=6579547, local hit=3984809 read=15263 written=2280
4. 3,191.489 29,149.227 ↓ 367.1 506,602 1

Nested Loop Left Join (cost=427,135.68..762,858.74 rows=1,380 width=370) (actual time=1,417.322..29,149.227 rows=506,602 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, p.pri_60011, p.pri_60012, p.pri_60013, p.pri_60014, p.pri_60016, p.object_id, t.metric_name, o.object_full_name, o.object_name, o.object_id, os.object_status, os.module_id, os.object_id, tran.transaction_ids, ap.first_snapshot_date, ap.last_snapshot_date
  • Buffers: shared hit=6579547, local hit=1460968 read=3039 written=114
5. 2,989.231 24,944.538 ↓ 367.1 506,600 1

Nested Loop Left Join (cost=427,135.68..759,084.52 rows=1,380 width=354) (actual time=1,417.299..24,944.538 rows=506,600 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, p.pri_60011, p.pri_60012, p.pri_60013, p.pri_60014, p.pri_60016, p.object_id, t.metric_name, o.object_full_name, o.object_name, o.object_id, os.object_status, os.module_id, os.object_id, tran.transaction_ids
  • Buffers: shared hit=5553091, local hit=1460968 read=3039 written=114
6. 3,108.744 20,606.342 ↓ 325.8 449,655 1

Nested Loop (cost=427,135.68..754,461.57 rows=1,380 width=218) (actual time=1,416.872..20,606.342 rows=449,655 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, p.pri_60011, p.pri_60012, p.pri_60013, p.pri_60014, p.pri_60016, p.object_id, t.metric_name, o.object_full_name, o.object_name, o.object_id, os.object_status, os.module_id, os.object_id
  • Join Filter: (p.object_id = o.object_id)
  • Buffers: shared hit=5553091
7. 3,150.631 15,249.323 ↓ 325.8 449,655 1

Nested Loop (cost=427,135.68..750,532.90 rows=1,380 width=125) (actual time=1,416.840..15,249.323 rows=449,655 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, p.pri_60011, p.pri_60012, p.pri_60013, p.pri_60014, p.pri_60016, p.object_id, t.metric_name, os.object_status, os.module_id, os.object_id
  • Buffers: shared hit=3752188
8. 3,226.242 10,300.072 ↓ 325.8 449,655 1

Nested Loop (cost=427,135.68..746,938.66 rows=1,380 width=76) (actual time=1,416.816..10,300.072 rows=449,655 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, p.pri_60011, p.pri_60012, p.pri_60013, p.pri_60014, p.pri_60016, p.object_id, os.object_status, os.module_id, os.object_id
  • Buffers: shared hit=2403197
9. 1,943.059 4,825.555 ↓ 217.3 449,655 1

Merge Join (cost=427,135.68..736,735.09 rows=2,069 width=32) (actual time=1,416.779..4,825.555 rows=449,655 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, os.object_status, os.module_id, os.object_id
  • Merge Cond: ((vs.previous_snapshot_id = os.previous_snapshot_id) AND (vs.object_id = os.object_id))
  • Buffers: shared hit=152556
10. 1,344.191 1,966.920 ↑ 1.1 410,981 1

Sort (cost=427,107.15..428,281.00 rows=469,540 width=20) (actual time=1,416.365..1,966.920 rows=410,981 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status
  • Sort Key: vs.previous_snapshot_id, vs.object_id
  • Sort Method: quicksort Memory: 44396kB
  • Buffers: shared hit=5511
11. 622.729 622.729 ↑ 1.1 410,981 1

Index Scan using dss_violation_status_idx1 on castoncast_central.dss_violation_statuses vs (cost=0.00..382,874.40 rows=469,540 width=20) (actual time=0.036..622.729 rows=410,981 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status
  • Index Cond: (vs.snapshot_id = 159)
  • Filter: (vs.violation_status <> 2)
  • Rows Removed by Filter: 15398
  • Buffers: shared hit=5511
12. 915.576 915.576 ↓ 1.6 634,352 1

Index Scan using dss_objects_statuses_idx2 on castoncast_central.dss_objects_statuses os (cost=0.00..304,182.71 rows=386,302 width=16) (actual time=0.047..915.576 rows=634,352 loops=1)

  • Output: os.object_id, os.module_id, os.snapshot_id, os.previous_snapshot_id, os.object_status, os.is_art, os.cost_complexity, os.techno_type_id
  • Index Cond: (os.snapshot_id = 159)
  • Buffers: shared hit=147045
13. 2,248.275 2,248.275 ↑ 1.0 1 449,655

Index Scan using dss_pri_idx1 on castoncast_central.dss_pri p (cost=0.00..4.92 rows=1 width=44) (actual time=0.004..0.005 rows=1 loops=449,655)

  • Output: p.object_id, p.snapshot_id, p.pri_60011, p.pri_60012, p.pri_60013, p.pri_60014, p.pri_60016
  • Index Cond: ((p.snapshot_id = 159) AND (p.object_id = vs.object_id))
  • Buffers: shared hit=2250641
14. 1,798.620 1,798.620 ↑ 1.0 1 449,655

Index Scan using dss_metr_typ_pk on castoncast_central.dss_metric_types t (cost=0.00..2.59 rows=1 width=53) (actual time=0.003..0.004 rows=1 loops=449,655)

  • Output: t.metric_id, t.metric_name, t.metric_description, t.metric_type, t.metric_group, t.metric_depends_on, t.metric_options, t.scope_id, t.property_id
  • Index Cond: (t.metric_id = vs.diag_id)
  • Buffers: shared hit=1348991
15. 2,248.275 2,248.275 ↑ 1.0 1 449,655

Index Scan using dss_objects_idx3 on castoncast_central.dss_objects o (cost=0.00..2.83 rows=1 width=93) (actual time=0.003..0.005 rows=1 loops=449,655)

  • Output: o.object_id, o.object_type_id, o.object_name, o.object_description, o.object_full_name
  • Index Cond: (o.object_id = vs.object_id)
  • Buffers: shared hit=1800903
16. 1,348.965 1,348.965 ↓ 0.0 0 449,655

Index Scan using idx_temp_transaction on pg_temp_6.temp_transaction tran (cost=0.00..3.33 rows=2 width=144) (actual time=0.003..0.003 rows=0 loops=449,655)

  • Output: tran.object_id, tran.snapshot_id, tran.transaction_ids
  • Index Cond: ((tran.snapshot_id = vs.snapshot_id) AND (tran.snapshot_id = 159) AND (tran.object_id = vs.object_id))
  • Buffers: local hit=1460968 read=3039 written=114
17. 1,013.200 1,013.200 ↓ 0.0 0 506,600

Index Scan using vwer_action_idx on castoncast_central.viewer_action_plans ap (cost=0.00..2.72 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=506,600)

  • Output: ap.metric_id, ap.object_id, ap.first_snapshot_date, ap.last_snapshot_date, ap.user_name, ap.sel_date, ap.priority, ap.action_def, ap.tag, ap.info
  • Index Cond: ((ap.metric_id = vs.diag_id) AND (ap.object_id = vs.object_id))
  • Buffers: shared hit=1026456
18. 4,052.816 4,052.816 ↑ 1.0 2 506,602

Index Scan using idx_temp_technology on pg_temp_6.temp_technology temp (cost=0.00..3.36 rows=2 width=12) (actual time=0.004..0.008 rows=2 loops=506,602)

  • Output: temp.object_id, temp.snapshot_id, temp.techno_type_id
  • Index Cond: ((temp.snapshot_id = 159) AND (temp.object_id = p.object_id))
  • Buffers: local hit=2523841 read=12224 written=2166
19. 34,448.951 34,449.004 ↑ 1.0 30 1,013,206

Materialize (cost=0.00..1.45 rows=30 width=524) (actual time=0.001..0.034 rows=30 loops=1,013,206)

  • Output: er.user_name, er.metric_id, er.object_id
  • Buffers: shared hit=1
20. 0.053 0.053 ↑ 1.0 30 1

Seq Scan on castoncast_central.aed_exclusions_requests er (cost=0.00..1.30 rows=30 width=524) (actual time=0.010..0.053 rows=30 loops=1)

  • Output: er.user_name, er.metric_id, er.object_id
  • Buffers: shared hit=1