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.