maping logic jan code in mysql (after improvement)

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