From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: anti-join chosen even when slower than old plan
Date: Tue, 09 Nov 2010 19:08:00 -0500
Message-ID: <20961.1289347680@sss.pgh.pa.us> (raw)
In-Reply-To: <4CD9663502000025000374B1@gw.wicourts.gov>
References: <4CD9663502000025000374B1@gw.wicourts.gov>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> The semi-join and anti-join have helped us quite a bit, but we have
> seen a situation where anti-join is chosen even though it is slower
> than the "old fashioned" plan. I know there have been other reports
> of this, but I just wanted to go on record with my details.
In principle, the old-style plan ought to be equivalent to a nestloop
antijoin with a seqscan of DbTranLogRecord on the outside and an
indexscan of DbTranRepository on the inside. Can you force it to choose
such a plan by setting enable_mergejoin off (and maybe enable_hashjoin
too)? If so, it'd be interesting to see the estimated costs and actual
runtime on 9.0 for that plan.
It would also be interesting to check estimated and actual costs for the
SELECT COUNT(*) versions of these queries, ie, no actual delete. I'm
suspicious that the cost differential has nothing to do with antijoin
vs. subplan, and everything to do with whether the targeted tuples are
being deleted in physical order (thus improving locality of access for
the deletions). If it's the latter, see previous discussions about
possibly sorting update/delete targets by CTID before applying the
actions.
regards, tom lane
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, Kevin.Grittner@wicourts.gov
Subject: Re: anti-join chosen even when slower than old plan
In-Reply-To: <20961.1289347680@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