explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Frl

Settings
# exclusive inclusive rows x rows loops node
1. 146.297 8,696.532 ↓ 18,135.5 36,271 1

Unique (cost=56,306.87..56,307.01 rows=2 width=2,303) (actual time=8,489.052..8,696.532 rows=36,271 loops=1)

  • Output: wt.diag_id, wt.object_id, wt.object_full_name, wt.object_name, wt.violation_status, wt.pri, os.object_status, ((SubPlan 2)), (CASE WHEN (wt.metric_id <> 0) THEN 1 ELSE 0 END), (CASE WHEN (wt.metric_id = 0) THEN 1 ELSE 0 END), ap.priority, ap.first_snapshot_date, ap.last_snapshot_date, ap.sel_date, ap.action_def, ap.tag, ((SubPlan 3)), er.user_name, er.last_update, er.justify, (NULL::text), (NULL::text), (NULL::text), (NULL::text), (lower((wt.object_full_name)::text)), (lower((wt.diag_name)::text))
  • Buffers: shared hit=800,390 read=2
2. 665.802 8,550.235 ↓ 18,135.5 36,271 1

Sort (cost=56,306.87..56,306.88 rows=2 width=2,303) (actual time=8,489.041..8,550.235 rows=36,271 loops=1)

  • Output: wt.diag_id, wt.object_id, wt.object_full_name, wt.object_name, wt.violation_status, wt.pri, os.object_status, ((SubPlan 2)), (CASE WHEN (wt.metric_id <> 0) THEN 1 ELSE 0 END), (CASE WHEN (wt.metric_id = 0) THEN 1 ELSE 0 END), ap.priority, ap.first_snapshot_date, ap.last_snapshot_date, ap.sel_date, ap.action_def, ap.tag, ((SubPlan 3)), er.user_name, er.last_update, er.justify, (NULL::text), (NULL::text), (NULL::text), (NULL::text), (lower((wt.object_full_name)::text)), (lower((wt.diag_name)::text))
  • Sort Key: wt.pri, (lower((wt.diag_name)::text)), (lower((wt.object_full_name)::text)), wt.object_id, wt.diag_id, wt.object_full_name, wt.object_name, wt.violation_status, os.object_status, ((SubPlan 2)), (CASE WHEN (wt.metric_id <> 0) THEN 1 ELSE 0 END), (CASE WHEN (wt.metric_id = 0) THEN 1 ELSE 0 END), ap.priority, ap.first_snapshot_date, ap.last_snapshot_date, ap.sel_date, ap.action_def, ap.tag, ((SubPlan 3)), er.user_name, er.last_update, er.justify
  • Sort Method: quicksort Memory: 19,867kB
  • Buffers: shared hit=800,390 read=2
3. 899.221 7,884.433 ↓ 18,135.5 36,271 1

Nested Loop Left Join (cost=41,037.73..56,306.86 rows=2 width=2,303) (actual time=4,400.059..7,884.433 rows=36,271 loops=1)

  • Output: wt.diag_id, wt.object_id, wt.object_full_name, wt.object_name, wt.violation_status, wt.pri, os.object_status, (SubPlan 2), CASE WHEN (wt.metric_id <> 0) THEN 1 ELSE 0 END, CASE WHEN (wt.metric_id = 0) THEN 1 ELSE 0 END, ap.priority, ap.first_snapshot_date, ap.last_snapshot_date, ap.sel_date, ap.action_def, ap.tag, (SubPlan 3), er.user_name, er.last_update, er.justify, NULL::text, NULL::text, NULL::text, NULL::text, lower((wt.object_full_name)::text), lower((wt.diag_name)::text)
  • Buffers: shared hit=800,379 read=2
4. 384.687 5,353.017 ↓ 18,135.5 36,271 1

Nested Loop (cost=41,037.73..56,281.45 rows=2 width=1,255) (actual time=4,399.557..5,353.017 rows=36,271 loops=1)

  • Output: wt.diag_id, wt.object_id, wt.object_full_name, wt.object_name, wt.violation_status, wt.pri, wt.metric_id, wt.snapshot_id, wt.diag_name, os.object_status, ap.priority, ap.first_snapshot_date, ap.last_snapshot_date, ap.sel_date, ap.action_def, ap.tag
  • Buffers: shared hit=618,923 read=1
