X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5E7745283C for ; Thu, 9 Jun 2005 00:00:02 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 24409-06 for ; Thu, 9 Jun 2005 02:59:52 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.197.194]) by svr1.postgresql.org (Postfix) with SMTP id 713CF528C1 for ; Wed, 8 Jun 2005 23:59:50 -0300 (ADT) Received: (qmail 7540 invoked by uid 500); 9 Jun 2005 02:59:07 -0000 Date: Wed, 8 Jun 2005 21:59:07 -0500 From: Bruno Wolff III To: Junaili Lie Cc: pgsql-performance@postgresql.org Subject: Re: Help with rewriting query Message-ID: <20050609025907.GB6139@wolff.to> Mail-Followup-To: Bruno Wolff III , Junaili Lie , pgsql-performance@postgresql.org References: <8d04ce990506081234121995f9@mail.gmail.com> <20050608195625.GM8451@tobias.nordicbet.com> <8d04ce990506081548355991bb@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <8d04ce990506081548355991bb@mail.gmail.com> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/194 X-Sequence-Number: 12831 On Wed, Jun 08, 2005 at 15:48:27 -0700, 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; The above is going to require reading all the food table (assuming no orphaned records), so the plan below seems reasonable. > 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; Using a subselect seems to be the best hope of getting better performance. I think you almost got it right, but in order to use the index on (p_id, id) you need to order by f.p_id desc, f.id desc. Postgres won't deduce this index can be used because f.p_id is constant in the subselect, you need to give it some help. > 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 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