Hi, The suggested query below took forever when I tried it. In addition, as suggested by Tobias, I also tried to create index on food(p_id, id), but still no goal (same query plan). Here is the explain: TEST1=# explain select f.p_id, max(f.id) from Food f, Person p where (f.p_id = p.id) group by p.id; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=0.00..214585.51 rows=569 width=16) -> Merge Join (cost=0.00..200163.50 rows=2884117 width=16) Merge Cond: ("outer".id = "inner".p_id) -> Index Scan using person_pkey on person p (cost=0.00..25.17 rows=569 width=8) -> Index Scan using person_id_food_index on food f (cost=0.00..164085.54 rows=2884117 width=16) (5 rows)
TEST1=# explain select p.id, (Select f.id from food f where f.p_id=p.id order by f.id desc limit 1) from person p; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Seq Scan on Person p (cost=100000000.00..100007015.24 rows=569 width=8) SubPlan -> Limit (cost=0.00..12.31 rows=1 width=8) -> Index Scan Backward using food_pkey on food f (cost=0.00..111261.90 rows=9042 width=8) Filter: (p_id = $0) (5 rows) any ideas or suggestions is appreciate. On 6/8/05, Tobias Brox <[EMAIL PROTECTED]> wrote: > [Junaili Lie - Wed at 12:34:32PM -0700] > > select f.p_id, max(f.id) from person p, food f where p.id=f.p_id group > > by f.p_id will work. > > But I understand this is not the most efficient way. Is there another > > way to rewrite this query? (maybe one that involves order by desc > > limit 1) > > eventually, try something like > > select p.id,(select f.id from food f where f.p_id=p.id order by f.id desc > limit 1) > from person p > > not tested, no warranties. > > Since subqueries can be inefficient, use "explain analyze" to see which one > is actually better. > > This issue will be solved in future versions of postgresql. > > -- > Tobias Brox, +47-91700050 > Tallinn > ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match