5. 137.810 4,605.620 ↓ 9.9 36,271 1

Merge Left Join (cost=41,037.73..41,065.67 rows=3,680 width=1,255) (actual time=4,399.380..4,605.620 rows=36,271 loops=1)

  • Output: wt.diag_id, wt.object_id, wt.object_full_name, wt.object_name, wt.violation_status, wt.pri, wt.metric_id, wt.snapshot_id, wt.diag_name, wt.previous_snapshot_id, ap.priority, ap.first_snapshot_date, ap.last_snapshot_date, ap.sel_date, ap.action_def, ap.tag
  • Merge Cond: ((wt.object_id = ap.object_id) AND (wt.diag_id = ap.metric_id))
  • Buffers: shared hit=473,483
6. 166.581 4,467.760 ↓ 9.9 36,271 1

Sort (cost=41,026.26..41,035.46 rows=3,680 width=195) (actual time=4,399.313..4,467.760 rows=36,271 loops=1)

  • Output: wt.diag_id, wt.object_id, wt.object_full_name, wt.object_name, wt.violation_status, wt.pri, wt.metric_id, wt.snapshot_id, wt.diag_name, wt.previous_snapshot_id
  • Sort Key: wt.object_id, wt.diag_id
  • Sort Method: quicksort Memory: 11,629kB
  • Buffers: shared hit=473,483
7. 115.644 4,301.179 ↓ 9.9 36,271 1

Subquery Scan on wt (cost=40,651.90..40,808.30 rows=3,680 width=195) (actual time=3,888.906..4,301.179 rows=36,271 loops=1)

  • Output: wt.diag_id, wt.object_id, wt.object_full_name, wt.object_name, wt.violation_status, wt.pri, wt.metric_id, wt.snapshot_id, wt.diag_name, wt.previous_snapshot_id
  • Buffers: shared hit=473,483
8. 105.658 4,185.535 ↓ 9.9 36,271 1

Limit (cost=40,651.90..40,771.50 rows=3,680 width=191) (actual time=3,888.898..4,185.535 rows=36,271 loops=1)

  • Output: vs.object_id, vs.diag_id, t.metric_name, (lower((t.metric_name)::text)), vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, o.object_full_name, (lower((o.object_full_name)::text)), o.object_name, (0), (COALESCE(r.metric_id, 0))
  • Buffers: shared hit=473,483
9. 131.461 4,079.877 ↓ 9.9 36,271 1

Unique (cost=40,651.90..40,771.50 rows=3,680 width=191) (actual time=3,888.891..4,079.877 rows=36,271 loops=1)

  • Output: vs.object_id, vs.diag_id, t.metric_name, (lower((t.metric_name)::text)), vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, o.object_full_name, (lower((o.object_full_name)::text)), o.object_name, (0), (COALESCE(r.metric_id, 0))
  • Buffers: shared hit=473,483
10. 358.078 3,948.416 ↓ 9.9 36,285 1

Sort (cost=40,651.90..40,661.10 rows=3,680 width=191) (actual time=3,888.883..3,948.416 rows=36,285 loops=1)

  • Output: vs.object_id, vs.diag_id, t.metric_name, (lower((t.metric_name)::text)), vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, o.object_full_name, (lower((o.object_full_name)::text)), o.object_name, (0), (COALESCE(r.metric_id, 0))
  • Sort Key: (lower((t.metric_name)::text)), (lower((o.object_full_name)::text)), vs.object_id, vs.diag_id, t.metric_name, vs.violation_status, o.object_full_name, o.object_name, (COALESCE(r.metric_id, 0))
  • Sort Method: quicksort Memory: 20,243kB
  • Buffers: shared hit=473,483
11. 213.042 3,590.338 ↓ 9.9 36,285 1

