Date: Thu, 26 May 2011 09:08:07 -0700 (PDT)
From: panam <panam@gmx.net>
To: pgsql-performance@postgresql.org
Message-ID: <1306426087061-4429125.post@n5.nabble.com>
In-Reply-To: <4DDE21FE020000250003DD49@gw.wicourts.gov>
References: <1306210464806-4420974.post@n5.nabble.com>
	<4DDB47BC.8030504@postnewspapers.com.au>
	<1306247697267-4422247.post@n5.nabble.com>
	<4DDC6109.7060708@postnewspapers.com.au>
	<1306341749094-4425890.post@n5.nabble.com>
	<4DDDBD96.4010809@postnewspapers.com.au>
	<1306413217406-4428435.post@n5.nabble.com>
	<BANLkTin6XpuCUCk97R6MNcuwd8bVvJ3ZcQ@mail.gmail.com>
	<4DDE21FE020000250003DD49@gw.wicourts.gov>
Subject: Re: Hash Anti Join performance degradation
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hi all,


C=C3=A9dric Villemain-3 wrote:
>=20
> without explaining further why the antijoin has bad performance
> without cluster, I wonder why you don't use this query :
>=20
> SELECT  b.id,
>                   max(m.id)
> FROM box b, message m
> WHERE m.box_id =3D b.id
> GROUP BY b.id;
>=20
> looks similar and fastest.
>=20
I actually did use a similar strategy in the meantime (during my problem
with the "left join" query we are talking about here all the time) for
mitigation.
It was
SELECT MAX(e.id) FROM event_message e WHERE e.box_id =3D id
and it performed worse in comparison to the "left join" query in the genera=
l
case (i.e. before my problems began).
At the end of this post is an explanation why I think I cannot use the
solution you suggested above.


Kevin Grittner wrote:
>=20
>  Each connection can allocate work_mem, potentially several times.=20
> On a machines without hundreds of GB of RAM, that pair of settings
> could cause severe swapping.
>=20
Indeed, thanks for the warning. These settings are not for production but t=
o
exclude a performance degradation because of small cache sizes.


Kevin Grittner wrote:
>=20
> I think you would need a left join to actually get identical
> results:
> =20
> SELECT  b.id, max(m.id)
>   FROM box b
>   LEFT JOIN message m ON m.box_id =3D b.id
>   GROUP BY b.id;
>=20
> But yeah, I would expect this approach to be much faster.  Rather
> easier to understand and harder to get wrong, too.
> =20
>=20
Correct, it is much faster, even with unclustered ids.
However, I think I cannot use it because of the way that query is generated
(by hibernate).
The (simplyfied) base query is just

SELECT b.id from box

the subquery

(SELECT  m1.id FROM message m1=20
   LEFT JOIN message m2=20
      ON (m1.box_id =3D m2.box_id  AND m1.id < m2.id )=20
   WHERE m2.id IS NULL AND m1.box_id =3D b.id) as lastMessageId

is due to a hibernate formula (containing more or less plain SQL) to
determine the last message id for that box. It ought to return just one row=
,
not multiple. So I am constrained to the subquery in all optimization
attemps (I cannot combine them as you did), at least I do not see how. If
you have an idea for a more performant subquery though, let me know, as thi=
s
can easily be replaced.

Thanks for your help and suggestions
panam

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Hash-=
Anti-Join-performance-degradation-tp4420974p4429125.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.