explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fCXR

Settings
# exclusive inclusive rows x rows loops node
1. 50.672 19,534.290 ↓ 33.3 100 1

Subquery Scan on base (cost=402,946.84..402,963.75 rows=3 width=486) (actual time=19,330.846..19,534.290 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=536306 read=33660
2. 103.444 19,483.618 ↓ 17.5 10,763 1

Subquery Scan on base_data (cost=402,946.84..402,954.52 rows=615 width=486) (actual time=19,330.833..19,483.618 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=536306 read=33660
3. 152.715 19,380.174 ↓ 17.5 10,763 1

Sort (cost=402,946.84..402,948.37 rows=615 width=478) (actual time=19,330.817..19,380.174 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=536306 read=33660
4. 201.518 19,227.459 ↓ 17.5 10,763 1

Nested Loop Left Join (cost=399,498.98..402,918.35 rows=615 width=478) (actual time=16,260.366..19,227.459 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=536303 read=33660
5. 121.859 18,799.918 ↓ 17.5 10,763 1

Hash Left Join (cost=399,498.56..402,279.05 rows=615 width=508) (actual time=16,260.327..18,799.918 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=494501 read=33146
6. 120.550 18,677.580 ↓ 17.5 10,763 1

Hash Left Join (cost=399,496.51..402,272.38 rows=615 width=501) (actual time=16,259.669..18,677.580 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=494500 read=33146
7. 184.532 18,553.659 ↓ 17.5 10,763 1

Nested Loop Left Join (cost=399,486.22..402,254.34 rows=615 width=498) (actual time=16,256.252..18,553.659 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=494499 read=33144
8. 1,116.445 18,132.341 ↓ 17.5 10,763 1

Merge Left Join (cost=399,485.79..399,557.13 rows=615 width=478) (actual time=16,256.010..18,132.341 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
  • Merge Cond: ((bom.cd_hinmokuc)::text = (base_buhin.cd_buhin)::text)
  • Buffers: shared hit=454394 read=31968
9. 49.218 74.304 ↓ 5.8 3,577 1

Sort (cost=1,202.33..1,203.87 rows=615 width=46) (actual time=55.776..74.304 rows=3,577 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
  • Sort Key: bom.cd_hinmokuc
  • Sort Method: quicksort Memory: 587kB
  • Buffers: shared read=277
10. 22.476 25.086 ↓ 5.8 3,577 1

Bitmap Heap Scan on reach02.d202_bom_b bom (cost=14.73..1,173.85 rows=615 width=46) (actual time=2.706..25.086 rows=3,577 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
  • Recheck Cond: (((bom.cd_hinmoku)::text = 'A3MX2614NP'::text) AND (bom.cd_plant = 'SOCC'::bpchar))
  • Heap Blocks: exact=204
  • Buffers: shared read=277
11. 2.610 2.610 ↓ 17.1 10,504 1

Bitmap Index Scan on idx_d202_bom_b_pk (cost=0.00..14.58 rows=615 width=0) (actual time=2.610..2.610 rows=10,504 loops=1)

  • Index Cond: (((bom.cd_hinmoku)::text = 'A3MX2614NP'::text) AND (bom.cd_plant = 'SOCC'::bpchar))
  • Buffers: shared read=73
12. 2,727.837 16,941.592 ↓ 12.6 165,506 1

Sort (cost=398,283.46..398,316.22 rows=13,104 width=432) (actual time=16,125.988..16,941.592 rows=165,506 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
  • Sort Key: base_buhin.cd_buhin
  • Sort Method: quicksort Memory: 28691kB
  • Buffers: shared hit=454394 read=31691
13. 1,546.584 14,213.755 ↓ 12.2 160,334 1

Subquery Scan on base_buhin (cost=1,150.05..397,387.29 rows=13,104 width=432) (actual time=31.601..14,213.755 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=454394 read=31691
14. 1,483.399 12,667.171 ↓ 12.2 160,334 1

Append (cost=1,150.05..397,256.25 rows=13,104 width=432) (actual time=31.589..12,667.171 rows=160,334 loops=1)

  • Buffers: shared hit=454394 read=31691
15. 3,076.094 10,391.986 ↓ 340.2 124,841 1

Bitmap Heap Scan on reach02.d105_chosadata d105 (cost=1,150.05..380,985.43 rows=367 width=150) (actual time=31.576..10,391.986 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.kb_jigyonaigai_hch = '1'::bpchar) AND ((d105.cd_jigyohonbu_hch)::text = 'SOCC'::text))
  • Filter: ((d105.no_kansetu = '0'::numeric) AND (d105.no_eda = (SubPlan 2)))
  • Rows Removed by Filter: 3031
  • Heap Blocks: exact=19996
  • Buffers: shared hit=454394 read=22896
16. 27.188 27.188 ↓ 1.9 139,013 1

Bitmap Index Scan on d105_idx_01 (cost=0.00..1,149.96 rows=73,353 width=0) (actual time=27.188..27.188 rows=139,013 loops=1)

  • Index Cond: ((d105.kb_jigyonaigai_hch = '1'::bpchar) AND ((d105.cd_jigyohonbu_hch)::text = 'SOCC'::text))
  • Buffers: shared read=402
17.          

SubPlan (forBitmap Heap Scan)

18. 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=453817 read=3075
19.          

Initplan (forResult)

20. 2,045.952 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=453817 read=3075
21. 2,685.312 2,685.312 ↑ 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.021..0.021 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=453817 read=3075
22. 374.618 791.786 ↓ 2.8 35,493 1

Subquery Scan on *SELECT* 2 (cost=484.98..16,267.16 rows=12,737 width=329) (actual time=11.222..791.786 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 read=8795
23. 407.482 417.168 ↓ 2.8 35,493 1

Bitmap Heap Scan on reach02.d104_michosa d104 (cost=484.98..16,076.10 rows=12,737 width=273) (actual time=11.204..417.168 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
  • Recheck Cond: ((d104.kb_jigyonaigai = '1'::bpchar) AND ((d104.cd_jigyohonbu)::text = 'SOCC'::text))
  • Heap Blocks: exact=8615
  • Buffers: shared read=8795
24. 9.686 9.686 ↓ 2.8 35,493 1

Bitmap Index Scan on d104_idx_02 (cost=0.00..481.80 rows=12,737 width=0) (actual time=9.686..9.686 rows=35,493 loops=1)

  • Index Cond: ((d104.kb_jigyonaigai = '1'::bpchar) AND ((d104.cd_jigyohonbu)::text = 'SOCC'::text))
  • Buffers: shared read=180
25. 236.786 236.786 ↑ 1.0 1 10,763

Index Scan using idx_m023_hinmokutxt_pk on reach02.m023_hinmokutxt m023 (cost=0.43..4.39 rows=1 width=33) (actual time=0.022..0.022 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=40105 read=1176
26. 1.696 3.371 ↑ 1.0 324 1

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

  • Output: m021.nm_tani_ja, m021.cd_tani
  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
  • Buffers: shared hit=1 read=2
27. 1.675 1.675 ↑ 1.0 324 1

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

  • Output: m021.nm_tani_ja, m021.cd_tani
  • Buffers: shared hit=1 read=2
28. 0.241 0.479 ↑ 1.0 42 1

Hash (cost=1.42..1.42 rows=42 width=13) (actual time=0.479..0.479 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
29. 0.238 0.238 ↑ 1.0 42 1

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

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

Index Scan using idx_spd018_dai_pk on reach02.spd018_dai (cost=0.42..1.04 rows=1 width=36) (actual time=0.021..0.021 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=41802 read=514