Message-ID: <42A75985.8090709@gvtc.com>
Date: Wed, 08 Jun 2005 20:48:05 +0000
From: Jim Johannsen <jjsa@gvtc.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
Cc: pgsql-performance@postgresql.org
Subject: Re: Help with rewriting query
References: <8d04ce990506081234121995f9@mail.gmail.com>	
	<20050608195625.GM8451@tobias.nordicbet.com>
	<8d04ce990506081548355991bb@mail.gmail.com>
In-Reply-To: <8d04ce990506081548355991bb@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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 <tobias@nordicbet.com> 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
>
>
>  
>