From: Junaili Lie <junaili@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Help with rewriting query
Date: Wed, 8 Jun 2005 15:48:27 -0700
Message-ID: <8d04ce990506081548355991bb@mail.gmail.com> (raw)
In-Reply-To: <20050608195625.GM8451@tobias.nordicbet.com>
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=# explain select f.p_id, max(f.id) from Food f, Person p where
(f.p_id = p.id) group by p.id;
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;
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 <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=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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: junaili@gmail.com
Subject: Re: Help with rewriting query
In-Reply-To: <8d04ce990506081548355991bb@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox