From: Atesz <atesz@ritek.hu>
To: pgsql-sql@postgresql.org
Subject: Re: Join issue on a maximum value
Date: Mon, 26 Apr 2004 15:05:14 +0200
Message-ID: <000901c42b8f$20592f90$0b02010a@atesz> (raw)
On Wed, Apr 21, 2004 at 14:29:34 -0400,
Heflin <hhogan ( at ) tampabay ( dot ) rr ( dot ) com> wrote:
>
> SELECT auction.auction_id, image.image_id, image.image_descr FROM
> auction JOIN image ON auction.auction_id = image.auction_id WHERE
> auction.auction_owner = 'Mabel';
>
In my opinion there are 2 problem: how can you make the query and how
many rows is in the result (performace)? Usually when you have more rows
in the result you can use the LIMIT and OFFSET. So you can reach the
result to unfold more pages. So I bult in these LIMIT and OFFSET into
the queries.
2 new possibilities:
SELECT IDSEL.*, (SELECT image_descr FROM image WHERE
IDSEL.image_id=image.image_id) FROM (
SELECT auction.auction_id, max(image.image_id) AS image_id FROM
auction JOIN image USING (auction_id)
WHERE auction_owner = 'Mabel'
GROUP BY auction.auction_id
ORDER BY auction.auction_id
LIMIT 10 OFFSET 0
) AS IDSEL;
or
SELECT DISTINCT ON (image.auction_id) image.auction_id,
image.image_id, image.image_descr FROM auction
JOIN image USING (auction_id)
WHERE auction.auction_owner = 'Mabel'
ORDER BY image.auction_id, (-image.image_id)
LIMIT 10 OFFSET 0;
Index suggestions:
CREATE INDEX auction_auction_owner on auction(auction_owner);
CREATE INDEX image_auction_id on image(auction_id);
CREATE INDEX image_auction_id_neg_image_id on image(auction_id,
(-image_id)); -- Specially for the second solution
The second solution build on Bruno Wolff III's ideas:
http://archives.postgresql.org/pgsql-sql/2004-04/msg00211.php and
http://archives.postgresql.org/pgsql-sql/2004-04/msg00262.php .
You can see more solutions for your problem. You have to select the
best performance solution for your specific databse. Use the EXPLAIN!
Regards, Antal Attila
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-sql@postgresql.org
Cc: atesz@ritek.hu
Subject: Re: Join issue on a maximum value
In-Reply-To: <000901c42b8f$20592f90$0b02010a@atesz>
* 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