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...