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 0ECA95290C for ; Thu, 9 Jun 2005 01:02:00 -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 46211-01 for ; Thu, 9 Jun 2005 04:01:57 +0000 (GMT) Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.91]) by svr1.postgresql.org (Postfix) with ESMTP id AB2AA528C7 for ; Thu, 9 Jun 2005 01:01:55 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Wed, 08 Jun 2005 23:01:52 -0500 Message-Id: X-Mailer: Novell GroupWise Internet Agent 6.5.2 Date: Wed, 08 Jun 2005 23:01:35 -0500 From: "Kevin Grittner" To: , Subject: Re: Help with rewriting query Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.115 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/196 X-Sequence-Number: 12833 This is a pattern which I've seen many of times. I call it a "best choice" query -- you can easily match a row from one table against any of a number of rows in another, the trick is to pick the one that matters most. I've generally found that I want the query results to show more than the columns used for making the choice (and there can be many), which rules out the min/max technique. What works in a pretty straitforward way, and generally optimizes at least as well as the alternatives, is to join to the set of candidate rows and add a "not exists" test to eliminate all but the best choice. For your example, I've taken some liberties and added hypothetical columns from both tables to the result set, to demonstrate how that works. Feel free to drop them or substitute actual columns as you see fit. This will work best if there is an index for the food table on p_id and id. Please let me know whether this works for you. select p.id as p_id, p.fullname, f.id, f.foodtype, f.ts from food f join person p on f.p_id = p.id and not exists (select * from food f2 where f2.p_id = f.p_id and f2.id > f.id) order by p_id Note that this construct works for inner or outer joins and works regardless of how complex the logic for picking the best choice is. I think one reason this tends to optimize well is that an EXISTS test can finish as soon as it finds one matching row. -Kevin >>> Junaili Lie 06/08/05 2:34 PM >>> Hi, I have the following table: person - primary key id, and some attributes food - primary key id, foreign key p_id reference to table person. table food store all the food that a person is eating. The more recent food is indicated by the higher food.id. I need to find what is the most recent food a person ate for every person. The query: 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) Thank you in advance. ---------------------------(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