PostgreSQL — Select column1 where MIN(column2)

Question

NOTE: I WANT TO AVOID DISTINCT ON FOR PERFORMANCE REASONS.

NOTE 2: I WAS WRONG. USING PROPER INDEXES THE QUERY WORKED AWESOME THANKS TO @Gordon Linoff!

Having the following structure:

| id | image_url     | sort | t1_id |
|----|---------------|------|-------|
| 1  | https://.../1 | 10   | 1     |
| 2  | https://.../2 | 20   | 1     |
| 3  | https://.../3 | 30   | 1     |
| 4  | https://.../4 | 30   | 2     |
| 5  | https://.../5 | 20   | 2     |
| 6  | https://.../6 | 10   | 2     |

I want to fetch the lowest sort row's image_url column by t1_id, similar to the following:

SELECT * FROM t2 WHERE MIN(sort) GROUP BY (t1_id);

Getting the following result:

| id | image_url     | sort | t1_id |
|----|---------------|------|-------|
| 1  | https://.../1 | 10   | 1     |
| 6  | https://.../6 | 10   | 2     |

Thanks in advance!

I am pretty sure that DISTINCT ON is not slower than any other technique to achieve the same (e.g. with ROW_NUMBER or a sbquery). So, your problem is more general and you may just want to provide your DBMS with an appropriate index. — Thorsten Kettner
– Thorsten Kettner, Commented Mar 8, 2021 at 12:55

Gordon Linoff · Accepted Answer · 2021-03-08 12:55:40Z

2

Postgres has a handy extension called distinct on:

select distinct on (t1_id) t2.*
from t2
order by t1_id, sort asc;

This is usually the fastest way to approach such a problem. In particular, this can take advantage of an index on (t1_id, sort [desc]).

However, you can try another approach such as:

select t2.*
from t2
where t2.sort = (select min(tt2.sort)
                 from t2 tt2
                 where tt2.t1_id = t2.t1_id
                );

This would use the same index. If this is faster, please post a comment with the relevant performance.

edited Mar 8, 2021 at 12:55

answered Mar 8, 2021 at 12:53

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

Zeswen Over a year ago

As I noted on the first line, the ms from my query goes from 200ms to 700ms with distinct on.

Gordon Linoff Over a year ago

@Zeswen . . . The query has to do more work, so it is not surprising that it takes more time. distinct on is often the fastest method -- particularly with the right index.

Zeswen Over a year ago

I did not notice the index you mentioned with (t1_id, sort [asc]). It really did the trick with performance, thank you very much! Learned something new with the asc in the index :)

Collectives™ on Stack Overflow

PostgreSQL — Select column1 where MIN(column2)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related