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: Wed, 10 Nov 2010 10:33:22 -0500
Message-ID: <4061.1289403202@sss.pgh.pa.us> (raw)
In-Reply-To: <4CDA62C60200002500037512@gw.wicourts.gov>
References: <4CD9663502000025000374B1@gw.wicourts.gov>
<4CD9663502000025000374B1@gw.wicourts.gov>
<20961.1289347680@sss.pgh.pa.us>
<4CDA62C60200002500037512@gw.wicourts.gov>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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)?
> Well, I got what I think is the equivalent plan by adding OFFSET 0
> to the subquery:
No, that *is* the old-style plan (plus a useless Limit node, which will
surely make it marginally slower). My point was that a nestloop
antijoin plan should have the same access pattern and hence very similar
performance, maybe even a little better due to not having the SubPlan
machinery in there.
> But wait -- it turns out that this pain was self-inflicted. Based
> on heavy testing of the interactive queries which users run against
> this database we tuned the database for "fully-cached" settings,
> with both random_page_cost and _seq_page_cost at 0.1.
Ah. So it was underestimating the cost of the full-table indexscans,
and my guess about nonsequential application of the delete actions
wasn't the right guess. The merge antijoin does seem like it should be
the fastest way of doing such a large join, so I think the problem is
solved.
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: <4061.1289403202@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