explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qhim

Settings
# exclusive inclusive rows x rows loops node
1. 50.737 5,562,830.301 ↓ 100.0 100 1

Subquery Scan on base (cost=740,935.12..740,935.64 rows=1 width=486) (actual time=5,562,627.130..5,562,830.301 rows=100 loops=1)

  • Output: base.cnt, base.no_meisai, base.no_level, base.no_dbom, base.cd_buhin, base.nu_suryo, base.cd_tani, base.cd_tyotatu, base.cd_tokushu, base.cd_sikyu, base.fl_tane, base.no_kanri, base.no_eda, base.no_kansetu, base.kb_torinaigai, base.cd_torihiki, base.kb_jigyonaigai, base.cd_jigyohonbu, base.kb_status_db, base.kb_status, base.kb_kibofiletype, base.dt_kakunin, base.nm_file, base.id_irai, base.dt_update, base.zigyohon_ryaku, base.torimei, base.nm_hinmoku, base.nm_tani, base.db_exist
  • Filter: ((base.cnt >= 1) AND (base.cnt <= 100))
  • Rows Removed by Filter: 10663
  • Buffers: shared hit=561203 read=11044
2. 103.720 5,562,779.564 ↓ 566.5 10,763 1

Subquery Scan on base_data (cost=740,935.12..740,935.35 rows=19 width=486) (actual time=5,562,627.117..5,562,779.564 rows=10,763 loops=1)

  • Output: rownum, base_data.no_meisai, base_data.no_level, base_data.no_dbom, base_data.cd_buhin, base_data.nu_suryo, base_data.cd_tani, base_data.cd_tyotatu, base_data.cd_tokushu, base_data.cd_sikyu, base_data.fl_tane, base_data.no_kanri, base_data.no_eda, base_data.no_kansetu, base_data.kb_torinaigai, base_data.cd_torihiki, base_data.kb_jigyonaigai, base_data.cd_jigyohonbu, base_data.kb_status_db, base_data.kb_status, base_data.kb_kibofiletype, base_data.dt_kakunin, base_data.nm_file, base_data.id_irai, base_data.dt_update, base_data.zigyohon_ryaku, base_data.torimei, base_data.nm_hinmoku, base_data.nm_tani, base_data.db_exist
  • Buffers: shared hit=561203 read=11044
3. 208.371 5,562,675.844 ↓ 566.5 10,763 1

Sort (cost=740,935.12..740,935.16 rows=19 width=478) (actual time=5,562,627.098..5,562,675.844 rows=10,763 loops=1)

  • Output: bom.no_meisai, bom.no_level, bom.no_dbom, bom.cd_hinmokuc, bom.nu_suryo, bom.cd_tani, bom.cd_tyotatu, bom.cd_tokushu, bom.cd_sikyu, bom.fl_tane, base_buhin.no_kanri, base_buhin.no_eda, base_buhin.no_kansetu, base_buhin.kb_torinaigai, base_buhin.cd_torihiki, base_buhin.kb_jigyonaigai, base_buhin.cd_jigyohonbu, base_buhin.kb_status_db, base_buhin.kb_status, base_buhin.kb_kibofiletype, base_buhin.dt_kakunin, base_buhin.nm_file, base_buhin.id_irai, base_buhin.dt_update, spd001.zigyohon_ryaku_jp, spd018_dai.torimeiknj, m023.nm_hinmoku_ja, m021.nm_tani_ja, (DECODE( (base_buhin.no_kanri)::character varying , NULL::character varying , DECODE( base_buhin.cd_buhin , NULL::character varying , 0 , 1 ) , 1 ))
  • Sort Key: bom.no_meisai, spd018_dai.torimeiknj, base_buhin.kb_status
  • Sort Method: quicksort Memory: 3221kB
  • Buffers: shared hit=561203 read=11044
4. 293.168 5,562,467.473 ↓ 566.5 10,763 1

Nested Loop Left Join (cost=20,385.92..740,934.71 rows=19 width=478) (actual time=4,827.799..5,562,467.473 rows=10,763 loops=1)

  • Output: bom.no_meisai, bom.no_level, bom.no_dbom, bom.cd_hinmokuc, bom.nu_suryo, bom.cd_tani, bom.cd_tyotatu, bom.cd_tokushu, bom.cd_sikyu, bom.fl_tane, base_buhin.no_kanri, base_buhin.no_eda, base_buhin.no_kansetu, base_buhin.kb_torinaigai, base_buhin.cd_torihiki, base_buhin.kb_jigyonaigai, base_buhin.cd_jigyohonbu, base_buhin.kb_status_db, base_buhin.kb_status, base_buhin.kb_kibofiletype, base_buhin.dt_kakunin, base_buhin.nm_file, base_buhin.id_irai, base_buhin.dt_update, spd001.zigyohon_ryaku_jp, spd018_dai.torimeiknj, m023.nm_hinmoku_ja, m021.nm_tani_ja, DECODE( (base_buhin.no_kanri)::character varying , NULL::character varying , DECODE( base_buhin.cd_buhin , NULL::character varying , 0 , 1 ) , 1 )
  • Inner Unique: true
  • Buffers: shared hit=561203 read=11044
5. 164.840 5,561,829.889 ↓ 566.5 10,763 1

Hash Left Join (cost=20,385.50..740,853.66 rows=19 width=508) (actual time=4,827.730..5,561,829.889 rows=10,763 loops=1)

  • Output: bom.no_meisai, bom.no_level, bom.no_dbom, bom.cd_hinmokuc, bom.nu_suryo, bom.cd_tani, bom.cd_tyotatu, bom.cd_tokushu, bom.cd_sikyu, bom.fl_tane, base_buhin.no_kanri, base_buhin.no_eda, base_buhin.no_kansetu, base_buhin.kb_torinaigai, base_buhin.cd_torihiki, base_buhin.kb_jigyonaigai, base_buhin.cd_jigyohonbu, base_buhin.kb_status_db, base_buhin.kb_status, base_buhin.kb_kibofiletype, base_buhin.dt_kakunin, base_buhin.nm_file, base_buhin.id_irai, base_buhin.dt_update, base_buhin.cd_buhin, m023.nm_hinmoku_ja, m021.nm_tani_ja, spd001.zigyohon_ryaku_jp
  • Hash Cond: ((base_buhin.kb_jigyonaigai = (spd001.zigyohon_code_f)::bpchar) AND ((base_buhin.cd_jigyohonbu)::text = (spd001.zigyohon_code)::text))
  • Buffers: shared hit=518887 read=11044
6. 173.855 5,561,664.586 ↓ 566.5 10,763 1

Hash Left Join (cost=20,383.45..740,851.46 rows=19 width=501) (actual time=4,827.234..5,561,664.586 rows=10,763 loops=1)

  • Output: bom.no_meisai, bom.no_level, bom.no_dbom, bom.cd_hinmokuc, bom.nu_suryo, bom.cd_tani, bom.cd_tyotatu, bom.cd_tokushu, bom.cd_sikyu, bom.fl_tane, base_buhin.no_kanri, base_buhin.no_eda, base_buhin.no_kansetu, base_buhin.kb_torinaigai, base_buhin.cd_torihiki, base_buhin.kb_jigyonaigai, base_buhin.cd_jigyohonbu, base_buhin.kb_status_db, base_buhin.kb_status, base_buhin.kb_kibofiletype, base_buhin.dt_kakunin, base_buhin.nm_file, base_buhin.id_irai, base_buhin.dt_update, base_buhin.cd_buhin, m023.nm_hinmoku_ja, m021.nm_tani_ja
  • Inner Unique: true
  • Hash Cond: (bom.cd_tani = m021.cd_tani)
  • Buffers: shared hit=518886 read=11044
7. 306.119 5,561,487.485 ↓ 566.5 10,763 1

Nested Loop Left Join (cost=20,373.16..740,840.93 rows=19 width=498) (actual time=4,823.947..5,561,487.485 rows=10,763 loops=1)

  • Output: bom.no_meisai, bom.no_level, bom.no_dbom, bom.cd_hinmokuc, bom.nu_suryo, bom.cd_tani, bom.cd_tyotatu, bom.cd_tokushu, bom.cd_sikyu, bom.fl_tane, base_buhin.no_kanri, base_buhin.no_eda, base_buhin.no_kansetu, base_buhin.kb_torinaigai, base_buhin.cd_torihiki, base_buhin.kb_jigyonaigai, base_buhin.cd_jigyohonbu, base_buhin.kb_status_db, base_buhin.kb_status, base_buhin.kb_kibofiletype, base_buhin.dt_kakunin, base_buhin.nm_file, base_buhin.id_irai, base_buhin.dt_update, base_buhin.cd_buhin, m023.nm_hinmoku_ja
  • Inner Unique: true
  • Buffers: shared hit=518883 read=11044
8. 2,835,092.794 5,560,600.164 ↓ 566.5 10,763 1

Nested Loop Left Join (cost=20,372.73..740,756.42 rows=19 width=478) (actual time=4,823.875..5,560,600.164 rows=10,763 loops=1)

  • Output: bom.no_meisai, bom.no_level, bom.no_dbom, bom.cd_hinmokuc, bom.nu_suryo, bom.cd_tani, bom.cd_tyotatu, bom.cd_tokushu, bom.cd_sikyu, bom.fl_tane, base_buhin.no_kanri, base_buhin.no_eda, base_buhin.no_kansetu, base_buhin.kb_torinaigai, base_buhin.cd_torihiki, base_buhin.kb_jigyonaigai, base_buhin.cd_jigyohonbu, base_buhin.kb_status_db, base_buhin.kb_status, base_buhin.kb_kibofiletype, base_buhin.dt_kakunin, base_buhin.nm_file, base_buhin.id_irai, base_buhin.dt_update, base_buhin.cd_buhin
  • Join Filter: ((bom.cd_hinmokuc)::text = (base_buhin.cd_buhin)::text)
  • Rows Removed by Join Filter: 573504138
  • Buffers: shared hit=477602 read=11044
9. 57.618 58.721 ↓ 188.3 3,577 1

Bitmap Heap Scan on reach02.d202_bom_b bom (cost=69.44..6,470.34 rows=19 width=46) (actual time=1.149..58.721 rows=3,577 loops=1)

  • Output: bom.cd_hinmoku, bom.cd_plant, bom.no_meisai, bom.no_level, bom.no_dbom, bom.cd_hinmokup, bom.cd_hinmokuc, bom.cd_type, bom.nu_suryo, bom.cd_tani, bom.cd_tyotatu, bom.cd_tokushu, bom.cd_sikyu, bom.fl_tane, bom.dt_update, bom.id_update
  • Recheck Cond: ((bom.cd_hinmoku)::text = 'A3MX2614NP'::text)
  • Filter: ((bom.cd_plant)::text = 'SOCC'::text)
  • Heap Blocks: exact=204
  • Buffers: shared hit=277
10. 1.103 1.103 ↓ 2.7 10,504 1

Bitmap Index Scan on idx_d202_bom_b_pk (cost=0.00..69.44 rows=3,868 width=0) (actual time=1.103..1.103 rows=10,504 loops=1)

  • Index Cond: ((bom.cd_hinmoku)::text = 'A3MX2614NP'::text)
  • Buffers: shared hit=73
11. 2,711,230.788 2,725,448.649 ↓ 871.4 160,334 3,577

Materialize (cost=20,303.28..734,234.10 rows=184 width=432) (actual time=0.050..761.937 rows=160,334 loops=3,577)

  • Output: base_buhin.no_kanri, base_buhin.no_eda, base_buhin.no_kansetu, base_buhin.kb_torinaigai, base_buhin.cd_torihiki, base_buhin.kb_jigyonaigai, base_buhin.cd_jigyohonbu, base_buhin.kb_status_db, base_buhin.kb_status, base_buhin.kb_kibofiletype, base_buhin.dt_kakunin, base_buhin.nm_file, base_buhin.id_irai, base_buhin.dt_update, base_buhin.cd_buhin
  • Buffers: shared hit=477325 read=11044
12. 1,569.759 14,217.861 ↓ 871.4 160,334 1

Subquery Scan on base_buhin (cost=20,303.28..734,233.18 rows=184 width=432) (actual time=157.318..14,217.861 rows=160,334 loops=1)

  • Output: base_buhin.no_kanri, base_buhin.no_eda, base_buhin.no_kansetu, base_buhin.kb_torinaigai, base_buhin.cd_torihiki, base_buhin.kb_jigyonaigai, base_buhin.cd_jigyohonbu, base_buhin.kb_status_db, base_buhin.kb_status, base_buhin.kb_kibofiletype, base_buhin.dt_kakunin, base_buhin.nm_file, base_buhin.id_irai, base_buhin.dt_update, base_buhin.cd_buhin
  • Buffers: shared hit=477325 read=11044
13. 1,510.691 12,648.102 ↓ 871.4 160,334 1

Append (cost=20,303.28..734,231.34 rows=184 width=432) (actual time=157.307..12,648.102 rows=160,334 loops=1)

  • Buffers: shared hit=477325 read=11044
14. 2,791.828 10,233.915 ↓ 41,613.7 124,841 1

Bitmap Heap Scan on reach02.d105_chosadata d105 (cost=20,303.28..689,928.94 rows=3 width=150) (actual time=157.297..10,233.915 rows=124,841 loops=1)

  • Output: d105.cd_buhin, d105.no_kanri, d105.no_eda, d105.no_kansetu, d105.kb_torinaigai, d105.cd_torihiki, d105.kb_jigyonaigai_hch, d105.cd_jigyohonbu_hch, d105.kb_status, DECODE( d105.kb_status , 0 , 5 , 1 , 3 , 2 , 4 , 3 , 1 , 8 , 7 , 9 , 6 , d105.kb_status ), d105.kb_kibofiletype, d105.dt_kakunin, CASE WHEN (d105.nm_file1 IS NOT NULL) THEN 'AIS'::text WHEN (d105.nm_file2 IS NOT NULL) THEN 'MSDSplus'::text WHEN (d105.nm_file3 IS NOT NULL) THEN 'JGP'::text WHEN (d105.nm_file6 IS NOT NULL) THEN 'chemSHERPA'::text ELSE NULL::text END, d105.id_irai, d105.dt_update
  • Recheck Cond: ((d105.cd_jigyohonbu_hch)::text = 'SOCC'::text)
  • Filter: ((d105.no_kansetu = '0'::numeric) AND ((d105.kb_jigyonaigai_hch)::text = '1'::text) AND (d105.no_eda = (SubPlan 2)))
  • Rows Removed by Filter: 3031
  • Heap Blocks: exact=19996
  • Buffers: shared hit=477289 read=3954
15. 153.383 153.383 ↓ 1.0 139,013 1

Bitmap Index Scan on d105_idx_01 (cost=0.00..20,303.28 rows=136,082 width=0) (actual time=153.383..153.383 rows=139,013 loops=1)

  • Index Cond: ((d105.cd_jigyohonbu_hch)::text = 'SOCC'::text)
  • Buffers: shared hit=401 read=3954
16.          

SubPlan (forBitmap Heap Scan)

17. 2,557.440 7,288.704 ↑ 1.0 1 127,872

Result (cost=4.45..4.46 rows=1 width=32) (actual time=0.053..0.057 rows=1 loops=127,872)

  • Output: $2
  • Buffers: shared hit=456892
18.          

Initplan (forResult)

19. 2,173.824 4,731.264 ↑ 1.0 1 127,872

Limit (cost=0.43..4.45 rows=1 width=5) (actual time=0.032..0.037 rows=1 loops=127,872)

  • Output: chosa_max.no_eda
  • Buffers: shared hit=456892
20. 2,557.440 2,557.440 ↑ 1.0 1 127,872

Index Only Scan Backward using idx_d105_chosadata_pk on reach02.d105_chosadata chosa_max (cost=0.43..4.45 rows=1 width=5) (actual time=0.020..0.020 rows=1 loops=127,872)

  • Output: chosa_max.no_eda
  • Index Cond: ((chosa_max.no_kanri = d105.no_kanri) AND (chosa_max.no_eda IS NOT NULL) AND (chosa_max.no_kansetu = d105.no_kansetu))
  • Heap Fetches: 71824
  • Buffers: shared hit=456892
21. 401.045 903.496 ↓ 196.1 35,493 1

Subquery Scan on *SELECT* 2 (cost=1,000.00..44,302.38 rows=181 width=329) (actual time=1.505..903.496 rows=35,493 loops=1)

  • Output: "*SELECT* 2".cd_buhin, NULL::bpchar, NULL::numeric, NULL::numeric, "*SELECT* 2".kb_torinaigai, "*SELECT* 2".cd_torihiki, "*SELECT* 2".kb_jigyonaigai, "*SELECT* 2".cd_jigyohonbu, 0, 5, NULL::numeric, NULL::bpchar, NULL::text, NULL::character varying, "*SELECT* 2".dt_update
  • Buffers: shared hit=36 read=7090
