Optimising PostgreSQL Query that seems slow

Question

I have a query that's doing quite a bit of heavy lifting on quite a large database. The way I have done it feels like it's jsut not optimal. I was wondering if anyone has any ideas that could point me in the right direction for optimising this query. Just to be clear I;m not looking for a re write jsut to get an idea of what approach to take here.

Just to clarify what I'm doing here. I want to get the sibling row of the selected rows details and merge it into one row.

SELECT rd.row_num,
    rd.id,
    rd.date,
    rd.hashed,
    rd.af,
    rd.le,
    rd.ti,
    rd.co,
    rd.row_num + 1 AS partner_row_number,
    (SELECT date FROM rowedDESC
     WHERE hashed = rd.hashed AND row_num = rd.row_num + 1) AS partner_date,
    (SELECT id FROM rowedDESC
     WHERE hashed = rd.hashed AND row_num = rd.row_num + 1) AS partner_id,
    (SELECT af FROM rowedDESC
     WHERE hashed = rd.hashed AND row_num = rd.row_num + 1) AS partner_af,
    (SELECT len FROM rowedDESC
     WHERE hashed = rd.hashed AND row_num = rd.row_num + 1) AS partner_le,
    (SELECT ti FROM rowedDESC
     WHERE hashed = rd.hashed AND row_num = rd.row_num + 1) AS partner_ti,
    (SELECT co FROM rowedDESC
     WHERE hashed = rd.hashed AND row_num = rd.row_num + 1) AS partner_co
    FROM rowedDESC rd
    WHERE rd.id IN (SELECT id FROM b)

Why so many subqueries to get data from the same table? When you need "the next" row, use a WINDOW function — Frank Heikens
– Frank Heikens, Commented Jun 8, 2022 at 11:43
@FrankHeikens I used lead before without success. I actually may have had a different version of my query then. I will try it out again — Wakka
– Wakka, Commented Jun 8, 2022 at 11:53
Without the result from EXPLAIN(ANALYZE, VERBOSE, BUFFERS) it's also hard to help you. Another thing you can do, but probably won't change much in performance, is rewriting the WHERE condition to a JOIN. Both id's should be indexed, not sure if that matters, don't know how many records match the conditions. — Frank Heikens
– Frank Heikens, Commented Jun 8, 2022 at 12:04
@FrankHeikens Yeah I;m testing a join and another lag now. Theres quite a bit more to the overall query. It looks like LEAD has worked this time though. Last time I was at a different point when using it. Been one of them I've been wokring cross over on a few bits and I've got myself tangled up not using my brain right. — Wakka
– Wakka, Commented Jun 8, 2022 at 12:07

Alexey · Accepted Answer · 2022-06-08 12:09:02Z

1

You can use CTE like this:

WITH rd(row_num, id, date, hashed, af, le, ti, co, partner_row_number) AS 
(
    SELECT 
        row_num,
        id,
        date,
        hashed,
        af,
        le,
        ti,
        co,
        row_num + 1 AS partner_row_number
    FROM rowedDESC
    WHERE id IN (SELECT id FROM b)
)
SELECT 
    rd.*,
    r.date as partner_date,
    r.id as partner_id,
    r.af as partner_af,
    r.len as partner_le,
    r.ti as partner_ti,
    r.co as partner_co,
FROM
    rd, rowedDESC r
WHERE r.hashed = rd.hashed AND r.row_num = rd.row_num + 1

or with JOIN, if partner existense is not guaranteed, like this:

WITH rd(row_num, id, date, hashed, af, le, ti, co, partner_row_number) AS 
(
    SELECT 
        row_num,
        id,
        date,
        hashed,
        af,
        le,
        ti,
        co,
        row_num + 1 AS partner_row_number
    FROM rowedDESC
    WHERE id IN (SELECT id FROM b)
)
SELECT 
    rd.*,
    r.date as partner_date,
    r.id as partner_id,
    r.af as partner_af,
    r.len as partner_le,
    r.ti as partner_ti,
    r.co as partner_co,
FROM
    rd
LEFT JOIN rowedDESC r 
ON r.hashed = rd.hashed AND r.row_num = rd.row_num + 1

Also it would be usefull to have an index on hashed + row_num

answered Jun 8, 2022 at 12:09

Alexey

2,4791 gold badge13 silver badges15 bronze badges

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

1 Comment

Wakka Over a year ago

Thanks Alexey, I ended up going with using a widnow function But I like the join method.

Collectives™ on Stack Overflow

Optimising PostgreSQL Query that seems slow

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related