CODEWITHSUNDEEP
sqlpostgresql
orange
orange

Reputation: 8090

Eliminate subquery to improve query performance

I'd like to rewrite the following subquery as it's used over and over again in a larger query. The DBMS used is Postgres and the table has the following structure table (id uuid, seq int, value int).

Given a value for id (id_value), the query finds all records in "table" where seq < seq of id_value

My naive (slow) solution so far is the following:

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

e.g. a query

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

returns

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

For testing purposes, I've tried to hardcode the relevant seq field and it improves the whole query significantly, but I really don't like to query for seq as a two-stage process. Ideally this could happen as part of the query. Any ideas or inspiration would be appreciated.


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;

enter image description here

Upvotes: 1

Views: 134

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You may have so many redundant indexes that you are confusing Postgres. Simply defining a column as primary key or unique is sufficient. You don't need multiple index declarations.

For what you want to do, this should be optimal:

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

This should use the index to fetch the seq value in the subquery. Then it should return the appropriate rows.

You could also try:

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

However, my suspicion is simply that the query is returning a large number of rows and that is affecting performance.

Upvotes: 1

Related Questions