Optimising PostgreSQL Query that seems slow-CodePudding

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)

CodePudding user response：

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