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=NRKv5qFJ0zZ1zu8mLiKDj+lqbBMivrKAhcpTZX6+mgLmxKzUQlGbvxxhrB/jM1mqgY5y23H5P/rfYoVUuPJbdop6ZZoublPNzCqV9Es19abhiHlNKCDmi8J2TjVQTFmu1vDp3DZFhF/xwxCBMHHGRKOCN41eYRSM2ZyKxaSa+kw=
Message-ID: <8d04ce9905060918264740a0da@mail.gmail.com>
Date: Thu, 9 Jun 2005 18:26:09 -0700
From: Junaili Lie <junaili@gmail.com>
Reply-To: Junaili Lie <junaili@gmail.com>
To: Bruno Wolff III <bruno@wolff.to>,
	pgsql-performance@postgresql.org
Subject: Re: Help with rewriting query
In-Reply-To: <20050609025907.GB6139@wolff.to>
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>
	<8d04ce990506081548355991bb@mail.gmail.com>
	<20050609025907.GB6139@wolff.to>

Hi Bruno,
I followed your suggestion.
The query plan shows that it uses the index (id, person_id). However,
the execution time is still slow. I have to do ctl-C to stop it.
Maybe something is wrong with my postgresql config.
It's running Solaris on dual Opteron, 4GB.
I allocated around 128MB for sorting and more than 80% for
effective_cache_size and shared_buffers =3D 32768.
Any further ideas is much appreciated.


On 6/8/05, Bruno Wolff III <bruno@wolff.to> wrote:
> On Wed, Jun 08, 2005 at 15:48:27 -0700,
>  Junaili Lie <junaili@gmail.com> 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=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;
>=20
> The above is going to require reading all the food table (assuming no
> orphaned records), so the plan below seems reasonable.
>=20
> >                                                   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;
>=20
> 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.
>=20
> >                                                QUERY PLAN
> > -----------------------------------------------------------------------=
------------------------------------
> >  Seq Scan on Person p  (cost=3D100000000.00..100007015.24 rows=3D569 wi=
dth=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 =
group
> > > > by f.p_id will work.
> > > > But I understand this is not the most efficient way. Is there anoth=
er
> > > > 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=3Dp.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 whi=
ch 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 y=
our
> >       joining column's datatypes do not match
>