DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=NaZIfKmNJOSeQRj98KHx9unov6VQh3UsxJ8MdZdtsr0vo9WO1npD8oXg0HSZu5yWdEAUh7548GHgtUt6dQ3r6T6OvwJyRklps1KFRqhKCsClcD3MpKkjuj3jJAeXGBjawofUE5/9HuGMFJmgI+NiZe8xT42Gt1qpAn50Zf/6FMQ=
Message-ID: <8d04ce990506081548355991bb@mail.gmail.com>
Date: Wed, 8 Jun 2005 15:48:27 -0700
From: Junaili Lie <junaili@gmail.com>
Reply-To: Junaili Lie <junaili@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Help with rewriting query
In-Reply-To: <20050608195625.GM8451@tobias.nordicbet.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <8d04ce990506081234121995f9@mail.gmail.com>
	<20050608195625.GM8451@tobias.nordicbet.com>

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=3D# explain select f.p_id, max(f.id) from Food f, Person p where
(f.p_id =3D p.id) group by p.id;
                                                  QUERY PLAN
---------------------------------------------------------------------------=
-------------------------------------
 GroupAggregate  (cost=3D0.00..214585.51 rows=3D569 width=3D16)
  ->  Merge Join  (cost=3D0.00..200163.50 rows=3D2884117 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 person_id_food_index on food f
(cost=3D0.00..164085.54 rows=3D2884117 width=3D16)
(5 rows)


TEST1=3D# explain select p.id, (Select f.id from food f where
f.p_id=3Dp.id order by f.id desc limit 1) from person p;
                                               QUERY PLAN
---------------------------------------------------------------------------=
--------------------------------
 Seq Scan on Person p  (cost=3D100000000.00..100007015.24 rows=3D569 width=
=3D8)
  SubPlan
    ->  Limit  (cost=3D0.00..12.31 rows=3D1 width=3D8)
          ->  Index Scan Backward using food_pkey on food f
(cost=3D0.00..111261.90 rows=3D9042 width=3D8)
                Filter: (p_id =3D $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=3Df.p_id grou=
p
> > 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
> eventually, try something like
>=20
>  select p.id,(select f.id from food f where f.p_id=3Dp.id order by f.id d=
esc limit 1)
>  from person p
>=20
> not tested, no warranties.
>=20
> Since subqueries can be inefficient, use "explain analyze" to see which o=
ne
> is actually better.
>=20
> This issue will be solved in future versions of postgresql.
>=20
> --
> Tobias Brox, +47-91700050
> Tallinn
>