Hash Join (cost=142.03..40,433.95 rows=3,680 width=191) (actual time=1,800.224..3,590.338 rows=36,285 loops=1)

  • Output: vs.object_id, vs.diag_id, t.metric_name, lower((t.metric_name)::text), vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, o.object_full_name, lower((o.object_full_name)::text), o.object_name, 0, COALESCE(r.metric_id, 0)
  • Hash Cond: (vs.diag_id = t.metric_id)
  • Buffers: shared hit=473,483
12. 367.648 3,368.351 ↓ 9.9 36,285 1

Nested Loop Left Join (cost=0.33..40,218.65 rows=3,680 width=144) (actual time=1,791.161..3,368.351 rows=36,285 loops=1)

  • Output: vs.object_id, vs.diag_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, o.object_full_name, o.object_name, r.metric_id
  • Buffers: shared hit=473,394
13. 491.315 2,565.451 ↓ 9.9 36,271 1

Nested Loop (cost=0.33..14,655.18 rows=3,680 width=144) (actual time=1,790.655..2,565.451 rows=36,271 loops=1)

  • Output: vs.object_id, vs.diag_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, o.object_full_name, o.object_name, o.object_id
  • Join Filter: (li.previous_object_id = ml.module_id)
  • Rows Removed by Join Filter: 217,626
  • Buffers: shared hit=291,744
14. 0.113 0.113 ↑ 1.0 7 1

Index Scan using dss_mod_lnk_idx2 on c8316_23879_dku.dss_module_links ml (cost=0.00..8.05 rows=7 width=8) (actual time=0.046..0.113 rows=7 loops=1)

  • Output: ml.module_id, ml.object_id, ml.object_type_id, ml.snapshot_id
  • Index Cond: (ml.snapshot_id = 19)
  • Filter: (ml.object_id = ANY ('{588834,588835,588836,588837,588838,588839,588840}'::integer[]))
  • Rows Removed by Filter: 31
  • Buffers: shared hit=4
15. 510.670 2,074.023 ↓ 9.0 36,271 7

Materialize (cost=0.33..14,232.11 rows=4,049 width=148) (actual time=0.024..296.289 rows=36,271 loops=7)

  • Output: vs.object_id, vs.diag_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, li.previous_object_id, o.object_full_name, o.object_name, o.object_id
  • Buffers: shared hit=291,740
16. 373.266 1,563.353 ↓ 9.0 36,271 1

Nested Loop (cost=0.33..14,211.86 rows=4,049 width=148) (actual time=0.149..1,563.353 rows=36,271 loops=1)

  • Output: vs.object_id, vs.diag_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, li.previous_object_id, o.object_full_name, o.object_name, o.object_id
  • Join Filter: (vs.object_id = li.next_object_id)
  • Buffers: shared hit=291,740
17. 372.810 791.106 ↓ 9.2 36,271 1

Nested Loop (cost=0.33..11,361.31 rows=3,928 width=144) (actual time=0.111..791.106 rows=36,271 loops=1)

  • Output: vs.object_id, vs.diag_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status, o.object_full_name, o.object_name, o.object_id
  • Buffers: shared hit=146,480
18. 91.857 91.857 ↓ 9.2 36,271 1

