Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-2024-05-13· OpenAIAi2 WildChat-4.8M (AI2)

with intdata as ( select stockmove.productid as productid, stockpicking.createuid as…

  1. User

    with int_data as ( select stock_move.product_id as product_id, stock_picking.create_uid as seller_id, stock_picking.state as int_state, stock_picking_internal.lcl_state as lcl_state, stock_move.product_qty as int_quantity, stock_picking_internal_box_line.compensate_qty as compensate_qty, stock_picking_internal_box_line.receiving_qty as receiving_qty, CASE when (si.type = 'shelf' AND si.usable_qty > 0 AND (procurement_order_info.order_type != 7 or procurement_order_info.order_type is null) OR procurement_order_info.order_type = 7) AND procurement_order.remain_count > 0 AND procurement_order.stock_state in ('partial', 'done') AND procurement_order.state in ('running', 'done', 'move_exported','move_confirmed') THEN 1 else 0 end as is_pending_allocation, CASE when procurement_order_transfer_details.state in ('to_be_reviewed', 'move_confirmed', 'partial_exported','partial_cancel') and (procurement_order_transfer_details.product_qty-procurement_order_transfer_details.transfer_qty-procurement_order_transfer_details.cancel_qty) >0 THEN 1 else 0 end as is_pending_stock_picking, stock_warehouse_configuration.warehouse_location as warehouse_location, stock_move.procurement_order_id as child_erp_id, procurement_order_transfer_details.transfer_qty as transfer_qty, procurement_order_transfer_details.cancel_qty as cancel_qty, procurement_order.remain_count as remain_count, ROW_NUMBER() OVER (PARTITION BY stock_move.procurement_order_id) as row_num from stock_picking left join stock_move on stock_move.picking_id = stock_picking.id left join stock_picking_internal on stock_move.picking_id = stock_picking_internal.picking_id left join stock_picking_internal_box_line on stock_picking_internal_box_line.move_id = stock_move.id left join procurement_order_transfer_details on procurement_order_transfer_details.name = stock_move.apply_batch_no left join procurement_order_info on procurement_order_info.procurement_order_id = stock_move.procurement_order_id left join procurement_order on procurement_order.id = procurement_order_transfer_details.procurement_order_id

         left join stock_warehouse on stock_warehouse.lot_stock_id = stock_picking.location_dest_id
         left join stock_warehouse_configuration on stock_warehouse.id = stock_warehouse_configuration.id
         LEFT JOIN starmerx_inventory si ON
         procurement_order.product_id = si.product_id
         AND procurement_order.location_id = si.location_id
         where stock_picking_internal.state in ('draft','2bprint','2bmake','pushed','2bdelivery','delivery','received','cancel','packing_canceling','2bweigh_canceling','2bconfirm_canceling','2bmake_canceling','pushed_canceling','2bdelivery_canceling','exception_state') and stock_picking_internal.lcl_state in ('1','2','3','4') )  ,    
         {where_sql}
         group_data as(
         select product_id,
         seller_id,
         warehouse_location,
         sum(case when is_pending_allocation =1 and row_num = 1 then remain_count end) as to_apply,
         sum(case when is_pending_stock_picking = 1 and row_num = 1 then int_quantity-transfer_qty-cancel_qty end) as to_make,
         sum(case when lcl_state = 1 then int_quantity end) as to_lcl,
         sum(case when lcl_state = 4 then int_quantity end) as to_send,
         sum(case when int_state in ('pushed', '2bdelivery') then int_quantity-receiving_qty-compensate_qty end) as on_the_way
         from int_data
         group by product_id, seller_id,warehouse_location
         )
         select product_id,
         seller_id,
         warehouse_location,
         to_apply,
         to_lcl,
         to_make,
         to_send,
         on_the_way
         from group_data limit {page_size} offset {offset}
    

    这段sql该如何优化

  2. Assistant

    优化这个 SQL 查询可以从多个方面入手,例如减少不必要的JOIN操作、优化过滤条件、使用适当的索引、去掉不必要的子查询等。以下是几个可以尝试的优化步骤:

    1. 减少不必要的JOIN:检查哪些JOIN操作可能是不必要的,可以考虑移除它们。
    2. 索引优化:确保在所有的联接表和过滤条件上都有适当的索引。
    3. 减少计算复杂度:尝试将一些复杂的CASE WHEN表达式简化。
    4. 过滤条件优化:提早应用过滤条件,减少数据集的大小。
    5. 消除子查询:如果可能,将子查询合并到主查询中。

    改写后的SQL可能类似于以下版本,但还是需要根据具体的数据库情况进一步调整和优化:

    with int_data as (
        select 
            stock_move.product_id as product_id,
            stock_picking.create_uid as seller_id,
            stock_picking.state as int_state,
            stock_picking_internal.lcl_state as lcl_state,
            stock_move.product_qty as int_quantity,
            stock_picking_internal_box_line.compensate_qty as compensate_qty,
            stock_picking_internal_box_line.receiving_qty as receiving_qty,
            CASE
                when (
                    (si.type = 'shelf' AND si.usable_qty > 0 AND (procurement_order_info.order_type != 7 or procurement_order_info.order_type is null)) 
                    OR procurement_order_info.order_type = 7
                ) 
                AND procurement_order.remain_count > 0
                AND procurement_order.stock_state in ('partial', 'done')
                AND procurement_order.state in ('running', 'done', 'move_exported','move_confirmed')
                THEN 1 ELSE 0 
            end as is_pending_allocation, 
            CASE
                when procurement_order_transfer_details.state in ('to_be_reviewed', 'move_confirmed', 'partial_exported','partial_cancel') 
                and (procurement_order_transfer_details.product_qty - procurement_order_transfer_details.transfer_qty - procurement_order_transfer_details.cancel_qty) > 0
                THEN 1 ELSE 0 
            end as is_pending_stock_picking,
            stock_warehouse_configuration.warehouse_location as warehouse_location,
            stock_move.procurement_order_id as child_erp_id, 
            procurement_order_transfer_details.transfer_qty as transfer_qty,
            procurement_order_transfer_details.cancel_qty as cancel_qty,
            procurement_order.remain_count as remain_count, 
            ROW_NUMBER() OVER (PARTITION BY stock_move.procurement_order_id) as row_num
        from stock_picking
        left join stock_move on stock_move.picking_id = stock_picking.id
        left join stock_picking_internal on stock_move.picking_id = stock_picking_internal.picking_id
        left join stock_picking_internal_box_line on stock_picking_internal_box_line.move_id = stock_move.id
        left join procurement_order_transfer_details on procurement_order_transfer_details.name = stock_move.apply_batch_no
        left join procurement_order_info on procurement_order_info.procurement_order_id = stock_move.procurement_order_id
        left join procurement_order on procurement_order.id = procurement_order_transfer_details.procurement_order_id
        left join stock_warehouse on stock_warehouse.lot_stock_id = stock_picking.location_dest_id
        left join stock_warehouse_configuration on stock_warehouse.id = stock_warehouse_configuration.id
        LEFT JOIN starmerx_inventory si on procurement_order.product_id = si.product_id AND procurement_order.location_id = si.location_id
        where stock_picking_internal.state in ('draft', '2bprint', '2bmake', 'pushed', '2bdelivery', 'delivery', 'received', 'cancel', 'packing_canceling', '2bweigh_canceling', '2bconfirm_canceling', '2bmake_canceling', 'pushed_canceling', '2bdelivery_canceling', 'exception_state') 
          and stock_picking_internal.lcl_state in ('1', '2', '3', '4')
    ),    
    group_data as (
        select 
            product_id,
            seller_id,
            warehouse_location,
            sum(case when is_pending_allocation = 1 and row_num = 1 then remain_count else 0 end) as to_apply,
            sum(case when is_pending_stock_picking = 1 and row_num = 1 then int_quantity - transfer_qty - cancel_qty else 0 end) as to_make,
            sum(case when lcl_state = 1 then int_quantity else 0 end) as to_lcl,
            sum(case when lcl_state = 4 then int_quantity else 0 end) as to_send,
            sum(case when int_state in ('pushed', '2bdelivery') then int_quantity - receiving_qty - compensate_qty else 0 end) as on_the_way
        from int_data
        group by product_id, seller_id, warehouse_location
    ) 
    select 
        product_id,
        seller_id,
        warehouse_location,
        to_apply,
        to_lcl,
        to_make,
        to_send,
        on_the_way
    from group_data 
    limit {page_size} offset {offset}
    

    具体优化措施解释

    1. 减少不必要的JOIN:仔细检查每个LEFT JOIN,确保它们是必须的。尤其是那些没有在WHERE条件中使用的表。
    2. 索引优化:确保以下字段有索引:
      • stock_picking.id
      • stock_move.picking_id
      • stock_picking_internal.picking_id
      • stock_picking_internal_box_line.move_id
      • procurement_order_transfer_details.name
      • procurement_order_info.procurement_order_id
      • procurement_order.id
      • stock_warehouse.lot_stock_id
      • stock_warehouse_configuration.id
      • procurement_order.product_id
      • procurement_order.location_id
    3. FILTER optimization: 使用适当的索引和提前过滤减少数据集的大小。
    4. 消除子查询:合并 where_sql,提前过滤不必要的数据。

    进一步步骤:

    • 确认数据库执行计划 (EXPLAIN PLAN),看看最耗时的部分有哪些。
    • 根据实际生产环境数据进行调整和优化。
  3. User

    执行一次大概要一分钟

  4. Assistant

    如果SQL查询执行时间过长(例如一分钟),以下是一些进一步优化和改进的方法,以提高查询性能:

    1. 检查索引

    确保所有需要的表和列都有适当的索引。特别是那些用于过滤、联接和排序的列:

    -- 添加索引示例
    CREATE INDEX idx_stock_picking_id ON stock_move (picking_id);
    CREATE INDEX idx_stock_move_apply_batch_no ON stock_move (apply_batch_no);
    CREATE INDEX idx_procurement_order_id ON procurement_order_info (procurement_order_id);
    CREATE INDEX idx_stock_picking_internal_picking_id ON stock_picking_internal (picking_id);
    CREATE INDEX idx_stock_warehouse_location_dest_id ON stock_warehouse (lot_stock_id);
    -- 增加更多索引根据你的需要
    

    2. 分析和重写查询

    再次仔细分析查询,看看是否有地方可以减少或者简化:

    WITH int_data AS (
        SELECT 
            sm.product_id,
            sp.create_uid AS seller_id,
            sp.state AS int_state,
            spi.lcl_state AS lcl_state,
            sm.product_qty AS int_quantity,
            spib.compensate_qty,
            spib.receiving_qty,
            CASE
                WHEN /* Simplify this logic if possible */
                    ((si.type = 'shelf' AND si.usable_qty > 0 AND (poi.order_type != 7 OR poi.order_type IS NULL)) 
                    OR poi.order_type = 7)
                    AND po.remain_count > 0
                    AND po.stock_state IN ('partial', 'done')
                    AND po.state IN ('running', 'done', 'move_exported', 'move_confirmed')
                THEN 1 ELSE 0 
            END AS is_pending_allocation, 
            CASE
                WHEN potd.state IN ('to_be_reviewed', 'move_confirmed', 'partial_exported', 'partial_cancel') 
                    AND (potd.product_qty - potd.transfer_qty - potd.cancel_qty) > 0
                THEN 1 ELSE 0 
            END AS is_pending_stock_picking,
            swc.warehouse_location,
            sm.procurement_order_id AS child_erp_id, 
            potd.transfer_qty,
            potd.cancel_qty,
            po.remain_count, 
            ROW_NUMBER() OVER (PARTITION BY sm.procurement_order_id) AS row_num
        FROM stock_picking sp
        LEFT JOIN stock_move sm ON sm.picking_id = sp.id
        LEFT JOIN stock_picking_internal spi ON sm.picking_id = spi.picking_id
        LEFT JOIN stock_picking_internal_box_line spib ON spib.move_id = sm.id
        LEFT JOIN procurement_order_transfer_details potd ON potd.name = sm.apply_batch_no
        LEFT JOIN procurement_order_info poi ON poi.procurement_order_id = sm.procurement_order_id
        LEFT JOIN procurement_order po ON po.id = potd.procurement_order_id
        LEFT JOIN stock_warehouse sw ON sw.lot_stock_id = sp.location_dest_id
        LEFT JOIN stock_warehouse_configuration swc ON sw.id = swc.id
        LEFT JOIN starmerx_inventory si ON po.product_id = si.product_id AND po.location_id = si.location_id
        WHERE spi.state IN ('draft', '2bprint', '2bmake', 'pushed', '2bdelivery', 'delivery', 'received', 'cancel', 'packing_canceling', '2bweigh_canceling', '2bconfirm_canceling', '2bmake_canceling', 'pushed_canceling', '2bdelivery_canceling', 'exception_state') 
        AND spi.lcl_state IN ('1', '2', '3', '4')
    )
    , group_data AS (
        SELECT 
            product_id,
            seller_id,
            warehouse_location,
            SUM(CASE WHEN is_pending_allocation = 1 AND row_num = 1 THEN remain_count ELSE 0 END) AS to_apply,
            SUM(CASE WHEN is_pending_stock_picking = 1 AND row_num = 1 THEN int_quantity - transfer_qty - cancel_qty ELSE 0 END) AS to_make,
            SUM(CASE WHEN lcl_state = 1 THEN int_quantity ELSE 0 END) AS to_lcl,
            SUM(CASE WHEN lcl_state = 4 THEN int_quantity ELSE 0 END) AS to_send,
            SUM(CASE WHEN int_state IN ('pushed', '2bdelivery') THEN int_quantity - receiving_qty - compensate_qty ELSE 0 END) AS on_the_way
        FROM int_data
        GROUP BY product_id, seller_id, warehouse_location
    ) 
    SELECT 
        product_id,
        seller_id,
        warehouse_location,
        to_apply,
        to_lcl,
        to_make,
        to_send,
        on_the_way
    FROM group_data 
    LIMIT {page_size} OFFSET {offset};
    

    3. 检查查询执行计划

    使用 EXPLAIN 语句来查看查询执行计划,找出瓶颈所在:

    EXPLAIN ANALYZE WITH int_data AS (
        ...
    )
    , group_data AS (
        ...
    )
    SELECT 
        product_id,
        seller_id,
        warehouse_location,
        ...
    FROM group_data 
    LIMIT {page_size} OFFSET {offset};
    

    4. 增加物理分区和分片

    如果数据量巨大,可以考虑对表进行分区或者分片,这样可以显著提高查询性能。

    5. 数据库参数优化

    根据查询执行情况,调整数据库的配置参数(如内存、缓存、并行度等),以更好地利用系统资源。

    6. 并行处理

    部分数据库系统支持并行查询处理(如PostgreSQL),可以调高并行处理参数:

    -- For PostgreSQL, you can set:
    SET max_parallel_workers_per_gather = 4;
    

    7. 物料化视图

    如果数据不是实时要求,可以使用物料化视图来预先计算和存储查询结果,以后查询时只需读取物料化视图即可:

    CREATE MATERIALIZED VIEW int_data_mv AS
    SELECT ...
    -- 重写优化查询
    WITH NO DATA;
    
    -- 定期刷新物料视图
    REFRESH MATERIALIZED VIEW int_data_mv;
    

    通过这些步骤,应该能够显著提升查询性能。务必对优化后的查询进行详细的性能测试和验证。

  5. User

    如何分析EXPLAIN ANALYZE

  6. Assistant

    使用EXPLAIN ANALYZE分析SQL查询对于理解查询性能瓶颈非常重要。它不仅提供了查询执行计划,还包括每个操作的实际时间和行数。这有助于确认优化查询的潜在区域。下面是一个示例以及如何解读结果。

    示例查询

    EXPLAIN ANALYZE
    WITH int_data AS (
        SELECT 
            sm.product_id,
            sp.create_uid AS seller_id,
            sp.state AS int_state,
            spi.lcl_state AS lcl_state,
            sm.product_qty AS int_quantity,
            spib.compensate_qty,
            spib.receiving_qty,
            CASE
                WHEN 
                    ((si.type = 'shelf' AND si.usable_qty > 0 AND (poi.order_type != 7 OR poi.order_type IS NULL))
                    OR poi.order_type = 7)
                    AND po.remain_count > 0
                    AND po.stock_state IN ('partial', 'done')
                    AND po.state IN ('running', 'done', 'move_exported', 'move_confirmed')
                THEN 1 ELSE 0 
            END AS is_pending_allocation, 
            CASE
                WHEN potd.state IN ('to_be_reviewed', 'move_confirmed', 'partial_exported', 'partial_cancel') 
                    AND (potd.product_qty - potd.transfer_qty - potd.cancel_qty) > 0
                THEN 1 ELSE 0 
            END AS is_pending_stock_picking,
            swc.warehouse_location,
            sm.procurement_order_id AS child_erp_id, 
            potd.transfer_qty,
            potd.cancel_qty,
            po.remain_count, 
            ROW_NUMBER() OVER (PARTITION BY sm.procurement_order_id) AS row_num
        FROM stock_picking sp
        LEFT JOIN stock_move sm ON sm.picking_id = sp.id
        LEFT JOIN stock_picking_internal spi ON sm.picking_id = spi.picking_id
        LEFT JOIN stock_picking_internal_box_line spib ON spib.move_id = sm.id
        LEFT JOIN procurement_order_transfer_details potd ON potd.name = sm.apply_batch_no
        LEFT JOIN procurement_order_info poi ON poi.procurement_order_id = sm.procurement_order_id
        LEFT JOIN procurement_order po ON po.id = potd.procurement_order_id
        LEFT JOIN stock_warehouse sw ON sw.lot_stock_id = sp.location_dest_id
        LEFT JOIN stock_warehouse_configuration swc ON sw.id = swc.id
        LEFT JOIN starmerx_inventory si ON po.product_id = si.product_id AND po.location_id = si.location_id
        WHERE spi.state IN ('draft', '2bprint', '2bmake', 'pushed', '2bdelivery', 'delivery', 'received', 'cancel', 'packing_canceling', '2bweigh_canceling', '2bconfirm_canceling', '2bmake_canceling', 'pushed_canceling', '2bdelivery_canceling', 'exception_state') 
        AND spi.lcl_state IN ('1', '2', '3', '4')
    )
    , group_data AS (
        SELECT 
            product_id,
            seller_id,
            warehouse_location,
            SUM(CASE WHEN is_pending_allocation = 1 AND row_num = 1 THEN remain_count ELSE 0 END) AS to_apply,
            SUM(CASE WHEN is_pending_stock_picking = 1 AND row_num = 1 THEN int_quantity - transfer_qty - cancel_qty ELSE 0 END) AS to_make,
            SUM(CASE WHEN lcl_state = 1 THEN int_quantity ELSE 0 END) AS to_lcl,
            SUM(CASE WHEN lcl_state = 4 THEN int_quantity ELSE 0 END) AS to_send,
            SUM(CASE WHEN int_state IN ('pushed', '2bdelivery') THEN int_quantity - receiving_qty - compensate_qty ELSE 0 END) AS on_the_way
        FROM int_data
        GROUP BY product_id, seller_id, warehouse_location
    ) 
    SELECT 
        product_id,
        seller_id,
        warehouse_location,
        to_apply,
        to_lcl,
        to_make,
        to_send,
        on_the_way
    FROM group_data 
    LIMIT 10 OFFSET 0;
    

    解读输出

    EXPLAIN ANALYZE的输出是一个分层树状结构,描述了查询执行过程中各个步骤的详细信息。每个步骤包括结点类型、执行时间、行数、估价等。以下是解读的关键步骤:

    示例输出

    (注:这是一个假想的输出,实际输出可能有所不同)

    Limit  (cost=350.95..350.98 rows=10 width=44) (actual time=32.647..32.647 rows=10 loops=1)
      CTE int_data
        ->  WindowAgg  (cost=232.59..267.29 rows=2760 width=108) (actual time=9.722..29.921 rows=267 loops=1)
              ->  Sort  (cost=232.59..239.49 rows=2760 width=92) (actual time=9.714..12.039 rows=267 loops=1)
                    Sort Key: sm.procurement_order_id
                    Sort Method: quicksort  Memory: 82kB
                    ->  Hash Left Join  (cost=10.21..83.38 rows=2760 width=92) (actual time=1.698..7.990 rows=267 loops=1)
                          Hash Cond: (po.id = potd.procurement_order_id)
                          ->  Merge Left Join  (cost=0.42..68.15 rows=2760 width=96) (actual time=0.786..3.739 rows=267 loops=1)
                                Merge Cond: (sm.picking_id = sp.id)
                                ->  Index Scan using picking_id_index on stock_move sm  (cost=0.29..49.77 rows=2760 width=48) (actual time=0.046..1.891 rows=267 loops=1)
                                ->  Sort  (cost=0.13..0.15 rows=8 width=48) (actual time=0.737..0.742 rows=267 loops=1)
                                      Sort Key: sp.id
                                      Sort Method: quicksort  Memory: 82kB
                                      ->  Seq Scan on stock_picking sp  (cost=0.00..0.09 rows=8 width=48) (actual time=0.017..0.020 rows=267 loops=1)
                                              Filter: (state = ANY ('{draft, 2bprint, 2bmake, pushed, 2bdelivery, delivery, received, cancel, packing_canceling, 2bweigh_canceling, 2bconfirm_canceling, 2bmake_canceling, pushed_canceling, 2bdelivery_canceling, exception_state}'::text[]))
    ...
    

    关键步骤解析

    1. Limit层

      • Limit (cost=350.95..350.98 rows=10 width=44) (actual time=32.647..32.647 rows=10 loops=1)
      • 表明查询最终返回10行数据。
      • actual time=32.647..32.647 指出从开始到结束花费了大约32.647毫秒。
    2. CTE int_data

      • WindowAgg (cost=232.59..267.29 rows=2760 width=108) (actual time=9.722..29.921 rows=267 loops=1)
      • 这个部分显示了Window函数的处理情况。
      • 高的实际时间可能表明在这里需要优化。
    3. Sort

      • Sort (cost=232.59..239.49 rows=2760 width=92) (actual time=9.714..12.039)
      • Sort操作非常快,但在大数据集上可能会变慢。关键是要看看内存使用情况。
      • Sort Method: quicksort Memory: 82kB显示了排序方法和内存用量,82kB是可以接受的。
    4. Hash Left Join

      • Hash Left Join (cost=10.21..83.38 rows=2760 width=92) (actual time=1.698..7.990 rows=267 loops=1)
      • 这里显示了两个大表之间的哈希连接。关注actual time和rows,确保执行时间和行数符合预期。
      • 若发现耗时很长,可以考虑重新评估这部分的连接条件是否适当。
    5. Merge Left Join

      • Merge Left Join (cost=0.42..68.15 rows=2760 width=96) (actual time=0.786..3.739 rows=267 loops=1)
      • 此步骤表示进行一次合并连接。actual time和rows再一次是关键。
      • Merge Cond: (sm.picking_id = sp.id) 表示连接条件。
    6. Index Scan

      • Index Scan using picking_id_index on stock_move sm (cost=0.29..49.77 rows=2760 width=48) (actual time=0.046..1.891 rows=267 loops=1)
      • 确保有适当的索引以优化查询,并关注索引扫描的时间和行数。
      • Index Scan通常比Seq Scan (全表扫描)快,可以利用字段索引。

    如何优化

    通过理解EXPLAIN ANALYZE中的输出,可以确定查询中的瓶颈所在。下面列出了一些潜在的优化措施:

    1. 优化表索引

      • 确保经常参与JOIN和过滤的列有适当的索引。
      • 例如:CREATE INDEX idx_stock_move_picking_id ON stock_move(picking_id);
    2. 调整过滤条件顺序

      • 把最能减少数据集大小的过滤条件放在前面。
    3. 减少数据扫描量

      • 通过改善过滤条件或联接条件来减少扫描的数据量。
    4. 分区表

      • 如果表数据量非常大,考虑对表进行分区处理。
    5. 物料化视图

      • 如果数据变化不频繁,可以使用物料化视图来存储中间结果。
    6. 调整数据库配置参数

      • 根据查询特性,调整优化参数如work_mem、maintenance_work_mem等。

    总结

    EXPLAIN ANALYZE是一个非常强大的工具,可以帮助你深入理解和优化SQL查询。通过仔细分析执行计划中的每一个步骤,识别出性能瓶颈,应用适当的优化技术,可以显著提高查询性能。

  7. User

    Limit (cost=138712190.36..138712190.96 rows=30 width=148) (actual time=78723.938..78724.069 rows=30 loops=1) CTE int_data -> WindowAgg (cost=18195447.93..60425583.20 rows=260226685 width=86) (actual time=18565.017..54065.328 rows=12098137 loops=1) -> Nested Loop Left Join (cost=18195447.93..48064815.66 rows=260226685 width=86) (actual time=18564.987..42105.338 rows=12098137 loops=1) -> Nested Loop Left Join (cost=18195447.50..28929040.38 rows=40178871 width=83) (actual time=18564.984..29956.555 rows=4894643 loops=1) -> Merge Left Join (cost=18195447.35..18406777.67 rows=40178871 width=83) (actual time=18564.964..24188.472 rows=4894643 loops=1) Merge Cond: (stock_move.procurement_order_id = procurement_order_info.procurement_order_id)

  8. Assistant

    从提供的 EXPLAIN ANALYZE 输出中可以看到,查询执行计划包含了几个关键步骤,每个步骤都使用了不同的连接算法。时间在各个步骤上的大致分布如下:

    Limit (cost=138712190.36..138712190.96 rows=30 width=148) (actual time=78723.938..78724.069 rows=30 loops=1)
    CTE int_data
    -> WindowAgg (cost=18195447.93..60425583.20 rows=260226685 width=86) (actual time=18565.017..54065.328 rows=12098137 loops=1)
    -> Nested Loop Left Join (cost=18195447.93..48064815.66 rows=260226685 width=86) (actual time=18564.987..42105.338 rows=12098137 loops=1)
    -> Nested Loop Left Join (cost=18195447.50..28929040.38 rows=40178871 width=83) (actual time=18564.984..29956.555 rows=4894643 loops=1)
    -> Merge Left Join (cost=18195447.35..18406777.67 rows=40178871 width=83) (actual time=18564.964..24188.472 rows=4894643 loops=1)
    Merge Cond: (stock_move.procurement_order_id = procurement_order_info.procurement_order_id)
    

    诊断和优化步骤

    1. 主要存在的问题:

      • 大量数据在多个步骤中进行处理。尤其是 WindowAgg 和 Nested Loop 连接花费了很长时间。
      • Nested Loop Left Join 和 Merge Left Join 操作时间较长,这可能由于缺少合适的索引支持。
    2. 优化方向:

    • 优化索引:
      • 确保在连接条件和过滤条件所用的列上有适当的索引。例如,在 procurement_order_id、picking_id 等列上创建索引。
    CREATE INDEX idx_procurement_order_id ON stock_move (procurement_order_id);
    CREATE INDEX idx_picking_id ON stock_move (picking_id);
    CREATE INDEX idx_procurement_order_info_id ON procurement_order_info (procurement_order_id);
    
    • 避免过多的嵌套循环:
      • Nested Loop 在处理大数据集时性能较差,尝试使用 Hash Join 或 Merge Join。确定生成这些Nested Loop的条件,将其替换或优化。

    重新评估复杂表达式:

    • 检查复杂的CASE表达式和计算,看看是否可以简化或提前计算,以减少合并后的数据量。

    调整查询结构:

    • 如果有可能,将CTE int_data物化为一个临时表,减少多次计算的开销。
    CREATE TEMP TABLE temp_int_data AS
    WITH int_data AS (
        ...
    )
    SELECT * FROM int_data;
    

    然后在主查询中使用这个临时表。

    调整计划和相应输出

    让我们假设我们进行了上述优化步骤并重新运行 EXPLAIN ANALYZE。希望看到的是较短的执行时间、减少的“实际时间”、更合理的行数和成本。

    示例重新优化查询

    -- 创建必要索引:
    CREATE INDEX idx_stock_move_procurement_order ON stock_move (procurement_order_id);
    CREATE INDEX idx_stock_move_picking_id ON stock_move (picking_id);
    CREATE INDEX idx_procurement_order_info ON procurement_order_info (procurement_order_id);
    
    -- 优化查询,将CTE物化为临时表:
    CREATE TEMP TABLE temp_int_data AS
    WITH int_data AS (
        SELECT 
            sm.product_id,
            sp.create_uid AS seller_id,
            sp.state AS int_state,
            spi.lcl_state AS lcl_state,
            sm.product_qty AS int_quantity,
            spib.compensate_qty,
            spib.receiving_qty,
            CASE
                WHEN 
                    ((si.type = 'shelf' AND si.usable_qty > 0 AND (poi.order_type != 7 OR poi.order_type IS NULL))
                    OR poi.order_type = 7)
                    AND po.remain_count > 0
                    AND po.stock_state IN ('partial', 'done')
                    AND po.state IN ('running', 'done', 'move_exported', 'move_confirmed')
                THEN 1 ELSE 0 
            END AS is_pending_allocation, 
            CASE
                WHEN potd.state IN ('to_be_reviewed', 'move_confirmed', 'partial_exported', 'partial_cancel') 
                    AND (potd.product_qty - potd.transfer_qty - potd.cancel_qty) > 0
                THEN 1 ELSE 0 
            END AS is_pending_stock_picking,
            swc.warehouse_location,
            sm.procurement_order_id AS child_erp_id, 
            potd.transfer_qty,
            potd.cancel_qty,
            po.remain_count, 
            ROW_NUMBER() OVER (PARTITION BY sm.procurement_order_id) AS row_num
        FROM stock_picking sp
        LEFT JOIN stock_move sm ON sm.picking_id = sp.id
        LEFT JOIN stock_picking_internal spi ON sm.picking_id = spi.picking_id
        LEFT JOIN stock_picking_internal_box_line spib ON spib.move_id = sm.id
        LEFT JOIN procurement_order_transfer_details potd ON potd.name = sm.apply_batch_no
        LEFT JOIN procurement_order_info poi ON poi.procurement_order_id = sm.procurement_order_id
        LEFT JOIN procurement_order po ON po.id = potd.procurement_order_id
        LEFT JOIN stock_warehouse sw ON sw.lot_stock_id = sp.location_dest_id
        LEFT JOIN stock_warehouse_configuration swc ON sw.id = swc.id
        LEFT JOIN starmerx_inventory si ON po.product_id = si.product_id AND po.location_id = si.location_id
        WHERE spi.state IN ('draft', '2bprint', '2bmake', 'pushed', '2bdelivery', 'delivery', 'received', 'cancel', 'packing_canceling', '2bweigh_canceling', '2bconfirm_canceling', '2bmake_canceling', 'pushed_canceling', '2bdelivery_canceling', 'exception_state') 
        AND spi.lcl_state IN ('1', '2', '3', '4')
    )
    SELECT * FROM int_data;
    
    -- 查询从临时表中提取数据:
    WITH group_data AS (
        SELECT 
            product_id,
            seller_id,
            warehouse_location,
            SUM(CASE WHEN is_pending_allocation = 1 AND row_num = 1 THEN remain_count ELSE 0 END) AS to_apply,
            SUM(CASE WHEN is_pending_stock_picking = 1 AND row_num = 1 THEN int_quantity - transfer_qty - cancel_qty ELSE 0 END) AS to_make,
            SUM(CASE WHEN lcl_state = 1 THEN int_quantity ELSE 0 END) AS to_lcl,
            SUM(CASE WHEN lcl_state = 4 THEN int_quantity ELSE 0 END) AS to_send,
            SUM(CASE WHEN int_state IN ('pushed', '2bdelivery') THEN int_quantity - receiving_qty - compensate_qty ELSE 0 END) AS on_the_way
        FROM temp_int_data
        GROUP BY product_id, seller_id, warehouse_location
    ) 
    SELECT 
        product_id,
        seller_id,
        warehouse_location,
        to_apply,
        to_lcl,
        to_make,
        to_send,
        on_the_way
    FROM group_data 
    LIMIT {page_size} OFFSET {offset};
    

    重新运行这次查询并获取 EXPLAIN ANALYZE 的结果,查看具体优化效果。通过这种迭代过程和尝试多种优化策略,可以逐步找到最适合当前数据库环境的优化方案。

    进一步的细节分析

    此外,通过结合数据库实际进行的性能测试来查看哪些部分是否还需要进一步的调优,最终要创建高效的索引策略和进行查询结构重组。

  9. User

    sql如何查看索引

  10. Assistant

    使用SQL查看数据库中的索引信息,可以帮助我们了解查询过程中使用的索引以及知道哪些列上已有索引,从而更好地优化查询。不同的数据库管理系统(DBMS)有不同的方式查看索引。下面是一些常见的DBMS如何查看索引的方法:

    PostgreSQL

    在PostgreSQL中,可以查询系统视图pg_indexes或者使用\d命令查看索引:

    查询系统视图pg_indexes

    SELECT
        schemaname,
        tablename,
        indexname,
        indexdef
    FROM
        pg_indexes
    WHERE
        tablename = 'your_table_name';
    

    使用 \d 命令

    在psql命令行工具中,你可以使用\d命令查看表的索引:

    \d your_table_name
    

    或者使用 \di 查看整个数据库的索引:

    \di
    

    示例

    SELECT
        schemaname,
        tablename,
        indexname,
        indexdef
    FROM
        pg_indexes
    WHERE
        tablename = 'stock_move';
    

    MySQL

    在MySQL中,可以使用SHOW INDEX语句查看索引:

    SHOW INDEX FROM your_table_name;
    

    示例

    SHOW INDEX FROM stock_move;
    

    SQL Server

    在SQL Server中,可以通过系统存储过程sp_helpindex或查询系统视图获得索引信息:

    使用系统存储过程sp_helpindex

    EXEC sp_helpindex 'your_table_name';
    

    查询系统视图

    SELECT
        i.name AS index_name,
        i.type_desc AS index_type,
        c.name AS column_name
    FROM
        sys.indexes i
    JOIN
        sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    JOIN
        sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE
        i.object_id = OBJECT_ID('your_table_name');
    

    示例

    EXEC sp_helpindex 'stock_move';
    
    SELECT
        i.name AS index_name,
        i.type_desc AS index_type,
        c.name AS column_name
    FROM
        sys.indexes i
    JOIN
        sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    JOIN
        sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE
        i.object_id = OBJECT_ID('stock_move');
    

    Oracle

    在Oracle中,可以从USER_INDEXES和USER_IND_COLUMNS视图中获取索引信息:

    SELECT
        index_name,
        index_type,
        table_name
    FROM
        user_indexes
    WHERE
        table_name = 'YOUR_TABLE_NAME';
    
    SELECT
        index_name,
        column_name
    FROM
        user_ind_columns
    WHERE
        table_name = 'YOUR_TABLE_NAME';
    

    示例

    SELECT
        index_name,
        index_type,
        table_name
    FROM
        user_indexes
    WHERE
        table_name = 'STOCK_MOVE';
    
    SELECT
        index_name,
        column_name
    FROM
        user_ind_columns
    WHERE
        table_name = 'STOCK_MOVE';
    

    通过这些查询,你可以列出特定表上的所有索引并查看每个索引所涉及的列。这将为查询的性能调优提供有价值的参考信息,例如添加新索引或修改现有索引。

  11. User

    Limit (cost=138712379.61..138712380.21 rows=30 width=148) (actual time=80864.399..80864.541 rows=30 loops=1) CTE int_data -> WindowAgg (cost=18195630.46..60425772.45 rows=260226685 width=86) (actual time=19357.546..55366.238 rows=12098156 loops=1) -> Nested Loop Left Join (cost=18195630.46..48065004.91 rows=260226685 width=86) (actual time=19357.515..43371.015 rows=12098156 loops=1) -> Nested Loop Left Join (cost=18195630.03..28929229.64 rows=40178871 width=83) (actual time=19357.501..30984.457 rows=4894662 loops=1) -> Merge Left Join (cost=18195629.88..18406966.93 rows=40178871 width=83) (actual time=19357.472..25161.015 rows=4894662 loops=1) Merge Cond: (stock_move.procurement_order_id = procurement_order_info.procurement_order_id)

  12. Assistant

    你提供的查询执行计划显示了查询在多种操作上花费了大量时间,包括WindowAgg和多个嵌套的Nested Loop连接。这通常说明查询处理了非常多的数据行,并且可能缺乏适当的索引支持。以下是一些具体的优化建议和步骤:

    1. 确保适当的索引

    首先,确保在连接和过滤条件的列上有适当的索引。对于这个查询,至少应在以下列上有索引:

    • stock_move.procurement_order_id
    • stock_move.picking_id
    • procurement_order_info.procurement_order_id
    • procurement_order.id

    SQL 创建索引示例:

    CREATE INDEX idx_stock_move_procurement_order_id ON stock_move(procurement_order_id);
    CREATE INDEX idx_stock_move_picking_id ON stock_move(picking_id);
    CREATE INDEX idx_procurement_order_info_id ON procurement_order_info(procurement_order_id);
    CREATE INDEX idx_procurement_order_id ON procurement_order(id);
    

    2. 优化WindowAgg

    WindowAgg操作处理了很多数据行,这可能是一个性能瓶颈。可以尝试减少数据量或者只在必要时应用窗口函数。也可以把复杂的计算提前物化为物料化视图或临时表。

    3. 避免嵌套循环连接

    嵌套循环连接在处理大量数据时会非常慢。如果索引调整后仍旧使用Nested Loop, 尝试强制使用其他连接方法,如哈希连接。以下是一个强制使用Hash Join的示例:

    SET enable_nestloop = OFF;
    

    4. 分析并重写查询

    如果上述优化并不能显著提升性能,可以考虑重写查询以改善其结构。

    优化后的查询和物料化视图示例

    -- 临时禁用嵌套循环连接
    SET enable_nestloop = OFF;
    
    -- 创建必要索引:
    CREATE INDEX idx_stock_move_procurement_order_id ON stock_move(procurement_order_id);
    CREATE INDEX idx_stock_move_picking_id ON stock_move(picking_id);
    CREATE INDEX idx_procurement_order_info_id ON procurement_order_info(procurement_order_id);
    CREATE INDEX idx_procurement_order_id ON procurement_order(id);
    
    -- 可选: 创建物料化视图来减少重复计算
    CREATE MATERIALIZED VIEW mat_view_int_data AS
    WITH int_data AS (
        SELECT 
            sm.product_id,
            sp.create_uid AS seller_id,
            sp.state AS int_state,
            spi.lcl_state AS lcl_state,
            sm.product_qty AS int_quantity,
            spib.compensate_qty,
            spib.receiving_qty,
            CASE
                WHEN 
                    ((si.type = 'shelf' AND si.usable_qty > 0 AND (poi.order_type != 7 OR poi.order_type IS NULL))
                    OR poi.order_type = 7)
                    AND po.remain_count > 0
                    AND po.stock_state IN ('partial', 'done')
                    AND po.state IN ('running', 'done', 'move_exported', 'move_confirmed')
                THEN 1 ELSE 0 
            END AS is_pending_allocation, 
            CASE
                WHEN potd.state IN ('to_be_reviewed', 'move_confirmed', 'partial_exported', 'partial_cancel') 
                    AND (potd.product_qty - potd.transfer_qty - potd.cancel_qty) > 0
                THEN 1 ELSE 0 
            END AS is_pending_stock_picking,
            swc.warehouse_location,
            sm.procurement_order_id AS child_erp_id, 
            potd.transfer_qty,
            potd.cancel_qty,
            po.remain_count, 
            ROW_NUMBER() OVER (PARTITION BY sm.procurement_order_id) AS row_num
        FROM stock_picking sp
        LEFT JOIN stock_move sm ON sm.picking_id = sp.id
        LEFT JOIN stock_picking_internal spi ON sm.picking_id = spi.picking_id
        LEFT JOIN stock_picking_internal_box_line spib ON spib.move_id = sm.id
        LEFT JOIN procurement_order_transfer_details potd ON potd.name = sm.apply_batch_no
        LEFT JOIN procurement_order_info poi ON poi.procurement_order_id = sm.procurement_order_id
        LEFT JOIN procurement_order po ON po.id = potd.procurement_order_id
        LEFT JOIN stock_warehouse sw ON sw.lot_stock_id = sp.location_dest_id
        LEFT JOIN stock_warehouse_configuration swc ON sw.id = swc.id
        LEFT JOIN starmerx_inventory si ON po.product_id = si.product_id AND po.location_id = si.location_id
        WHERE spi.state IN ('draft', '2bprint', '2bmake', 'pushed', '2bdelivery', 'delivery', 'received', 'cancel', 'packing_canceling', '2bweigh_canceling', '2bconfirm_canceling', '2bmake_canceling', 'pushed_canceling', '2bdelivery_canceling', 'exception_state') 
        AND spi.lcl_state IN ('1', '2', '3', '4')
    );
    
    -- 再次执行查询时使用物料化视图
    WITH group_data AS (
        SELECT 
            product_id,
            seller_id,
            warehouse_location,
            SUM(CASE WHEN is_pending_allocation = 1 AND row_num = 1 THEN remain_count ELSE 0 END) AS to_apply,
            SUM(CASE WHEN is_pending_stock_picking = 1 AND row_num = 1 THEN int_quantity - transfer_qty - cancel_qty ELSE 0 END) AS to_make,
            SUM(CASE WHEN lcl_state = 1 THEN int_quantity ELSE 0 END) AS to_lcl,
            SUM(CASE WHEN lcl_state = 4 THEN int_quantity ELSE 0 END) AS to_send,
            SUM(CASE WHEN int_state IN ('pushed', '2bdelivery') THEN int_quantity - receiving_qty - compensate_qty ELSE 0 END) AS on_the_way
        FROM mat_view_int_data
        GROUP BY product_id, seller_id, warehouse_location
    ) 
    SELECT 
        product_id,
        seller_id,
        warehouse_location,
        to_apply,
        to_lcl,
        to_make,
        to_send,
        on_the_way
    FROM group_data 
    LIMIT {page_size} OFFSET {offset};
    

    重新分析执行计划

    在应用这些优化后,再次运行 EXPLAIN ANALYZE 以检查优化是否生效。期待看到的结果包括:

    • 总执行时间的显著减少。
    • Nested Loop Join 的减少或者完全消除,替换为更高效的 Hash Join 或者 Merge Join。
    • 数据处理步骤中的显著优化,特别是 WindowAgg。

    总结

    通过适当索引、禁用嵌套循环连接、考虑使用物料化视图和优化查询结构,可以显著提高查询性能。继续监测并通过不同优化策略的结合,找到最适合你的具体查询工作负载的方法。

  13. User

    这一段代表了什么意思呢

  14. Assistant

    这段执行计划输出的内容提供了有关查询执行过程的详细信息。为了更好地理解这一段,我们可以逐步解析每个部分。下面是对这段输出的逐步解释:

    Limit (cost=138712379.61..138712380.21 rows=30 width=148) (actual time=80864.399..80864.541 rows=30 loops=1)
       CTE int_data
       -> WindowAgg (cost=18195630.46..60425772.45 rows=260226685 width=86) (actual time=19357.546..55366.238 rows=12098156 loops=1)
          -> Nested Loop Left Join (cost=18195630.46..48065004.91 rows=260226685 width=86) (actual time=19357.515..43371.015 rows=12098156 loops=1)
             -> Nested Loop Left Join (cost=18195630.03..28929229.64 rows=40178871 width=83) (actual time=19357.501..30984.457 rows=4894662 loops=1)
                -> Merge Left Join (cost=18195629.88..18406966.93 rows=40178871 width=83) (actual time=19357.472..25161.015 rows=4894662 loops=1)
                   Merge Cond: (stock_move.procurement_order_id = procurement_order_info.procurement_order_id)
    

    逐层解析

    1. Limit

      Limit (cost=138712379.61..138712380.21 rows=30 width=148) (actual time=80864.399..80864.541 rows=30 loops=1)
      
      • Limit: 该操作用于限制结果集的行数。
        • cost=138712379.61..138712380.21: 这是查询优化器估算的成本。起始成本为138,712,379.61,结束成本为138,712,380.21。
        • rows=30: 预计返回的行数是30行。
        • width=148: 每行的宽度为148字节。
        • actual time=80864.399..80864.541: 实际执行时间。开始时间80864.399ms,结束时间80864.541ms,总耗时约为0.142ms。
        • rows=30: 实际返回的行数为30行。
        • loops=1: 循环次数为1次。
    2. CTE int_data 和 WindowAgg

      CTE int_data
      -> WindowAgg (cost=18195630.46..60425772.45 rows=260226685 width=86) (actual time=19357.546..55366.238 rows=12098156 loops=1)
      
      • CTE int_data: 公用表表达式(CTE)的名称。
      • WindowAgg: 窗口聚合函数,用于执行窗口函数。
        • cost=18195630.46..60425772.45: 查询优化器估算的起始成本和结束成本。
        • rows=260226685: 预计处理的行数为260,226,685行。
        • width=86: 每行的宽度为86字节。
        • actual time=19357.546..55366.238: 实际执行时间。开始时间19357.546ms,结束时间55366.238ms,总耗时约为36009ms。
        • rows=12098156: 实际处理的行数为12,098,156行。
        • loops=1: 循环次数为1次。
    3. Nested Loop Left Join

      -> Nested Loop Left Join (cost=18195630.46..48065004.91 rows=260226685 width=86) (actual time=19357.515..43371.015 rows=12098156 loops=1)
      
      • Nested Loop Left Join: 嵌套循环左连接。
        • cost=18195630.46..48065004.91: 起始成本和结束成本。
        • rows=260226685: 预计处理的行数为260,226,685行。
        • width=86: 每行的宽度为86字节。
        • actual time=19357.515..43371.015: 实际执行时间。开始时间19357.515ms,结束时间43371.015ms,总耗时约为24013ms。
        • rows=12098156: 实际处理的行数为12,098,156行。
        • loops=1: 循环次数为1次。
    4. Nested Loop Left Join

      -> Nested Loop Left Join (cost=18195630.03..28929229.64 rows=40178871 width=83) (actual time=19357.501..30984.457 rows=4894662 loops=1)
      
      • Nested Loop Left Join: 嵌套循环左连接。
        • cost=18195630.03..28929229.64: 起始成本和结束成本。
        • rows=40178871: 预计处理的行数为40,178,871行。
        • width=83: 每行的宽度为83字节。
        • actual time=19357.501..30984.457: 实际执行时间。开始时间19357.501ms,结束时间30984.457ms,总耗时约为11627ms。
        • rows=4894662: 实际处理的行数为4,894,662行。
        • loops=1: 循环次数为1次。
    5. Merge Left Join

      -> Merge Left Join (cost=18195629.88..18406966.93 rows=40178871 width=83) (actual time=19357.472..25161.015 rows=4894662 loops=1)
      
      • Merge Left Join: 合并左连接。
        • cost=18195629.88..18406966.93: 起始成本和结束成本。
        • rows=40178871: 预计处理的行数为40,178,871行。
        • width=83: 每行的宽度为83字节。
        • actual time=19357.472..25161.015: 实际执行时间。开始时间19357.472ms,结束时间25161.015ms,总耗时约为5804ms。
        • rows=4894662: 实际处理的行数为4,894,662行。
        • loops=1: 循环次数为1次。
    6. Merge Cond

      Merge Cond: (stock_move.procurement_order_id = procurement_order_info.procurement_order_id)
      
      • Merge Cond: 合并条件。
        • (stock_move.procurement_order_id = procurement_order_info.procurement_order_id): 指明连接条件是在stock_move.procurement_order_id和procurement_order_info.procurement_order_id之间进行合并。

    总结

    从以上执行计划可以看出,主要的性能瓶颈包括:

    1. WindowAgg 操作:处理了大量行,花费了很长时间。
    2. 多个嵌套的 Nested Loop Left Join:处理了大量行,执行时间较长。
    3. 大数据集的操作:这些连接处理了数以亿计的行。

    为了优化这段查询及其性能,建议采取以下措施:

    • 确保适当的索引:在连接和过滤条件所用的列上创建适当的索引,大幅提升连接操作的性能。
    • 重写查询:如果可能,尝试重写查询以减少复杂度和数据处理量。
    • 使用物料化视图:对于复杂的计算和中间结果,可以考虑物化视图,以减少重复计算。
    • 禁用 Nested Loop:如果适用,强制使用 Hash Join 或 Merge Join,通过为SQL配置提供建议或使用提示,减少 Nested Loop 带来的性能问题。

    通过对执行计划的分析和扰,采取针对性措施,可以显著提升查询性能。

  15. User

    如何禁用Nested Loop

  16. Assistant

    在某些情况下,嵌套循环连接 (Nested Loop Join) 的性能可能不佳,特别是在处理大量数据时。这时,可以尝试禁用 Nested Loop Join,迫使查询优化器使用其他连接方法,例如 Hash Join 或 Merge Join。不同的数据库系统提供不同的方法来控制连接策略。

    PostgreSQL

    在 PostgreSQL 中,可以通过设置会话级别或查询级别的配置参数来禁用 Nested Loop Join。这里有两种方法:一种是设置会话参数,另一种是使用配置选项。

    会话级别禁用 Nested Loop Join

    SET enable_nestloop = OFF;
    

    这是一个会话级别的设置,当前会话中的所有查询在该设置启用时都不会使用 Nested Loop Join。

    恢复默认设置

    SET enable_nestloop = ON;
    

    要重新启用 Nested Loop Join,只需将该参数设回 ON。

    示例查询

    SET enable_nestloop = OFF;
    
    -- 执行你的查询
    WITH int_data AS (
        SELECT 
            sm.product_id,
            sp.create_uid AS seller_id,
            sp.state AS int_state,
            spi.lcl_state AS lcl_state,
            sm.product_qty AS int_quantity,
            spib.compensate_qty,
            spib.receiving_qty,
            CASE
                WHEN 
                    ((si.type = 'shelf' AND si.usable_qty > 0 AND (poi.order_type != 7 OR poi.order_type IS NULL))
                    OR poi.order_type = 7)
                    AND po.remain_count > 0
                    AND po.stock_state IN ('partial', 'done')
                    AND po.state IN ('running', 'done', 'move_exported', 'move_confirmed')
                THEN 1 ELSE 0 
            END AS is_pending_allocation, 
            CASE
                WHEN potd.state IN ('to_be_reviewed', 'move_confirmed', 'partial_exported', 'partial_cancel') 
                    AND (potd.product_qty - potd.transfer_qty - potd.cancel_qty) > 0
                THEN 1 ELSE 0 
            END AS is_pending_stock_picking,
            swc.warehouse_location,
            sm.procurement_order_id AS child_erp_id, 
            potd.transfer_qty,
            potd.cancel_qty,
            po.remain_count, 
            ROW_NUMBER() OVER (PARTITION BY sm.procurement_order_id) AS row_num
        FROM stock_picking sp
        LEFT JOIN stock_move sm ON sm.picking_id = sp.id
        LEFT JOIN stock_picking_internal spi ON sm.picking_id = spi.picking_id
        LEFT JOIN stock_picking_internal_box_line spib ON spib.move_id = sm.id
        LEFT JOIN procurement_order_transfer_details potd ON potd.name = sm.apply_batch_no
        LEFT JOIN procurement_order_info poi ON poi.procurement_order_id = sm.procurement_order_id
        LEFT JOIN procurement_order po ON po.id = potd.procurement_order_id
        LEFT JOIN stock_warehouse sw ON sw.lot_stock_id = sp.location_dest_id
        LEFT JOIN stock_warehouse_configuration swc ON sw.id = swc.id
        LEFT JOIN starmerx_inventory si ON po.product_id = si.product_id AND po.location_id = si.location_id
        WHERE spi.state IN ('draft', '2bprint', '2bmake', 'pushed', '2bdelivery', 'delivery', 'received', 'cancel', 'packing_canceling', '2bweigh_canceling', '2bconfirm_canceling', '2bmake_canceling', 'pushed_canceling', '2bdelivery_canceling', 'exception_state') 
        AND spi.lcl_state IN ('1', '2', '3', '4')
    )
    , group_data AS (
        SELECT 
            product_id,
            seller_id,
            warehouse_location,
            SUM(CASE WHEN is_pending_allocation = 1 AND row_num = 1 THEN remain_count ELSE 0 END) AS to_apply,
            SUM(CASE WHEN is_pending_stock_picking = 1 AND row_num = 1 THEN int_quantity - transfer_qty - cancel_qty ELSE 0 END) AS to_make,
            SUM(CASE WHEN lcl_state = 1 THEN int_quantity ELSE 0 END) AS to_lcl,
            SUM(CASE WHEN lcl_state = 4 THEN int_quantity ELSE 0 END) AS to_send,
            SUM(CASE WHEN int_state IN ('pushed', '2bdelivery') THEN int_quantity - receiving_qty - compensate_qty ELSE 0 END) AS on_the_way
        FROM int_data
        GROUP BY product_id, seller_id, warehouse_location
    ) 
    SELECT 
        product_id,
        seller_id,
        warehouse_location,
        to_apply,
        to_lcl,
        to_make,
        to_send,
        on_the_way
    FROM group_data 
    LIMIT 30;
    
    -- 恢复默认设置
    SET enable_nestloop = ON;
    

    MySQL

    在 MySQL 中,可以通过控制优化器提示来禁用 Nested Loop Join。MySQL 不允许直接禁用特定的连接方法,但可以通过提示来影响优化器的选择。

    使用优化器提示

    通过在查询中包含优化器提示,可以影响 MySQL 优化器的决策。例如,要禁用 Nested Loop Join,可以提示使用 Hash Join或Merge Join。

    SELECT /*+ MRR() */ 
        -- 这里是你的查询
        ...
    

    SQL Server

    在 SQL Server 中,使用查询提示(query hints)来禁用 Nested Loop Join。

    使用查询提示

    SELECT /*+ OPTION (HASH JOIN) */
        -- 这里是你的查询
        ...
    

    Oracle

    在 Oracle 中,可以使用提示来禁用 Nested Loop Join。

    使用查询提示

    SELECT /*+ USE_HASH(A B) */
        -- 这里是你的查询
        ...
    

    总结

    禁用 Nested Loop Join 可以在特定情况下显著提高查询性能,尤其是处理大数据集时。然而,禁用某种连接方法也是一种折衷,需具体问题具体分析。尝试上述方法来调整查询,并通过分析EXPLAIN ANALYZE输出,确认这些调整的实际效果。通过这样的方法,才能逐步找到最适合你的具体查询和数据库环境的优化策略。

  17. User

    [ {"QUERY PLAN":"Limit (cost=138770260.27..138770260.87 rows=30 width=148) (actual time=78558.978..78559.107 rows=30 loops=1)"}, {"QUERY PLAN":" CTE int_data"}, {"QUERY PLAN":" -> WindowAgg (cost=18251137.15..60483653.11 rows=260226685 width=86) (actual time=18872.974..53932.723 rows=12098156 loops=1)"}, {"QUERY PLAN":" -> Nested Loop Left Join (cost=18251137.15..48122885.57 rows=260226685 width=86) (actual time=18872.940..42113.431 rows=12098156 loops=1)"}, {"QUERY PLAN":" -> Nested Loop Left Join (cost=18251136.72..28987110.30 rows=40178871 width=83) (actual time=18872.936..30093.257 rows=4894662 loops=1)"}, {"QUERY PLAN":" -> Merge Left Join (cost=18251136.57..18464847.58 rows=40178871 width=83) (actual time=18872.907..24527.093 rows=4894662 loops=1)"}, {"QUERY PLAN":" Merge Cond: (stock_move.procurement_order_id = procurement_order_info.procurement_order_id)"}, {"QUERY PLAN":" -> Sort (cost=18047912.23..18148359.41 rows=40178871 width=79) (actual time=18475.942..21478.658 rows=4894662 loops=1)"}, {"QUERY PLAN":" Sort Key: stock_move.procurement_order_id"}, {"QUERY PLAN":" Sort Method: external merge Disk: 453376kB"}, {"QUERY PLAN":" -> Hash Left Join (cost=9894178.56..11188040.14 rows=40178871 width=79) (actual time=6805.134..12584.888 rows=4894662 loops=1)"}, {"QUERY PLAN":" Hash Cond: ((stock_move.apply_batch_no)::text = (procurement_order_transfer_details.name)::text)"}, {"QUERY PLAN":" -> Hash Right Join (cost=6544043.36..7320267.14 rows=40178871 width=37) (actual time=4724.766..8239.559 rows=4894662 loops=1)"}, {"QUERY PLAN":" Hash Cond: (stock_picking_internal_box_line.move_id = stock_move.id)"}, {"QUERY PLAN":" -> Seq Scan on stock_picking_internal_box_line (cost=0.00..141411.97 rows=4760297 width=11) (actual time=0.005..997.770 rows=4789713 loops=1)"}, {"QUERY PLAN":" -> Hash (cost=5727909.47..5727909.47 rows=40178871 width=34) (actual time=4724.263..4724.263 rows=871053 loops=1)"}, {"QUERY PLAN":" Buckets: 262144 Batches: 32 Memory Usage: 2467kB"}, {"QUERY PLAN":" -> Nested Loop (cost=15.19..5727909.47 rows=40178871 width=34) (actual time=0.192..4409.126 rows=871053 loops=1)"}, {"QUERY PLAN":" -> Hash Left Join (cost=14.62..2056160.50 rows=268725 width=25) (actual time=0.170..2110.845 rows=381537 loops=1)"}, {"QUERY PLAN":" Hash Cond: (stock_picking.location_dest_id = stock_warehouse.lot_stock_id)"}, {"QUERY PLAN":" -> Nested Loop (cost=0.56..2054554.08 rows=268725 width=25) (actual time=0.040..2001.666 rows=381537 loops=1)"}, {"QUERY PLAN":" -> Seq Scan on stock_picking_internal (cost=0.00..32214.70 rows=268725 width=8) (actual time=0.017..309.975 rows=381545 loops=1)"}, {"QUERY PLAN":" Filter: ((lcl_state = ANY ('{1,2,3,4}'::integer[])) AND ((state)::text = ANY ('{draft,2bprint,2bmake,pushed,2bdelivery,delivery,received,cancel,packing_canceling,2bweigh_canceling,2bconfirm_canceling,2bmake_canceling,pushed_canceling,2bdelivery_canceling,exception_state}'::text[])))"}, {"QUERY PLAN":" Rows Removed by Filter: 726"}, {"QUERY PLAN":" -> Index Scan using stock_picking_pkey on stock_picking (cost=0.56..7.52 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=381545)"}, {"QUERY PLAN":" Index Cond: (id = stock_picking_internal.picking_id)"}, {"QUERY PLAN":" -> Hash (cost=9.58..9.58 rows=358 width=8) (actual time=0.122..0.122 rows=369 loops=1)"}, {"QUERY PLAN":" Buckets: 1024 Batches: 1 Memory Usage: 15kB"}, {"QUERY PLAN":" -> Seq Scan on stock_warehouse (cost=0.00..9.58 rows=358 width=8) (actual time=0.005..0.079 rows=369 loops=1)"}, {"QUERY PLAN":" -> Index Scan using stock_move_picking_id_index on stock_move (cost=0.57..9.39 rows=427 width=21) (actual time=0.004..0.005 rows=2 loops=381537)"}, {"QUERY PLAN":" Index Cond: (picking_id = stock_picking.id)"}, {"QUERY PLAN":" -> Hash (cost=3344154.20..3344154.20 rows=478480 width=67) (actual time=2080.190..2080.190 rows=479886 loops=1)"}, {"QUERY PLAN":" Buckets: 65536 Batches: 1 Memory Usage: 52569kB"}, {"QUERY PLAN":" -> Nested Loop Left Join (cost=0.56..3344154.20 rows=478480 width=67) (actual time=0.020..1861.855 rows=479886 loops=1)"}, {"QUERY PLAN":" -> Seq Scan on procurement_order_transfer_details (cost=0.00..22242.80 rows=478480 width=52) (actual time=0.005..68.956 rows=479886 loops=1)"}, {"QUERY PLAN":" -> Index Scan using procurement_order_pkey on procurement_order (cost=0.56..6.93 rows=1 width=23) (actual time=0.003..0.003 rows=1 loops=479886)"}, {"QUERY PLAN":" Index Cond: (id = procurement_order_transfer_details.procurement_order_id)"}, {"QUERY PLAN":" -> Sort (cost=203224.34..206344.70 rows=1248143 width=8) (actual time=396.956..758.316 rows=4925763 loops=1)"}, {"QUERY PLAN":" Sort Key: procurement_order_info.procurement_order_id"}, {"QUERY PLAN":" Sort Method: quicksort Memory: 61125kB"}, {"QUERY PLAN":" -> Seq Scan on procurement_order_info (cost=0.00..76841.43 rows=1248143 width=8) (actual time=0.010..173.562 rows=779699 loops=1)"}, {"QUERY PLAN":" -> Index Scan using stock_warehouse_configuration_pk on stock_warehouse_configuration (cost=0.14..0.25 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=4894662)"}, {"QUERY PLAN":" Index Cond: (stock_warehouse.id = id)"}, {"QUERY PLAN":" -> Index Scan using starmerx_inventory_product_uniq on starmerx_inventory si (cost=0.43..0.47 rows=1 width=19) (actual time=0.001..0.002 rows=2 loops=4894662)"}, {"QUERY PLAN":" Index Cond: ((procurement_order.product_id = product_id) AND (procurement_order.location_id = location_id))"}, {"QUERY PLAN":" CTE group_data"}, {"QUERY PLAN":" -> GroupAggregate (cost=63814139.09..78286606.76 rows=8000000 width=172) (actual time=78558.871..78559.078 rows=50 loops=1)"}, {"QUERY PLAN":" Group Key: int_data.product_id, int_data.seller_id, int_data.warehouse_location"}, {"QUERY PLAN":" -> Sort (cost=63814139.09..64464705.80 rows=260226685 width=172) (actual time=78558.827..78558.890 rows=201 loops=1)"}, {"QUERY PLAN":" Sort Key: int_data.product_id, int_data.seller_id, int_data.warehouse_location"}, {"QUERY PLAN":" Sort Method: external merge Disk: 880640kB"}, {"QUERY PLAN":" -> CTE Scan on int_data (cost=0.00..5204533.70 rows=260226685 width=172) (actual time=18872.978..61825.864 rows=12098156 loops=1)"}, {"QUERY PLAN":" -> CTE Scan on group_data (cost=0.00..160000.00 rows=8000000 width=148) (actual time=78558.874..78559.105 rows=50 loops=1)"}, {"QUERY PLAN":"Planning time: 4.209 ms"}, {"QUERY PLAN":"Execution time: 79047.515 ms"} ]

  18. Assistant

    这段输出是使用 EXPLAIN ANALYZE 收集的 PostgreSQL 查询执行计划,展示了查询的各个阶段的执行情况。逐步解析这些信息可以帮助我们理解查询的性能瓶颈和优化方向。

    总体概述

    • Planning time: 4.209 ms — 查询规划时间,较短。
    • Execution time: 79047.515 ms (约79秒) — 查询实际执行的总时间,较长。

    详解分析各部分

    Limit 和 CTE int_data

    Limit  (cost=138770260.27..138770260.87 rows=30 width=148) (actual time=78558.978..78559.107 rows=30 loops=1)
      CTE int_data
    
    • Limit 操作限制结果集为 30 行。
      • cost 显示估计成本。
      • actual time 显示执行的实际时间。
    • CTE int_data 是公用表表达式的名称,下文将详细说明其内容。

    WindowAgg

    WindowAgg  (cost=18251137.15..60483653.11 rows=260226685 width=86) (actual time=18872.974..53932.723 rows=12098156 loops=1)
    
    • WindowAgg 显示窗口聚合运算。
      • cost 显示估算成本。
      • 实际操作处理了约 1200 万行数据,时间较长。

    Nested Loop Left Join (第一级)

    Nested Loop Left Join  (cost=18251137.15..48122885.57 rows=260226685 width=86) (actual time=18872.940..42113.431 rows=12098156 loops=1)
    
    • 嵌套循环的左连接,第一级。
    • cost 和 actual time 显示的估算成本和实际时间。

    Nested Loop Left Join (第二级)

    Nested Loop Left Join  (cost=18251136.72..28987110.30 rows=40178871 width=83) (actual time=18872.936..30093.257 rows=4894662 loops=1)
    
    • 嵌套循环的左连接,第二级。
    • 处理的行数较大,时间较长。

    Merge Left Join

    Merge Left Join  (cost=18251136.57..18464847.58 rows=40178871 width=83) (actual time=18872.907..24527.093 rows=4894662 loops=1)
    
    • 合并左连接。
    • 处理的行数依旧较大。

    Sort 和 Hash Join

    Sort  (cost=18047912.23..18148359.41 rows=40178871 width=79) (actual time=18475.942..21478.658 rows=4894662 loops=1)
      Sort Key: stock_move.procurement_order_id
      Sort Method: external merge  Disk: 453376kB
      Hash Left Join  (cost=9894178.56..11188040.14 rows=40178871 width=79) (actual time=6805.134..12584.888 rows=4894662 loops=1)
        Hash Cond: ((stock_move.apply_batch_no)::text = (procurement_order_transfer_details.name)::text)
    
    • 显示排序操作:排在外部磁盘上。
    • Hash Left Join 表示哈希左连接,显示了哈希条件。

    Hash Right Join 和 Hash 操作

    Hash Right Join  (cost=6544043.36..7320267.14 rows=40178871 width=37) (actual time=4724.766..8239.559 rows=4894662 loops=1)
      Hash Cond: (stock_picking_internal_box_line.move_id = stock_move.id)
      Seq Scan on stock_picking_internal_box_line  (cost=0.00..141411.97 rows=4760297 width=11) (actual time=0.005..997.770 rows=4789713 loops=1)
      Hash  (cost=5727909.47..5727909.47 rows=40178871 width=34) (actual time=4724.263..4724.263 rows=871053 loops=1)
    
    • 连续的哈希右连接、序列扫描和哈希操作。这些操作处理了大量行,时间较长。

    Nested Loop 和 Seq Scan

    Nested Loop  (cost=15.19..5727909.47 rows=40178871 width=34) (actual time=0.192..4409.126 rows=871053 loops=1)
      Hash Left Join  (cost=14.62..2056160.50 rows=268725 width=25) (actual time=0.170..2110.845 rows=381537 loops=1)
        Hash Cond: (stock_picking.location_dest_id = stock_warehouse.lot_stock_id)
        Nested Loop  (cost=0.56..2054554.08 rows=268725 width=25) (actual time=0.040..2001.666 rows=381537 loops=1)
          Seq Scan on stock_picking_internal  (cost=0.00..32214.70 rows=268725 width=8) (actual time=0.017..309.975 rows=381545 loops=1)
            Filter: ((lcl_state = ANY ('{1,2,3,4}'::integer[])) AND ((state)::text = ANY ('{draft,2bprint,2bmake,pushed,2bdelivery,delivery,received,cancel,packing_canceling,2bweigh_canceling,2bconfirm_canceling,2bmake_canceling,pushed_canceling,2bdelivery_canceling,exception_state}'::text[])))
      Index Scan using stock_move_picking_id_index on stock_move  (cost=0.57..9.39 rows=427 width=21) (actual time=0.004..0.005 rows=2 loops=381537)
        Index Cond: (picking_id = stock_picking.id)
    
    • 显示嵌套循环和序列扫描操作。Seq Scan 指序列扫描整个表。

    Index Scan 和 GroupAggregate

    Index Scan using starmerx_inventory_product_uniq on starmerx_inventory si  (cost=0.43..0.47 rows=1 width=19) (actual time=0.001..0.002 rows=2 loops=4894662)
      Index Cond: ((procurement_order.product_id = product_id) AND (procurement_order.location_id = location_id))
     
    GroupAggregate  (cost=63814139.09..78286606.76 rows=8000000 width=172) (actual time=78558.871..78559.078 rows=50 loops=1)
      Group Key: int_data.product_id, int_data.seller_id, int_data.warehouse_location
      Sort  (cost=63814139.09..64464705.80 rows=260226685 width=172) (actual time=78558.827..78558.890 rows=201 loops=1)
        Sort Key: int_data.product_id, int_data.seller_id, int_data.warehouse_location
        Sort Method: external merge  Disk: 880640kB
    
    • Index Scan 显示使用索引扫描,对应条件给出。
    • GroupAggregate 显示分组聚合操作,处理大量行数。
    • Sort 显示排序操作,是在外部磁盘上执行的,磁盘 I/O 量大。

    优化建议

    根据以上分析,以下是可能的优化措施:

    1. 索引优化:

      • 确保所有连接条件和过滤条件上都有适当的索引。例如,以下列:
        • stock_move.procurement_order_id
        • stock_move.id
        • stock_picking_internal_box_line.move_id
        • procurement_order_transfer_details.name
        • stock_picking.location_dest_id
        • stock_warehouse.lot_stock_id
        • procurement_order.product_id
        • procurement_order.location_id

      创建索引示例:

      CREATE INDEX idx_stock_move_procurement_order_id ON stock_move(procurement_order_id);
      CREATE INDEX idx_stock_move_id ON stock_move(id);
      CREATE INDEX idx_s_p_ib_move_id ON stock_picking_internal_box_line(move_id);
      CREATE INDEX idx_potd_name ON procurement_order_transfer_details(name);
      CREATE INDEX idx_s_p_loc_dest_id ON stock_picking(location_dest_id);
      CREATE INDEX idx_s_w_lot_stock_id ON stock_warehouse(lot_stock_id);
      CREATE INDEX idx_po_product_id_location_id ON procurement_order(product_id, location_id);
      
    2. 禁用嵌套循环连接:

      • 禁用嵌套循环连接,迫使查询优化器使用其他连接方法(例如哈希连接或合并连接)。
      SET enable_nestloop = OFF;
      
      你可在执行完查询后再将其启用:
      SET enable_nestloop = ON;
      
    3. 使用临时表或物料化视图:

      • 将复杂的计算和中间结果物化为临时表或物料化视图,减少重复计算。

      示例:

      CREATE MATERIALIZED VIEW mat_view_int_data AS
      WITH int_data AS (
      -- 你的查询
      )
      ;
      
      -- 查询主表
      SELECT ... FROM mat_view_int_data;
      
    4. 减少排序操作的I/O:

      • 尽量减少排序操作导致的外部磁盘I/O。可以通过增加排序缓冲区大小来实现(需要数据库管理员权限)。

      PostgreSQL 增加排序缓冲区大小示例:

      SET work_mem = '256MB';  -- 调整内存大小
      
    5. 优化 CTE 和聚合操作:

      • 通过重写查询或进一步分解复杂 CTE 来优化其性能。

    通过应用这些优化措施,可以期望显著提升查询执行的性能。继续监测并通过不同优化策略的结合,找到最适合你的具体查询工作负载的方法。