explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xhHN

Settings
# exclusive inclusive rows x rows loops node
1. 3,540.945 69,229.963 ↓ 300.4 410,983 1

HashAggregate (cost=500,155.34..500,172.44 rows=1,368 width=877) (actual time=68,360.511..69,229.963 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=4052625 read=8031, local hit=2760214 read=1269 written=688
2. 18,605.693 65,689.018 ↓ 328.7 449,657 1

Nested Loop Left Join (cost=470,941.94..500,080.10 rows=1,368 width=877) (actual time=4,820.031..65,689.018 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: 13489710
  • Buffers: shared hit=4052618 read=8031, local hit=2758800 read=1269 written=688
3. 3,076.871 31,794.987 ↓ 328.7 449,657 1

Nested Loop (cost=470,941.94..499,360.53 rows=1,368 width=361) (actual time=4,819.825..31,794.987 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=4052617 read=8031, local hit=2758800 read=1269 written=688
4. 2,603.840 26,469.836 ↓ 328.9 449,656 1

Nested Loop Left Join (cost=470,941.94..495,558.30 rows=1,367 width=369) (actual time=4,819.628..26,469.836 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, 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=4052617 read=8031, local hit=958631 read=171 written=101
5. 3,179.439 22,517.028 ↓ 328.9 449,656 1

Nested Loop (cost=470,941.94..491,785.93 rows=1,367 width=234) (actual time=4,819.466..22,517.028 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, 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, ap.first_snapshot_date, ap.last_snapshot_date
  • Join Filter: (p.object_id = o.object_id)
  • Buffers: shared hit=4052617 read=8031
6. 3,366.663 17,089.309 ↓ 328.9 449,656 1

Nested Loop (cost=470,941.94..487,898.90 rows=1,367 width=141) (actual time=4,819.387..17,089.309 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, p.object_id, t.metric_name, os.object_status, os.module_id, os.object_id, ap.first_snapshot_date, ap.last_snapshot_date
  • Buffers: shared hit=2251710 read=8031
7. 1,708.480 11,024.710 ↓ 213.6 449,656 1

Hash Left Join (cost=470,941.94..477,522.68 rows=2,105 width=97) (actual time=4,819.346..11,024.710 rows=449,656 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, t.metric_name, os.object_status, os.module_id, os.object_id, ap.first_snapshot_date, ap.last_snapshot_date
  • Hash Cond: ((vs.diag_id = ap.metric_id) AND (vs.object_id = ap.object_id))
  • Buffers: shared hit=2485 read=6610
8. 1,271.114 9,190.571 ↓ 213.6 449,655 1

Hash Join (cost=470,273.69..476,754.39 rows=2,105 width=81) (actual time=4,693.650..9,190.571 rows=449,655 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, t.metric_name, os.object_status, os.module_id, os.object_id
  • Hash Cond: (vs.diag_id = t.metric_id)
  • Buffers: shared hit=2483 read=6379
9. 1,934.710 7,817.273 ↓ 213.6 449,655 1

Merge Join (cost=470,083.81..476,530.30 rows=2,105 width=32) (actual time=4,591.433..7,817.273 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.object_id = os.object_id) AND (vs.previous_snapshot_id = os.previous_snapshot_id))
  • Buffers: shared hit=2476 read=6266
10. 1,205.123 2,713.739 ↑ 1.1 410,981 1

Sort (cost=426,025.88..427,199.07 rows=469,276 width=20) (actual time=2,190.499..2,713.739 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.object_id, vs.previous_snapshot_id
  • Sort Method: quicksort Memory: 44396kB
  • Buffers: shared hit=1250 read=4261
11. 1,508.616 1,508.616 ↑ 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=0.039..1,508.616 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=1250 read=4261
12. 1,359.448 3,168.824 ↓ 1.6 634,352 1

Sort (cost=44,057.59..45,026.68 rows=387,637 width=16) (actual time=2,400.665..3,168.824 rows=634,352 loops=1)

  • Output: os.object_status, os.module_id, os.object_id, os.previous_snapshot_id
  • Sort Key: os.object_id, os.previous_snapshot_id
  • Sort Method: quicksort Memory: 30459kB
  • Buffers: shared hit=1226 read=2005
13. 855.458 1,809.376 ↑ 1.0 387,636 1

Append (cost=0.00..8,076.45 rows=387,637 width=16) (actual time=0.034..1,809.376 rows=387,636 loops=1)

  • Buffers: shared hit=1226 read=2005
14. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on castoncast_central_partitioned.dss_objects_statuses os (cost=0.00..0.00 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: os.object_status, os.module_id, os.object_id, os.previous_snapshot_id
  • Filter: (os.snapshot_id = 159)
15. 953.915 953.915 ↑ 1.0 387,636 1

Seq Scan on castoncast_central_partitioned.dss_objects_statuses_159 os (cost=0.00..8,076.45 rows=387,636 width=16) (actual time=0.022..953.915 rows=387,636 loops=1)

  • Output: os.object_status, os.module_id, os.object_id, os.previous_snapshot_id
  • Filter: (os.snapshot_id = 159)
  • Buffers: shared hit=1226 read=2005
16. 5.056 102.184 ↑ 1.0 3,106 1

Hash (cost=151.06..151.06 rows=3,106 width=53) (actual time=102.184..102.184 rows=3,106 loops=1)

  • Output: t.metric_name, t.metric_id
  • Buckets: 1024 Batches: 1 Memory Usage: 263kB
  • Buffers: shared hit=7 read=113
17. 97.128 97.128 ↑ 1.0 3,106 1

Seq Scan on castoncast_central_partitioned.dss_metric_types t (cost=0.00..151.06 rows=3,106 width=53) (actual time=0.074..97.128 rows=3,106 loops=1)

  • Output: t.metric_name, t.metric_id
  • Buffers: shared hit=7 read=113
18. 22.026 125.659 ↑ 1.0 17,410 1

Hash (cost=407.10..407.10 rows=17,410 width=24) (actual time=125.659..125.659 rows=17,410 loops=1)

  • Output: ap.first_snapshot_date, ap.last_snapshot_date, ap.metric_id, ap.object_id
  • Buckets: 2048 Batches: 1 Memory Usage: 953kB
  • Buffers: shared hit=2 read=231
19. 103.633 103.633 ↑ 1.0 17,410 1

Seq Scan on castoncast_central_partitioned.viewer_action_plans ap (cost=0.00..407.10 rows=17,410 width=24) (actual time=0.013..103.633 rows=17,410 loops=1)

  • Output: ap.first_snapshot_date, ap.last_snapshot_date, ap.metric_id, ap.object_id
  • Buffers: shared hit=2 read=231
20. 2,697.936 2,697.936 ↑ 1.0 1 449,656

Index Scan using dss_pri_idx1 on castoncast_central_partitioned.dss_pri p (cost=0.00..4.92 rows=1 width=44) (actual time=0.005..0.006 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 = vs.object_id))
  • Buffers: shared hit=2249225 read=1421
21. 2,248.280 2,248.280 ↑ 1.0 1 449,656

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

  • 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=1800907
22. 1,348.968 1,348.968 ↓ 0.0 0 449,656

Index Scan using idx_temp_transaction on pg_temp_6.temp_transaction tran (cost=0.00..2.75 rows=1 width=143) (actual time=0.003..0.003 rows=0 loops=449,656)

  • 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=958631 read=171 written=101
23. 2,248.280 2,248.280 ↑ 1.0 1 449,656

Index Scan using idx_temp_technology on pg_temp_6.temp_technology temp (cost=0.00..2.77 rows=1 width=12) (actual time=0.003..0.005 rows=1 loops=449,656)

  • 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=1800169 read=1098 written=587
24. 15,288.288 15,288.338 ↑ 1.0 30 449,657

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

  • Output: er.user_name, er.metric_id, er.object_id
  • Buffers: shared hit=1
25. 0.050 0.050 ↑ 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.006..0.050 rows=30 loops=1)

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