maping logic jan code in mysql (after improvement)
unknown
pgsql
4 years ago
10 kB
5
Indexable
"Sort (cost=64277.97..64277.97 rows=1 width=938) (actual time=357.615..366.537 rows=13 loops=1)"
" Sort Key: check_table.jan_code"
" Sort Method: quicksort Memory: 28kB"
" Buffers: shared hit=69783 read=3068, temp read=3987 written=4076"
" CTE check_table"
" -> CTE Scan on calculation_table (cost=35294.29..35413.95 rows=5983 width=640) (actual time=287.698..334.987 rows=5983 loops=1)"
" Buffers: shared hit=45849 read=3068, temp read=3987 written=4076"
" CTE calculation_table"
" -> CTE Scan on temp_table (cost=34695.99..35294.29 rows=5983 width=640) (actual time=287.696..330.023 rows=5983 loops=1)"
" Buffers: shared hit=45849 read=3068, temp read=3987 written=4076"
" CTE temp_table"
" -> Nested Loop Left Join (cost=29661.15..34695.99 rows=5983 width=279) (actual time=287.687..319.551 rows=5983 loops=1)"
" Buffers: shared hit=45849 read=3068, temp read=3987 written=4076"
" -> Merge Right Join (cost=29660.73..30894.07 rows=5983 width=184) (actual time=287.665..307.957 rows=5983 loops=1)"
" Merge Cond: ((jan_from_upc1.jan)::text = (mid_pos.code_upc1)::text)"
" Buffers: shared hit=45849 read=3068, temp read=3987 written=4076"
" -> Nested Loop Left Join (cost=0.84..288629.54 rows=409813 width=22) (actual time=0.020..7.553 rows=1412 loops=1)"
" Buffers: shared hit=7070"
" -> Index Scan using master_maruetsu_product_jan_jan_jan_type_key on master_maruetsu_product_jan jan_from_upc1 (cost=0.42..35384.01 rows=409813 width=22) (actual time=0.005..1.015 rows=1412 loops=1)"
" Buffers: shared hit=1421"
" -> Index Only Scan using maruetsu_data_product_product_code_key on maruetsu_data_product pp_from_upc1 (cost=0.42..0.62 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1412)"
" Index Cond: (product_code = (jan_from_upc1.product_code)::text)"
" Heap Fetches: 1412"
" Buffers: shared hit=5649"
" -> Sort (cost=29659.88..29674.84 rows=5983 width=176) (actual time=287.641..297.282 rows=5983 loops=1)"
" Sort Key: mid_pos.code_upc1"
" Sort Method: quicksort Memory: 1782kB"
" Buffers: shared hit=38779 read=3068, temp read=3987 written=4076"
" -> Merge Right Join (cost=28054.75..29284.55 rows=5983 width=176) (actual time=262.963..282.967 rows=5983 loops=1)"
" Merge Cond: ((jan_from_upc2.jan)::text = (mid_pos.code_upc2)::text)"
" Buffers: shared hit=38779 read=3068, temp read=3987 written=4076"
" -> Nested Loop Left Join (cost=0.84..288629.54 rows=409813 width=22) (actual time=0.008..7.623 rows=1412 loops=1)"
" Buffers: shared hit=7070"
" -> Index Scan using master_maruetsu_product_jan_jan_jan_type_key on master_maruetsu_product_jan jan_from_upc2 (cost=0.42..35384.01 rows=409813 width=22) (actual time=0.003..1.041 rows=1412 loops=1)"
" Buffers: shared hit=1421"
" -> Index Only Scan using maruetsu_data_product_product_code_key on maruetsu_data_product pp_from_upc2 (cost=0.42..0.62 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1412)"
" Index Cond: (product_code = (jan_from_upc2.product_code)::text)"
" Heap Fetches: 1412"
" Buffers: shared hit=5649"
" -> Sort (cost=28053.90..28068.86 rows=5983 width=168) (actual time=262.949..272.420 rows=5983 loops=1)"
" Sort Key: mid_pos.code_upc2"
" Sort Method: quicksort Memory: 1782kB"
" Buffers: shared hit=31709 read=3068, temp read=3987 written=4076"
" -> Gather (cost=20000.41..27678.57 rows=5983 width=168) (actual time=232.288..261.313 rows=5983 loops=1)"
" Workers Planned: 1"
" Workers Launched: 1"
" Buffers: shared hit=31709 read=3068, temp read=3987 written=4076"
" -> Parallel Hash Left Join (cost=19000.41..26080.27 rows=3519 width=168) (actual time=232.704..249.191 rows=2992 loops=2)"
" Hash Cond: (concat('0', mid_pos.code_upc2) = (jan_from_upc3.jan)::text)"
" Buffers: shared hit=31709 read=3068, temp read=3987 written=4076"
" -> Nested Loop Left Join (cost=9500.41..15387.02 rows=3519 width=160) (actual time=99.751..139.236 rows=2992 loops=2)"
" Buffers: shared hit=28571 read=1502, temp read=1991 written=2032"
" -> Parallel Hash Left Join (cost=9499.99..13212.44 rows=3519 width=160) (actual time=99.711..122.668 rows=2992 loops=2)"
" Hash Cond: (btrim((mid_pos.product_code)::text) = (jan_from_33.jan)::text)"
" Buffers: shared hit=5647 read=1502, temp read=1991 written=2032"
" -> Parallel Seq Scan on maruetsu_pos_sales_on_process mid_pos (cost=0.00..2527.19 rows=3519 width=152) (actual time=0.016..4.362 rows=2992 loops=2)"
" Buffers: shared hit=2492"
" -> Parallel Hash (cost=6364.55..6364.55 rows=170755 width=22) (actual time=89.192..89.192 rows=204906 loops=2)"
" Buckets: 65536 Batches: 8 Memory Usage: 3360kB"
" Buffers: shared hit=3155 read=1502, temp written=1872"
" -> Parallel Seq Scan on master_maruetsu_product_jan jan_from_33 (cost=0.00..6364.55 rows=170755 width=22) (actual time=0.032..36.102 rows=204906 loops=2)"
" Buffers: shared hit=3155 read=1502"
" -> Index Only Scan using maruetsu_data_product_product_code_key on maruetsu_data_product pp_from_33 (cost=0.42..0.62 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=5983)"
" Index Cond: (product_code = (jan_from_33.product_code)::text)"
" Heap Fetches: 5727"
" Buffers: shared hit=22924"
" -> Parallel Hash (cost=6364.55..6364.55 rows=170755 width=22) (actual time=87.665..87.665 rows=204906 loops=2)"
" Buckets: 65536 Batches: 8 Memory Usage: 3360kB"
" Buffers: shared hit=3091 read=1566, temp written=1876"
" -> Parallel Seq Scan on master_maruetsu_product_jan jan_from_upc3 (cost=0.00..6364.55 rows=170755 width=22) (actual time=0.025..36.346 rows=204906 loops=2)"
" Buffers: shared hit=3091 read=1566"
" -> Index Only Scan using maruetsu_data_product_product_code_key on maruetsu_data_product pp_from_upc3 (cost=0.42..0.62 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=5983)"
" Index Cond: (product_code = (jan_from_upc3.product_code)::text)"
" Heap Fetches: 0"
" -> Nested Loop Left Join (cost=0.42..28864.01 rows=1 width=938) (actual time=287.710..357.598 rows=13 loops=1)"
" Filter: (pp.product_id IS NULL)"
" Rows Removed by Filter: 5970"
" Buffers: shared hit=69783 read=3068, temp read=3987 written=4076"
" -> CTE Scan on check_table (cost=0.00..119.66 rows=5983 width=640) (actual time=287.699..327.811 rows=5983 loops=1)"
" Buffers: shared hit=45849 read=3068, temp read=3987 written=4076"
" -> Index Scan using maruetsu_data_product_product_code_key on maruetsu_data_product pp (cost=0.42..4.79 rows=1 width=298) (actual time=0.004..0.004 rows=1 loops=5983)"
" Index Cond: ((product_code)::text = (check_table.product_code_from_else_if_condition)::text)"
" Buffers: shared hit=23934"
"Planning Time: 2.424 ms"
"Execution Time: 367.622 ms"
Editor is loading...