Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 74BFF64FCC2
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 18 Sep 2008 15:33:44 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 84081-09 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 18 Sep 2008 15:33:34 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.29])
	by postgresql.org (Postfix) with ESMTP id 35D8864FCBF
	for <pgsql-performance@postgresql.org>;
	Thu, 18 Sep 2008 15:33:33 -0300 (ADT)
Received: by yw-out-2324.google.com with SMTP id 3so7133ywj.73
	for <pgsql-performance@postgresql.org>;
	Thu, 18 Sep 2008 11:33:32 -0700 (PDT)
Received: by 10.143.17.5 with SMTP id u5mr819812wfi.39.1221762811672;
	Thu, 18 Sep 2008 11:33:31 -0700 (PDT)
Received: by 10.143.39.19 with HTTP; Thu, 18 Sep 2008 11:33:31 -0700 (PDT)
Message-ID: <a1ec7d000809181133p20847cdbw3c25ca47a57d2765@mail.gmail.com>
Date: Thu, 18 Sep 2008 11:33:31 -0700
From: "Scott Carey" <scott@richrelevance.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: Why does this query write to the disk?
Cc: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>,
	"Nikolas Everett" <nik9000@gmail.com>, pgsql-performance@postgresql.org
In-Reply-To: <23587.1221761600@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_39933_32707945.1221762811629"
References: <d4e11e980809181030t3f09db94vd5b69d7868271906@mail.gmail.com>
	<48D24E6C.EE98.0025.0@wicourts.gov> <23587.1221761600@sss.pgh.pa.us>
X-Google-Sender-Auth: be15a559b2046966
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200809/183
X-Sequence-Number: 31496

------=_Part_39933_32707945.1221762811629
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Under what conditions does EXPLAIN ANALYZE report spilling work_mem to
disk?  When does it not report work_mem or other overflow to disk?
I know that a planned disk-sort shows up.  I have also seen it report a
hash-agg on disk, but this was a while ago and rather difficult to reproduce
and I'm somewhat confident I have seen it spill to temp disk without
reporting it in EXPLAIN ANALYZE, but I could be wrong.

On Thu, Sep 18, 2008 at 11:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> > "Nikolas Everett" <nik9000@gmail.com> wrote:
> >> I'm a bit confused as to why this query writes to the disk:
>
> > It's probably writing hint bits to improve performance of subsequent
> > access to the table.  The issue is discussed here:
> > http://wiki.postgresql.org/wiki/Hint_Bits
>
> Hint-bit updates wouldn't be WAL-logged.  If the table has been around a
> long time, it might be freezing old tuples, which *would* be WAL-logged
> (since 8.2 or so) --- but that would be a one-time, non-repeatable
> behavior.  How sure are you that there was WAL output?
>
> What I was thinking was more likely was that the hash table for the hash
> join was spilling out to temp files.  That wouldn't be WAL-logged
> either, but depending on your tablespace setup it might result in I/O on
> some other disk than the table proper.
>
>                        regards, tom lane
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

------=_Part_39933_32707945.1221762811629
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div dir="ltr">Under what conditions does EXPLAIN ANALYZE report spilling work_mem to disk?&nbsp; When does it not report work_mem or other overflow to disk?<br>I know that a planned disk-sort shows up.&nbsp; I have also seen it report a hash-agg on disk, but this was a while ago and rather difficult to reproduce and I&#39;m somewhat confident I have seen it spill to temp disk without reporting it in EXPLAIN ANALYZE, but I could be wrong.<br>
<br><div class="gmail_quote">On Thu, Sep 18, 2008 at 11:13 AM, Tom Lane <span dir="ltr">&lt;<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt;</span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div class="Ih2E3d">&quot;Kevin Grittner&quot; &lt;<a href="mailto:Kevin.Grittner@wicourts.gov">Kevin.Grittner@wicourts.gov</a>&gt; writes:<br>
&gt; &quot;Nikolas Everett&quot; &lt;<a href="mailto:nik9000@gmail.com">nik9000@gmail.com</a>&gt; wrote:<br>
&gt;&gt; I&#39;m a bit confused as to why this query writes to the disk:<br>
<br>
</div><div class="Ih2E3d">&gt; It&#39;s probably writing hint bits to improve performance of subsequent<br>
&gt; access to the table. &nbsp;The issue is discussed here:<br>
&gt; <a href="http://wiki.postgresql.org/wiki/Hint_Bits" target="_blank">http://wiki.postgresql.org/wiki/Hint_Bits</a><br>
<br>
</div>Hint-bit updates wouldn&#39;t be WAL-logged. &nbsp;If the table has been around a<br>
long time, it might be freezing old tuples, which *would* be WAL-logged<br>
(since 8.2 or so) --- but that would be a one-time, non-repeatable<br>
behavior. &nbsp;How sure are you that there was WAL output?<br>
<br>
What I was thinking was more likely was that the hash table for the hash<br>
join was spilling out to temp files. &nbsp;That wouldn&#39;t be WAL-logged<br>
either, but depending on your tablespace setup it might result in I/O on<br>
some other disk than the table proper.<br>
<br>
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;regards, tom lane<br>
<div><div></div><div class="Wj3C7c"><br>
--<br>
Sent via pgsql-performance mailing list (<a href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href="http://www.postgresql.org/mailpref/pgsql-performance" target="_blank">http://www.postgresql.org/mailpref/pgsql-performance</a><br>
</div></div></blockquote></div><br></div>

------=_Part_39933_32707945.1221762811629--