CODEWITHSUNDEEP
sqlpostgresqlselectquery-optimization
miloxe
miloxe

Reputation: 463

How to select MIN and MAX from another table

I have two tables in postgresql: ITEM (ID, TYPE) and ITEM_DATA (TS, ITEM_ID) in 1:n relation. The first table is small and the second is huge. I would like to know the best way to select min and max from the table ITEM_DATA for each row of a select from the table ITEM. At this moment I have this select, but it's too slow:

SELECT 
  I.*,
  (SELECT MIN(ID.TS) FROM ITEM_DATA ID WHERE ID.ITEM_ID=I.ID) AS MIN_TS,
  (SELECT MAX(ID.TS) FROM ITEM_DATA ID WHERE ID.ITEM_ID=I.ID) AS MAX_TS
FROM ITEM I
WHERE I.TYPE = 'X';

There is a primary key in the table ITEM_DATA which has two segments: TS and ITEM_ID and there is also an index on the table ITEM which has one segment: TYPE.

Is there a way how to make this query faster?

Upvotes: 0

Views: 3064

Answers (2)

joop
joop

Reputation: 4503

SELECT i.*, mima.mi, mima.ma
FROM item i
JOIN (
        SELECT DISTINCT item_id
        , MIN(ts) as mi
        , MAX(ts) as ma
        FROM item_data
        GROUP BY item_id
        ) mima ON mima.item_id = i.id
WHERE i.type = 'X'
        ;

And, yes: your query will benefit from an index

    CREATE INDEX ON item_data(item_id,ts);

Upvotes: 0

RayCW
RayCW

Reputation: 184

I think you should reverse the order of the columns of the PK on ITEM_DATA.

Upvotes: 1

Related Questions