Question

postgresql 大神帮优化个sql

explain analyze select * from dtb_products t where product_id = (select product_id from dtb_products where group_id = t.group_id and status = 1 and del_flg = 0 order by product_id limit 1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on dtb_products t (cost=0.00..97872.27 rows=42 width=2009) (actual time=0.040..156.460 rows=3692 loops=1)
Filter: (product_id = (SubPlan 1))
SubPlan 1
-> Limit (cost=11.39..11.40 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=8399)
-> Sort (cost=11.39..11.40 rows=2 width=4) (actual time=0.016..0.016 rows=1 loops=8399)
Sort Key: dtb_products.product_id
Sort Method: quicksort Memory: 25kB
-> Index Scan using group_id on dtb_products (cost=0.00..11.38 rows=2 width=4) (actual time=0.004..0.011 rows=5 loops=8399)
Index Cond: (group_id = $0)
Filter: ((status = 1) AND (del_flg = 0))
Total runtime: 157.881 ms

例如：
id group_id
1 1
2 1
3 2
4 3
5 1
这样的数据 1,2,5 是一组的
这样检索出的应该是1,3,4

按照group_id 分组，每一组只取一个id

Answer 1

select id
from (select id,group_id ,row_number() over(partition by group_id order by id) as n
from t ) a
where n=1

Answer 2

select min(id) id,product_id from dtb_products where status = 1 and del_flg = 0
group by product_id
order by product_id