explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q2PH

Settings
# exclusive inclusive rows x rows loops node
1. 4,228.033 168,327.851 ↓ 51,372.9 410,983 1

HashAggregate (cost=407,606.79..407,606.89 rows=8 width=878) (actual time=167,435.833..168,327.851 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=7,606,800 read=24,520, local hit=3,539 read=2,399 dirtied=1,927 written=2,399
2. 19,133.444 164,099.818 ↓ 56,207.1 449,657 1

Nested Loop Left Join (cost=11,854.10..407,606.35 rows=8 width=878) (actual time=47,602.629..164,099.818 rows=449,657 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: 13,489,710
  • Buffers: shared hit=7,606,793 read=24,520, local hit=2,252 read=2,272 dirtied=1,927 written=2,272
3. 2,824.459 128,778.722 ↓ 56,207.1 449,657 1

Nested Loop Left Join (cost=11,854.10..407,600.78 rows=8 width=362) (actual time=47,602.332..128,778.722 rows=449,657 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=7,606,793 read=24,519, local hit=2,252 read=2,272 dirtied=1,927 written=2,272
4. 2,248.915 123,256.327 ↓ 56,207.0 449,656 1

Hash Left Join (cost=11,854.10..407,578.90 rows=8 width=346) (actual time=47,602.299..123,256.327 rows=449,656 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
  • Hash Cond: ((vs.snapshot_id = tran.snapshot_id) AND (vs.object_id = tran.object_id))
  • Buffers: shared hit=6,695,505 read=24,234, local hit=2,252 read=2,272 dirtied=1,927 written=2,272
5. 3,474.924 120,814.927 ↓ 56,207.0 449,656 1

Nested Loop (cost=8,760.08..404,484.65 rows=8 width=210) (actual time=47,409.690..120,814.927 rows=449,656 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
  • Buffers: shared hit=6,695,505 read=24,234, local hit=899 read=2,272 dirtied=1,927 written=2,272
6. 3,627.918 114,642.067 ↓ 56,207.0 449,656 1

Nested Loop (cost=8,760.08..404,463.81 rows=8 width=161) (actual time=47,406.471..114,642.067 rows=449,656 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, o.object_full_name, o.object_name, temp.techno_type_id, os.object_status, os.module_id
  • Join Filter: (vs.object_id = p.object_id)
  • Buffers: shared hit=5,346,595 read=24,150, local hit=899 read=2,272 dirtied=1,927 written=2,272
7. 3,149.354 104,269.309 ↓ 24,980.9 449,656 1

Nested Loop (cost=8,760.08..404,376.64 rows=18 width=133) (actual time=47,369.400..104,269.309 rows=449,656 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, o.object_full_name, o.object_name, o.object_id, temp.techno_type_id, temp.object_id, os.object_status, os.module_id, os.object_id
  • Buffers: shared hit=3,098,544 read=21,554, local hit=899 read=2,272 dirtied=1,927 written=2,272
8. 23,538.650 73,995.143 ↓ 101.3 410,982 1

Hash Join (cost=8,760.08..392,933.12 rows=4,058 width=121) (actual time=47,346.470..73,995.143 rows=410,982 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, o.object_full_name, o.object_name, o.object_id, temp.techno_type_id, temp.object_id
  • Hash Cond: (vs.object_id = o.object_id)
  • Buffers: shared hit=1,416,900 read=16,392, local hit=899 read=2,272 dirtied=1,927 written=2,272
9. 3,155.049 3,155.049 ↑ 1.1 410,981 1

Index Scan using dss_violation_status_idx1 on castoncast_central_partitioned.dss_violation_statuses vs (cost=0.00..381,819.89 rows=469,276 width=20) (actual time=44.953..3,155.049 rows=410,981 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.violation_status, vs.previous_snapshot_id
  • Index Cond: (vs.snapshot_id = 159)
  • Filter: (vs.violation_status <> 2)
  • Rows Removed by Filter: 15,398
  • Buffers: shared hit=1,157 read=4,355
10. 788.531 47,301.444 ↓ 190.7 356,466 1

Hash (cost=8,736.72..8,736.72 rows=1,869 width=105) (actual time=47,301.444..47,301.444 rows=356,466 loops=1)

  • Output: o.object_full_name, o.object_name, o.object_id, temp.techno_type_id, temp.object_id, temp.snapshot_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 52,831kB
  • Buffers: shared hit=1,415,743 read=12,037, local hit=899 read=2,272 dirtied=1,927 written=2,272
11. 2,765.704 46,512.913 ↓ 190.7 356,466 1

Nested Loop (cost=28.76..8,736.72 rows=1,869 width=105) (actual time=52.355..46,512.913 rows=356,466 loops=1)

  • Output: o.object_full_name, o.object_name, o.object_id, temp.techno_type_id, temp.object_id, temp.snapshot_id
  • Buffers: shared hit=1,415,743 read=12,037, local hit=899 read=2,272 dirtied=1,927 written=2,272
12. 576.204 614.823 ↓ 190.7 356,466 1

Bitmap Heap Scan on pg_temp_7.temp_technology temp (cost=28.76..1,552.47 rows=1,869 width=12) (actual time=38.952..614.823 rows=356,466 loops=1)

  • Output: temp.object_id, temp.snapshot_id, temp.techno_type_id
  • Recheck Cond: (temp.snapshot_id = 159)
  • Buffers: local hit=899 read=2,272 dirtied=1,927 written=2,272
13. 38.619 38.619 ↓ 190.7 356,466 1

Bitmap Index Scan on idx_temp_technology (cost=0.00..28.29 rows=1,869 width=0) (actual time=38.619..38.619 rows=356,466 loops=1)

  • Index Cond: (temp.snapshot_id = 159)
  • Buffers: local hit=899 read=345 written=345
14. 43,132.386 43,132.386 ↑ 1.0 1 356,466

Index Scan using dss_objects_idx3 on castoncast_central_partitioned.dss_objects o (cost=0.00..3.83 rows=1 width=93) (actual time=0.120..0.121 rows=1 loops=356,466)

  • Output: o.object_id, o.object_type_id, o.object_name, o.object_description, o.object_full_name
  • Index Cond: (o.object_id = temp.object_id)
  • Buffers: shared hit=1,415,743 read=12,037
15. 8,630.622 27,124.812 ↑ 2.0 1 410,982

Append (cost=0.00..2.80 rows=2 width=20) (actual time=0.059..0.066 rows=1 loops=410,982)

  • Buffers: shared hit=1,681,644 read=5,162
16. 410.982 410.982 ↓ 0.0 0 410,982

Seq Scan on castoncast_central_partitioned.dss_objects_statuses os (cost=0.00..0.00 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=410,982)

  • Output: os.object_status, os.module_id, os.object_id, os.snapshot_id, os.previous_snapshot_id
  • Filter: ((os.snapshot_id = 159) AND (vs.object_id = os.object_id) AND (vs.previous_snapshot_id = os.previous_snapshot_id))
17. 18,083.208 18,083.208 ↑ 1.0 1 410,982

Index Scan using dss_objects_statuses_159_snapshot_id_previous_snapshot_id_o_idx on castoncast_central_partitioned.dss_objects_statuses_159 os (cost=0.00..2.80 rows=1 width=20) (actual time=0.040..0.044 rows=1 loops=410,982)

  • Output: os.object_status, os.module_id, os.object_id, os.snapshot_id, os.previous_snapshot_id
  • Index Cond: ((os.snapshot_id = 159) AND (os.previous_snapshot_id = vs.previous_snapshot_id) AND (os.object_id = vs.object_id))
  • Buffers: shared hit=1,681,644 read=5,162
18. 6,744.840 6,744.840 ↑ 1.0 1 449,656

Index Scan using dss_pri_idx1 on castoncast_central_partitioned.dss_pri p (cost=0.00..4.83 rows=1 width=48) (actual time=0.014..0.015 rows=1 loops=449,656)

  • 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 = o.object_id))
  • Buffers: shared hit=2,248,051 read=2,596
19. 2,697.936 2,697.936 ↑ 1.0 1 449,656

Index Scan using dss_metr_typ_pk on castoncast_central_partitioned.dss_metric_types t (cost=0.00..2.59 rows=1 width=53) (actual time=0.005..0.006 rows=1 loops=449,656)

  • 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=1,348,910 read=84
20. 111.016 192.485 ↑ 1.0 63,310 1

Hash (cost=2,144.38..2,144.38 rows=63,310 width=144) (actual time=192.485..192.485 rows=63,310 loops=1)

  • Output: tran.transaction_ids, tran.object_id, tran.snapshot_id
  • Buckets: 8,192 Batches: 1 Memory Usage: 10,946kB
  • Buffers: local hit=1,353
21. 81.469 81.469 ↑ 1.0 63,310 1

Seq Scan on pg_temp_7.temp_transaction tran (cost=0.00..2,144.38 rows=63,310 width=144) (actual time=0.013..81.469 rows=63,310 loops=1)

  • Output: tran.transaction_ids, tran.object_id, tran.snapshot_id
  • Filter: (tran.snapshot_id = 159)
  • Buffers: local hit=1,353
22. 2,697.936 2,697.936 ↓ 0.0 0 449,656

Index Scan using vwer_action_idx on castoncast_central_partitioned.viewer_action_plans ap (cost=0.00..2.72 rows=1 width=24) (actual time=0.006..0.006 rows=0 loops=449,656)

  • 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=911,288 read=285
23. 16,187.522 16,187.652 ↑ 1.0 30 449,657

Materialize (cost=0.00..1.45 rows=30 width=524) (actual time=0.001..0.036 rows=30 loops=449,657)

  • Output: er.user_name, er.metric_id, er.object_id
  • Buffers: shared read=1
24. 0.130 0.130 ↑ 1.0 30 1

Seq Scan on castoncast_central_partitioned.aed_exclusions_requests er (cost=0.00..1.30 rows=30 width=524) (actual time=0.083..0.130 rows=30 loops=1)

  • Output: er.user_name, er.metric_id, er.object_id
  • Buffers: shared read=1
Total runtime : 168,905.088 ms