CODEWITHSUNDEEP
sqlpostgresqlquery-optimization
Carlos Gonzalez
Carlos Gonzalez

Reputation: 151

Optimizing a query in PostgreSQL

CREATE TABLE master.estado_movimiento_inventario
(
  id integer NOT NULL,
  eliminado boolean NOT NULL DEFAULT false,
  fundamentacion text NOT NULL,
  fecha timestamp without time zone NOT NULL,
  id_empresa integer NOT NULL,
  id_usuario integer NOT NULL,
  id_estado integer NOT NULL,
  id_movimiento integer NOT NULL,
  se_debio_tramitar_hace bigint DEFAULT 0,
  CONSTRAINT "PK15estadomovtec" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE master.estado_movimiento_inventario
  OWNER TO postgres;

This table tracks the state of every inventory movement in my business logic. So, every movement that is not ended yet (there is not any id_estado =3 or id_estado=4 for any id_movimiento in the master.estado_movimiento_inventario table) must store in its last state's se_debio_tramitar_hace field the difference between now() and fecha field every time a scheduled task runs (Windows).

The query I built in order to do so is this:

with update_time as(
   SELECT  distinct on(id_movimiento) id 
   from master.estado_movimiento_inventario 
   where id_movimiento not in (
      select id_movimiento 
      from master.estado_movimiento_inventario 
      where id_estado = 2 or id_estado=3
   ) order by id_movimiento, id desc
)
update master.estado_movimiento_inventario mi 
  set se_debio_tramitar_hace= EXTRACT(epoch FROM now()::timestamp - mi.fecha )/3600
  where mi.id in (select id from update_time);

This works as expected but I suspect it is not optimal, specially in the update operation and here is my biggest doubt: what is optimal when performing this update operation:

Sorry if I am not explicit enough, I have not much experience working with databases, although I have understood the theory behind, yet not worked too much with it.

Edit

Please notice the id_estado is not unique per id_movimiento, just like the picture shows:

enter image description here

Upvotes: 0

Views: 88

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think this improves the CTE:

with update_time as (
      select id_movimiento, max(id) as max_id
      from master.estado_movimiento_inventario 
      group by id_movimiento
      having sum( (id_estado in (2, 3))::int ) = 0
     )
update master.estado_movimiento_inventario mi 
  set diferencia = now() - mi.fecha 
  where mi.id in (select id from update_time);

If the last id would be the one in the "2" or "3" state, I would simply do:

update master.estado_movimiento_inventario mi 
    set diferencia = now() - mi.fecha 
    where mi.id = (select max(mi2.id)
                   from master.estado_movimiento_inventario mi 
                   where mi2.id_movimiento = mi.id_movimiento
                  ) and
          mi.id_estado not in (2, 3);

Upvotes: 1

Related Questions