How about
   SELECT p_id, f_id
      person as p
         LEFT JOIN
   (SELECT f.p_id, max(, 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:

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( from Food f, Person p where
(f.p_id = group by;
                                                 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, (Select from food f where order by desc limit 1) from person p;
                                              QUERY PLAN
Seq Scan on Person p  (cost=100000000.00..100007015.24 rows=569 width=8)
   ->  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( from person p, food f where 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,(select from food f where order by desc limit 
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

---------------------------(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

Reply via email to