有没有搞过sql优化的看看 pgsql 查询优化

Question

问题遇到的现象和发生背景

看看这个sql语句还能不能优化

问题相关代码，请勿粘贴截图

SELECT
    com.name                AS company, 
    area.name               AS area_name, 
    br.name                 AS branch,
    hps.id                  AS swinery_id,
    svc.svc_org_id          AS svg_id,
    so.date                 AS date,
    fm.name                 AS farmer, 
    cus.name                AS customer,
    CASE 
        WHEN so.sale_type = 'inner_allocate' THEN '内部调拨'
        WHEN so.sale_type = 'inner_sale' THEN '内部销售'
        WHEN so.sale_type = 'outer' THEN '外部销售'
    END                                   AS saletype,

    CASE 
        WHEN sol.level = 'lev_one' THEN '一级品'
        WHEN sol.level = 'lev_two' THEN '二级品'
        WHEN sol.level = 'lev_three' THEN '三级品'
    END                                   AS level,
    
    sol.qty_delivery      AS qty_sold,
    sol.weight_delivery   AS weight_sold,
    CASE 
        WHEN sol.qty_delivery > 0 THEN round(sol.weight_delivery :: NUMERIC / sol.qty_delivery :: NUMERIC, 2)
        ELSE 0
    END                      AS weight_avg,
    
    sol.price                AS price_avg,
    sol.line_amount          AS price,
    ff.name  as  ff_name,
    hme.name as sale_man,
    hps.number as swinery,
    emp.name as technician
    FROM hd_mrk_sale_order so
    INNER JOIN hd_mrk_sale_order_line sol ON sol.order_id = so.id
    LEFT JOIN hd_m_customer cus ON cus.id = so.customer_id
    left JOIN res_company com ON com.id = so.company_id
    INNER JOIN hd_m_svc_org svc on svc.org_unit_id = so.company_id             
    INNER JOIN hd_m_area area on area.id = svc.area_id          
    INNER JOIN hd_mrk_recycle_order rec on rec.id  = so.recycle_order_id
    INNER JOIN hd_m_farmer fm on fm.id  = rec.farmer_id
    INNER JOIN hd_m_branch br on br.id = svc.branch_id
    left join hd_pro_swinery as hps on hps.id = rec.swinery_id
    left join hd_m_employee emp on emp.id = hps.technician_id
    left join hd_far_farm ff on ff.id = hps.farm_id
    left join hd_m_employee hme on rec.salesman_id = hme.id
    WHERE so.state IN ('audit', 'closed')

Answer 1

不知道你这个语句查询需要多长时间，表的大小，只能给你提几个意见：

1. 如果表的数据量大。可以考虑用子查询只查需要的字段，然后在关联。
2. 查询尽量走索引
3. 你关联的表有点多，可以试试嵌套。