Optimizing a query in PostgreSQL

Question

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:

Perform the updates as it currently does or
The equivalent of postgresql function to this:

foreach(update_time.id as row_id){ update master.estado_movimiento_inventario mi set diferencia = now() - mi.fecha where mi.id=row_id; }

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:

Properly formatted SQL is so much easier to read and to understand. — jarlh
– jarlh, Commented Nov 1, 2018 at 13:52
Try to use store procedure in place of complex query. Will be easy to understand and solve your issue. — Wit Wikky
– Wit Wikky, Commented Nov 1, 2018 at 13:53

Gordon Linoff · Accepted Answer · 2018-11-01 14:13:11Z

1

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);

answered Nov 1, 2018 at 14:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hogan Over a year ago

hmmm... haven't seen you be wrong before I wonder what I missed.

Carlos Gonzalez Over a year ago

Loving this approach. Thanks a lot

Collectives™ on Stack Overflow

Optimizing a query in PostgreSQL

Edit

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edit

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest