DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=DcK+lim1T8NMnnDh5HpUE7h8C+cWHB4ckuXWjv3FdGHQ7dnbR+QaeXMxZvY9WZoagpyXTtGjLCY7k3bagTum7yvqZgcbKw5IKx7DjbNDThohynpbuyD6OM1QPw4PW67K6BfS84hWemHZ0RcjdTYKp+qAtx/R7l9WoG1rCnYi+ac=
Message-ID: <8d04ce9905060918306fc4afb8@mail.gmail.com>
Date: Thu, 9 Jun 2005 18:30:37 -0700
From: Junaili Lie <junaili@gmail.com>
Reply-To: Junaili Lie <junaili@gmail.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Subject: Re: Help with rewriting query
Cc: pgsql-performance@postgresql.org
In-Reply-To: <s2a778e0.061@gwmta.wicourts.gov>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <s2a778e0.061@gwmta.wicourts.gov>

Hi Kevin,
Thanks for the reply.
I tried that query. It definately faster, but not fast enough (took
around 50 second to complete).
I have around 2.5 million on food and 1000 on person.
Here is the query plan:
                                                   QUERY PLAN
---------------------------------------------------------------------------=
-------------------------------------
 Merge Join  (cost=3D0.00..11662257.52 rows=3D1441579 width=3D16)
   Merge Cond: ("outer".id =3D "inner".p_id)
   ->  Index Scan using person_pkey on person p  (cost=3D0.00..25.17
rows=3D569 width=3D8)
   ->  Index Scan using p_id_food_index on food f=20
(cost=3D0.00..11644211.28 rows=3D1441579 width=3D16)
         Filter: (NOT (subplan))
         SubPlan
           ->  Index Scan using p_id_food_index on food f2=20
(cost=3D0.00..11288.47 rows=3D2835 width=3D177)
                 Index Cond: (p_id =3D $0)
                 Filter: (id > $1)
(9 rows)

I appreciate if you have further ideas to troubleshoot this issue.
Thank you!

On 6/8/05, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
> 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.
>=20
> 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.
>=20
> select p.id as p_id, p.fullname, f.id, f.foodtype, f.ts
> from food f join person p
> on f.p_id =3D p.id
> and not exists (select * from food f2 where f2.p_id =3D f.p_id and f2.id =
>
> f.id)
> order by p_id
>=20
> 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.
>=20
> -Kevin
>=20
>=20
> >>> Junaili Lie <junaili@gmail.com> 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.
>=20
> table food store all the food that a person is eating. The more recent
> food is indicated by the higher food.id.
>=20
> 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=3Df.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)
>=20
> Thank you in advance.
>=20
> ---------------------------(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
>=20
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>=20
>               http://www.postgresql.org/docs/faq
>