[SOLVED] Bad query performance postgres 16

Bad query performance postgres 16

We are trying to make the step from postgres 10 to 16, we already tried a while ago to lower versions 12 or 13, but updates were added in the machine and the poor performance we attribute to those updates. Now we are testing on a new machine, and we have 2 virtual machines with the same resources and same configuration in postgres.conf. The query in question is:

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;

On the machine with postgres 10, the response time is approximately 750 ms. On the machine with postgres 16, the response time goes to 4 minutes.

Reviewed documentation, it seems that there are changes from version 12. We have made changes to the query until we found a more optimal solution for v16:

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;

We have seen

that order by and limit can lead to bad strategies, they suggest applying + 0, in our case increasing the date, but we don't see improvements either.
that the pg_hint_plan extension can be added, but the tests have not been satisfactory, possibly due to lack of knowledge on our part.

If the change were for a single query there would be no problem, but it is a large application, where we do not know how many queries we may encounter with such a different and bad performance, or if it is the same pattern.

Initially it seems that it is some scheduler configuration, which would allow good results to be obtained without changing the SQL queries.

Solution

Could you try a re-index for all indexes after upgrade. I think this might be the reason

More efficiently store duplicates in B-tree indexes (Anastasia Lubennikova, Peter Geoghegan). This allows efficient B-tree indexing of low-cardinality columns by storing duplicate keys only once. Users upgrading with pg_upgrade will need to use REINDEX to make an existing index use this feature

source - https://www.postgresql.org/docs/13/release-13.html#id-1.11.6.20.5

Anyway you need to do a through check before upgrade to ensure the system works well after upgrade, there are some behavior changes as well

Please go through release notes of each major release to find out the changes