pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Cédric Villemain <cedric.villemain.debian@gmail.com>
Cc: panam <panam@gmx.net>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [PERFORM] Hash Anti Join performance degradation
Date: Tue, 31 May 2011 23:47:42 -0400
Message-ID: <7913.1306900062@sss.pgh.pa.us> (raw)
In-Reply-To: <BANLkTinbb35QT_57EozGOnszBWuarBgW1g@mail.gmail.com>
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>
	<BANLkTinwJ69W4i6JXVGqodbzZdTZ+SSzyA@mail.gmail.com>
	<BANLkTi==zbH2kepG3OmR=Nt0ArxMvWgwdA@mail.gmail.com>
	<BANLkTimRjbzjqA13MLtjLf1XCini_UAAQw@mail.gmail.com>
	<BANLkTikDKUF53_MhP-T9y9NM7a8z40djXA@mail.gmail.com>
	<BANLkTinbb35QT_57EozGOnszBWuarBgW1g@mail.gmail.com>

Robert Haas <robertmhaas@gmail.com> writes:
> With respect to the root of the issue (why does the anti-join take so
> long?), my first thought was that perhaps the OP was very unlucky and
> had a lot of values that hashed to the same bucket.  But that doesn't
> appear to be the case.

Well, yes it is.  Notice what the subquery is doing: for each row in
"box", it's pulling all matching "box_id"s from message and running a
self-join across those rows.  The hash join condition is a complete
no-op.  And some of the box_ids have hundreds of thousands of rows.

I'd just write it off as being a particularly stupid way to find the
max(), except I'm not sure why deleting just a few thousand rows
improves things so much.  It looks like it ought to be an O(N^2)
situation, so the improvement should be noticeable but not amazing.

And if you force it to not use a hashjoin, suddenly things are better.
Nestloop should also be O(N^2) in this situation, but seemingly it
avoids whatever weird corner case we are hitting here.

As Cedric says, the lack of any CHECK_FOR_INTERRUPTS in this loop is
also problematic.  I'm not sure that right there is an ideal place
to put it, but we need one somewhere in the loop.

			regards, tom lane



view thread (35+ messages)  latest in thread

Message-ID: <7913.1306900062@sss.pgh.pa.us>
Permalink:  ../7913.1306900062@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/7913.1306900062@sss.pgh.pa.us

 · 

reply
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: tgl@sss.pgh.pa.us, robertmhaas@gmail.com, cedric.villemain.debian@gmail.com, panam@gmx.net, pgsql-hackers@postgresql.org
  Subject: Re: [PERFORM] Hash Anti Join performance degradation
  In-Reply-To: <7913.1306900062@sss.pgh.pa.us>

* 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