22. 151.742 502.451 ↓ 196.1 35,493 1

Gather (cost=1,000.00..44,299.66 rows=181 width=273) (actual time=1.487..502.451 rows=35,493 loops=1)

  • Output: d104.cd_buhin, NULL::bpchar, NULL::numeric, NULL::numeric, d104.kb_torinaigai, d104.cd_torihiki, d104.kb_jigyonaigai, d104.cd_jigyohonbu, 0, 5, NULL::numeric, NULL::bpchar, NULL::text, NULL::character varying, d104.dt_update
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=36 read=7090
23. 350.709 350.709 ↓ 157.7 11,831 3

Parallel Seq Scan on reach02.d104_michosa d104 (cost=0.00..43,281.56 rows=75 width=273) (actual time=42.636..350.709 rows=11,831 loops=3)

  • Output: d104.cd_buhin, NULL::bpchar, NULL::numeric, NULL::numeric, d104.kb_torinaigai, d104.cd_torihiki, d104.kb_jigyonaigai, d104.cd_jigyohonbu, 0, 5, NULL::numeric, NULL::bpchar, NULL::text, NULL::character varying, d104.dt_update
  • Filter: (((d104.cd_jigyohonbu)::text = 'SOCC'::text) AND ((d104.kb_jigyonaigai)::text = '1'::text))
  • Rows Removed by Filter: 421994
  • Buffers: shared hit=8632 read=25163
  • Worker 0: actual time=85.711..123.727 rows=746 loops=1
  • Buffers: shared hit=223 read=5115
  • Worker 1: actual time=42.162..774.648 rows=34705 loops=1
  • Buffers: shared hit=8373 read=12958
24. 581.202 581.202 ↑ 1.0 1 10,763

Index Scan using idx_m023_hinmokutxt_pk on reach02.m023_hinmokutxt m023 (cost=0.43..4.45 rows=1 width=33) (actual time=0.054..0.054 rows=1 loops=10,763)

  • Output: m023.cd_hinmoku, m023.nm_hinmoku_ja, m023.nm_hinmoku_en, m023.nm_hinmoku_cn, m023.dt_update, m023.id_update
  • Index Cond: ((bom.cd_hinmokuc)::text = (m023.cd_hinmoku)::text)
  • Buffers: shared hit=41281
25. 1.631 3.246 ↑ 1.0 324 1

Hash (cost=6.24..6.24 rows=324 width=7) (actual time=3.246..3.246 rows=324 loops=1)

  • Output: m021.nm_tani_ja, m021.cd_tani
  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
  • Buffers: shared hit=3
26. 1.615 1.615 ↑ 1.0 324 1

Seq Scan on reach02.m021_tani m021 (cost=0.00..6.24 rows=324 width=7) (actual time=0.035..1.615 rows=324 loops=1)

  • Output: m021.nm_tani_ja, m021.cd_tani
  • Buffers: shared hit=3
27. 0.239 0.463 ↑ 1.0 42 1

Hash (cost=1.42..1.42 rows=42 width=13) (actual time=0.463..0.463 rows=42 loops=1)

  • Output: spd001.zigyohon_ryaku_jp, spd001.zigyohon_code_f, spd001.zigyohon_code
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
28. 0.224 0.224 ↑ 1.0 42 1

Seq Scan on reach02.spd001 (cost=0.00..1.42 rows=42 width=13) (actual time=0.019..0.224 rows=42 loops=1)

  • Output: spd001.zigyohon_ryaku_jp, spd001.zigyohon_code_f, spd001.zigyohon_code
  • Buffers: shared hit=1
29. 344.416 344.416 ↑ 1.0 1 10,763

Index Scan using idx_spd018_dai_pk on reach02.spd018_dai (cost=0.42..4.26 rows=1 width=36) (actual time=0.032..0.032 rows=1 loops=10,763)

  • Output: spd018_dai.kokkaikbn, spd018_dai.daihyokey, spd018_dai.torimeiknj, spd018_dai.torimeieng
  • Index Cond: ((base_buhin.kb_torinaigai = spd018_dai.kokkaikbn) AND ((base_buhin.cd_torihiki)::text = (spd018_dai.daihyokey)::text))
  • Buffers: shared hit=42316