PostgreSQL 16查询性能骤降求助：从PG10升级后响应超时

我们正尝试将PostgreSQL从10版本升级至16版本，此前曾测试过12、13版本，但当时因机器更新导致性能表现不佳。目前在配置、资源完全相同的两台虚拟机上开展测试：目标查询在PG10中响应仅需约750ms，在PG16中却耗时长达4分钟。

查阅官方文档后得知，PostgreSQL 12版本后存在相关变更，我们已针对PG16修改了部分查询以适配新版本，但由于应用规模庞大，无法逐一修改所有SQL语句，因此希望找到无需修改SQL的调度或配置优化方案。目前我们已尝试过调整order by+limit的执行策略、使用pg_hint_plan扩展，但均未取得理想效果。

以下是原查询与优化后的查询语句：

原查询 #

select  numero, mov.id, mov.tipo_movimiento,    mov.cliente_origen_id,  mov.cliente_destino_id, mov.motivo_baja,    mov.estado estado_movimiento,   mov.tipo_notificacion,  mov.fecha_movimiento,   (   select      i.id    from        industria i join clientesprecintos c on     c.id_industria = i.id   where       c.id = mov.cliente_origen_id) as industria_origen_id,   (   select  i.id    from        industria i join clientesprecintos c on     c.id_industria = i.id   where       c.id = mov.cliente_destino_id) as industria_destino_id
    from    (values (0,'13704147270')) as precintos (position,  numero)
left join lateral ( select      mp.id,      mp.estado ,     mpt.clave as tipo_movimiento,       mp.cliente_origen_id,       mp.cliente_destino_id,      mpmb.clave as motivo_baja,      tn.clave as tipo_notificacion,      fecha_ini as fecha_movimiento
    from        movimiento_piezas_rangos mpr
    join movimiento_piezas mp on        mp.id = mpr.movimiento_piezas_id
    left join notificaciones n on       n.id_movimiento_piezas = mp.id
    left join tipos_notificaciones tn on        tn.id = n.id_tipo_notificacion
    join movimiento_piezas_tipo mpt on      mpt.id = mp.tipo_movimiento_id
    left join movimiento_piezas_motivo_baja mpmb on     mpmb.id = mp.motivo_baja_id
    where       numero::bigint between mpr.desde and mpr.hasta      and mp.estado not in ('ANULADO', 'RECHAZADO')
    order by        mp.fecha_ini desc   limit 1 ) as mov on true
order by    precintos.position;

优化后查询 #

Select numero,  mov.id,  mov.tipo_movimiento,  mov.cliente_origen_id,  mov.cliente_destino_id,  mov.motivo_baja,  mov.estado estado_movimiento,  mov.tipo_notificacion,  mov.fecha_movimiento,  (select i.id from industria i join clientesprecintos c on c.id_industria = i.id where c.id = mov.cliente_origen_id) as industria_origen_id, (select i.id from industria i join clientesprecintos c on c.id_industria = i.id where c.id = mov.cliente_destino_id) as industria_destino_id 
 from (values (0,'15727625912')) as precintos (position, numero) 
 left join lateral (
    select mp.id, mp.estado , mp.tipo_movimiento, mp.cliente_origen_id, mp.cliente_destino_id, mp.motivo_baja, tn.clave as tipo_notificacion, mp.fecha_movimiento 
        from 
        (WITH mp_consulta AS (
            select mp.id, mp.estado , mpt.clave as tipo_movimiento, mp.cliente_origen_id, mp.cliente_destino_id, mpmb.clave as motivo_baja, fecha_ini as fecha_movimiento 
            from movimiento_piezas_rangos mpr 
        join movimiento_piezas mp on mp.id = mpr.movimiento_piezas_id 
        join movimiento_piezas_tipo mpt on mpt.id = mp.tipo_movimiento_id 
        left join movimiento_piezas_motivo_baja mpmb on mpmb.id = mp.motivo_baja_id 
        where 
            numero::bigint between mpr.desde and mpr.hasta and mp.estado NOT IN ('ANULADO', 'RECHAZADO')) 
            SELECT * FROM ( 
            SELECT * FROM mp_consulta 
            UNION ALL 
        SELECT * FROM mp_consulta ORDER BY fecha_movimiento DESC LIMIT 1 
            ) AS union_mp 
        ORDER BY fecha_movimiento DESC LIMIT 1) mp 
     left join notificaciones n on n.id_movimiento_piezas = mp.id 
     left join tipos_notificaciones tn on tn.id = n.id_tipo_notificacion 
 ) as mov on true 
 order by precintos.position;

内容的提问来源于stack exchange，提问作者Víctor Martín Aguilar