时间戳

首页 > 解决方案 > 消除子查询以提高查询性能

sql - 消除子查询以提高查询性能

问题描述

我想重写以下子查询，因为它在更大的查询中一遍又一遍地使用。使用的 DBMS 是 Postgres，表的结构如下table (id uuid, seq int, value int)。

给定 id ( id_value) 的值，查询在“表”中查找所有记录，其中 seq < seq of id_value

到目前为止，我的幼稚（缓慢）解决方案如下：

select * from table
where seq < (select seq from table where id = id_value)

table
id, seq,  value
a,   1,     12
b,   2,     22
c,   3,     32
x,   4,     43
d,   5,     54
s,   6,     32
a,   7,     54

例如查询

select * from table where seq < (select seq from table where id = 'x')

返回

a,   1,     12
b,   2,     22
c,   3,     32

出于测试目的，我尝试对相关seq字段进行硬编码，它显着改进了整个查询，但我真的不喜欢将查询seq作为一个两阶段的过程。理想情况下，这可能作为查询的一部分发生。任何想法或灵感将不胜感激。


CREATE TABLE foo
(
    seq integer NOT NULL,
    id uuid NOT NULL,
    CONSTRAINT foo_pkey PRIMARY KEY (id),
    CONSTRAINT foo_id_key UNIQUE (id),
    CONSTRAINT foo_seq_key UNIQUE (seq)
);


CREATE UNIQUE INDEX idx_foo_id
    ON public.foo USING btree
    (id)
    TABLESPACE pg_default;


CREATE UNIQUE INDEX idx_foo_seq
    ON public.foo USING btree
    (seq)
    TABLESPACE pg_default;

在此处输入图像描述

标签： sqlpostgresql

解决方案

您可能有太多冗余索引，以至于您对 Postgres 感到困惑。只需将列定义为primary keyorunique就足够了。您不需要多个索引声明。

对于您想要做的事情，这应该是最佳的：

select f.*
from foo f
where f.seq < (select f2.seq from foo f2 where f2.id = :id_value)

这应该使用索引来获取seq子查询中的值。然后它应该返回适​​当的行。

你也可以试试：

select f.*
from (select f.*, min(seq) filter (where id = :id_value) over () as min_seq
      from foo f
     ) f
where seq < min_seq;

但是，我的怀疑只是查询返回了大量行，这会影响性能。

推荐阅读