Index Scan using dss_violation_status_idx1 on c8316_23879_dku.dss_violation_statuses vs (cost=0.33..3,880.34 rows=3,928 width=20) (actual time=0.064..91.857 rows=36,271 loops=1)

  • Output: vs.diag_id, vs.object_id, vs.snapshot_id, vs.previous_snapshot_id, vs.violation_status
  • Index Cond: ((vs.snapshot_id = 19) AND (vs.previous_snapshot_id = 18) AND (vs.diag_id = ANY ('{578,1058,1060,2232,2564,3062,3586,3610,3612,3616,3630,4598,4602,5092,5100,5144,6078,6124,7054,7126,7130,7140,7146,7156,7198,7200,7202,7204,7206,7210,7212,7258,7260,7274,7288,7292,7294,7300,7340,7346,7348,7370,7424,7434,7438,7470,7504,7506,7518,7520,7532,7542,7562,7572,7576,7578,7580,7582,7584,7586,7650,7652,7664,7670,7690,7692,7702,7710,7716,7728,7732,7740,7742,7746,7748,7750,7752,7770,7778,7788,7792,7802,7808,7816,7842,7868,7874,7880,7882,7902,7914,7916,7932,7934,7954,7962,7968,7970,7972,7976,7978,7980,7982,7984,7986,7988,7990,7992,7994,7998,8002,8004,8006,8012,8014,8024,8026,8028,8034,8054,8062,8070,8074,8092,8098,8100,8102,8104,8108,8110,8112,8114,8120,8122,8132,8148,8152,8156,8158,8160,8162,8214,8216,8218,8220,8222,8236,8400,8402,8408,8410,8418,8420,8424,8426,8434,8436,8438,8440,8442,8444,1020004,1020006,1020008,1020010,1020012,1020014,1020016,1020022,1020024,1020026,1020028,1020030,1020032,1020034,1020036,1020038,1020042,1020044,1020046,1020048,1020050,1020052,1020056,1020058,1020062,1020064,1020066,1020070,1020072,1020074,1020076,1020102,1020104,1020300,1020302,1020306,1020308,1020310,1020314,1020316,1020318,1020452,1020454,1020456,1020504,1020508,1020510,1020512,1020516,1020518,1020520,1020522,1020524,1020526,1020528,1020530,1020534,1020536,1020542,1020544,1020546,1020548,1020550,1020552,1020700,1020710,1020714,1020716,1020730,1020748,1020800,1020806,1020808,1020810,1020814,1020820,1020822,1020824,1020826,1020828,1020830,1020832,1020834,1020838,1020840,1020848,1020850,1027000,1027004,1039032,1039034,1039036,1039038,1039040,1039056,1039062,1042026,1042042,1042046,1101004,1101006,1101016,1101040,1101084,1101088}'::integer[])))
  • Filter: (vs.violation_status <> 2)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1,205
19. 326.439 326.439 ↑ 1.0 1 36,271

Index Scan using dss_objects_pk on c8316_23879_dku.dss_objects o (cost=0.00..1.89 rows=1 width=124) (actual time=0.007..0.009 rows=1 loops=36,271)

  • 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=145,275
20. 398.981 398.981 ↑ 1.0 1 36,271

Index Scan using dss_link_info3_idx2 on c8316_23879_dku.dss_link_info3 li (cost=0.00..0.71 rows=1 width=12) (actual time=0.009..0.011 rows=1 loops=36,271)

  • Output: li.snapshot_id, li.previous_object_id, li.next_object_id
  • Index Cond: ((li.next_object_id = o.object_id) AND (li.snapshot_id = 19))
  • Buffers: shared hit=145,260
21. 435.252 435.252 ↑ 1.0 1 36,271

Index Only Scan using dss_metr_res_idx2 on c8316_23879_dku.dss_metric_results r (cost=0.00..6.94 rows=1 width=12) (actual time=0.010..0.012 rows=1 loops=36,271)

  • Output: r.snapshot_id, r.metric_id, r.object_id, r.metric_value_index
  • Index Cond: ((r.snapshot_id = vs.snapshot_id) AND (r.snapshot_id = 19) AND (r.metric_id = (vs.diag_id + 1)) AND (r.object_id = o.object_id))
  • Heap Fetches: 36,285
  • Buffers: shared hit=181,650
22. 4.423 8.945 ↑ 1.0 2,342 1

Hash (cost=112.42..112.42 rows=2,342 width=51) (actual time=8.945..8.945 rows=2,342 loops=1)

  • Output: t.metric_name, t.metric_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 196kB
  • Buffers: shared hit=89
23. 4.522 4.522 ↑ 1.0 2,342 1

Seq Scan on c8316_23879_dku.dss_metric_types t (cost=0.00..112.42 rows=2,342 width=51) (actual time=0.022..4.522 rows=2,342 loops=1)

  • Output: t.metric_name, t.metric_id
  • Buffers: shared hit=89
24. 0.046 0.050 ↓ 0.0 0 1

Sort (cost=11.46..11.56 rows=40 width=1,068) (actual time=0.050..0.050 rows=0 loops=1)

  • Output: ap.priority, ap.first_snapshot_date, ap.last_snapshot_date, ap.sel_date, ap.action_def, ap.tag, ap.metric_id, ap.object_id
  • Sort Key: ap.object_id, ap.metric_id
  • Sort Method: quicksort Memory: 25kB
25. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on c8316_23879_dku.viewer_action_plans ap (cost=0.00..10.40 rows=40 width=1,068) (actual time=0.004..0.004 rows=0 loops=1)

  • Output: ap.priority, ap.first_snapshot_date, ap.last_snapshot_date, ap.sel_date, ap.action_def, ap.tag, ap.metric_id, ap.object_id
26. 362.710 362.710 ↑ 1.0 1 36,271

Index Scan using dss_objects_statuses_idx2 on c8316_23879_dku.dss_objects_statuses os (cost=0.00..4.12 rows=1 width=16) (actual time=0.008..0.010 rows=1 loops=36,271)

  • 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 = wt.snapshot_id) AND (os.previous_snapshot_id = wt.previous_snapshot_id) AND (os.object_id = wt.object_id))
  • Buffers: shared hit=145,440 read=1
27. 108.813 108.813 ↓ 0.0 0 36,271

Index Scan using aed_exclreq_idx on c8316_23879_dku.aed_exclusions_requests er (cost=0.00..0.28 rows=1 width=1,048) (actual time=0.003..0.003 rows=0 loops=36,271)

  • Output: er.object_id, er.metric_id, er.application_id, er.user_name, er.last_update, er.justify
  • Index Cond: ((er.object_id = wt.object_id) AND (er.metric_id = wt.diag_id))
  • Buffers: shared hit=36,271
28.          

SubPlan (for Nested Loop Left Join)

29. 398.981 1,160.672 ↑ 1.0 1 36,271

Result (cost=3.14..3.15 rows=1 width=0) (actual time=0.030..0.032 rows=1 loops=36,271)

  • Output: $1
  • Buffers: shared hit=145,185 read=1
30.          

Initplan (for Result)

31. 253.897 761.691 ↑ 1.0 1 36,271

Limit (cost=0.00..3.14 rows=1 width=0) (actual time=0.020..0.021 rows=1 loops=36,271)

  • Output: (1)
  • Buffers: shared hit=145,185 read=1
32. 253.897 507.794 ↑ 2.0 1 36,271

Result (cost=0.00..6.27 rows=2 width=0) (actual time=0.014..0.014 rows=1 loops=36,271)

  • Output: 1
  • One-Time Filter: (1 IS NOT NULL)
  • Buffers: shared hit=145,185 read=1
33. 253.897 253.897 ↑ 2.0 1 36,271

Index Only Scan using dss_source_positions_idx on c8316_23879_dku.dss_source_positions sp (cost=0.00..6.27 rows=2 width=0) (actual time=0.007..0.007 rows=1 loops=36,271)

  • Output: sp.object_id, sp.panel
  • Index Cond: (sp.object_id = wt.object_id)
  • Heap Fetches: 36,271
  • Buffers: shared hit=145,185 read=1
34. 290.168 362.710 ↓ 0.0 0 36,271

HashAggregate (cost=9.25..9.26 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=36,271)

  • Output: (sign((r.metric_id)::double precision))
35. 72.542 72.542 ↓ 0.0 0 36,271

Index Only Scan using dss_metr_res_idx2 on c8316_23879_dku.dss_metric_results r (cost=0.01..9.25 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=36,271)

  • Output: sign((r.metric_id)::double precision)
  • Index Cond: ((r.snapshot_id = wt.snapshot_id) AND (r.metric_id = ANY (ARRAY[(er.metric_id + 1), (- (er.metric_id + 1))])) AND (r.object_id = er.object_id))
  • Heap Fetches: 0'Total runtime: 8,760.245 ms'