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: <4CD97FDE02000025000374D1@gw.wicourts.gov> 
References: <4CD9663502000025000374B1@gw.wicourts.gov>
	<4CD97FDE02000025000374D1@gw.wicourts.gov>
Comments: In-reply-to "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
	message dated "Tue, 09 Nov 2010 17:07:42 -0600"
Date: Tue, 09 Nov 2010 18:17:42 -0500
Message-ID: <20215.1289344662@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
>> samples  %        symbol name
>> 2320174  33.7617  index_getnext
 
> I couldn't resist seeing where the time went within this function.
> Over 13.7% of the opannotate run time was on this bit of code:

>   /*
>    * The xmin should match the previous xmax value, else chain is
>    * broken.  (Note: this test is not optional because it protects
>    * us against the case where the prior chain member's xmax aborted
>    * since we looked at it.)
>    */
>   if (TransactionIdIsValid(scan->xs_prev_xmax) &&
>       !TransactionIdEquals(scan->xs_prev_xmax,
>                         HeapTupleHeaderGetXmin(heapTuple->t_data)))
>       break;
 
> I can't see why it would be such a hotspot, but it is.

Main-memory access waits, maybe?  If at_chain_start is false, that xmin
fetch would be the first actual touch of a given heap tuple, and could
be expected to have to wait for a cache line to be pulled in from RAM.
However, you'd have to be spending a lot of time chasing through long
HOT chains before that would happen enough to make this a hotspot...

			regards, tom lane