How about
SELECT p_id, f_id
FROM
person as p
LEFT JOIN
(SELECT f.p_id, max(f.id), f_item
FROM food) as f
ON p.p_id = f.p_id
Create an index on Food (p_id, seq #)
This may not gain any performance, but worth a try. I don't have any
data similar to this to test it on. Let us know.
I assume that the food id is a sequential number across all people.
Have you thought of a date field and a number representing what meal was
last eaten, i.e. 1= breakfast, 2 = mid morning snack etc. Or a date
field and the food id code?
Junaili Lie wrote:
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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
