优化 PostgreSQL 中的查询

debugcn 发表于 Dev

卡洛斯·冈萨雷斯

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;

此表跟踪我的业务逻辑中每个库存移动的状态。因此，每个尚未结束的移动（master.estado_movimiento_inventario 表中的任何 id_movimiento 都没有任何 id_estado =3 或 id_estado=4）必须在其最后状态的se_debio_tramitar_hace字段中存储每次计划任务运行时now()与fecha字段之间的差异（视窗）。

我为此构建的查询是这样的：

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

这按预期工作，但我怀疑它不是最佳的，特别是在更新操作中，这是我最大的疑问：执行此更新操作时什么是最佳的：

像当前那样执行更新或
postgresql 函数的等价物：

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

对不起，如果我不够明确，我没有太多使用数据库的经验，虽然我了解背后的理论，但并没有太多使用它。

编辑

请注意，每个 id_movimiento 的 id_estado 并不是唯一的，如图所示：

戈登·利诺夫

我认为这提高了 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);

如果最后一个 id 是处于“2”或“3”状态的那个，我会简单地做：

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。