To: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
cc: pgsql-performance@postgresql.org
Subject: Re: anti-join chosen even when slower than old plan 
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>
Comments: In-reply-to "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
	message dated "Wed, 10 Nov 2010 09:15:50 -0600"
Date: Wed, 10 Nov 2010 10:33:22 -0500
Message-ID: <4061.1289403202@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"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