Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 11FB364FD30
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 18 Sep 2008 14:30:48 -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 67678-01-3
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 18 Sep 2008 14:30:44 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from wf-out-1314.google.com (wf-out-1314.google.com
 [209.85.200.171])
	by postgresql.org (Postfix) with ESMTP id 9AFD864FD01
	for <pgsql-performance@postgresql.org>;
	Thu, 18 Sep 2008 14:30:44 -0300 (ADT)
Received: by wf-out-1314.google.com with SMTP id 25so6709wfc.28
	for <pgsql-performance@postgresql.org>;
	Thu, 18 Sep 2008 10:30:43 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to
	:subject:mime-version:content-type;
	bh=/Rj1v6Xf3EtiH3ibBASXMRHCvYdTrjUnWMmVY5+jxSE=;
	b=Vp3NO0ZKlmzORwewdQb/YJ8mWmi5ad0VUfBiuNjnJaDfRvsPzH3zqmx8W4dy0XEWbq
	B2mzKzlE6Y8iZsudw9KXDqYJAo1F9pFwbFe98W5T0dQX5eogxHe1OR9S8KxDUdhrQ3pf
	Ux03WVbz82jpe0VPQdC1llcU+jdebhGpv+FWU=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type;
	b=bDfMgk/mPLWckeCIdAHC0FrLZC+c4fOEtG+C4x8c0VzOEwQ24KtdV2GLNCLlHAmocW
	FauRzmTJxDDY2FE7048v6bLFN+GsW+wI7IWVVpPFo3hJGjjo//Y0UK7SsqKnjcViOu3Y
	B1sa5lOg+2iQP2StMORizn9kdVc0xhEHyqZwc=
Received: by 10.114.211.1 with SMTP id j1mr4151941wag.103.1221759043036;
	Thu, 18 Sep 2008 10:30:43 -0700 (PDT)
Received: by 10.114.120.19 with HTTP; Thu, 18 Sep 2008 10:30:42 -0700 (PDT)
Message-ID: <d4e11e980809181030t3f09db94vd5b69d7868271906@mail.gmail.com>
Date: Thu, 18 Sep 2008 13:30:42 -0400
From: "Nikolas Everett" <nik9000@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Why does this query write to the disk?
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_24688_647193.1221759043016"
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/179
X-Sequence-Number: 31492

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

List,

I'm a bit confused as to why this query writes to the disk:
SELECT count(*)
FROM    bigbigtable
WHERE customerid IN (SELECT customerid FROM
smallcustomertable)
AND x !=
'special'

AND y IS NULL

It writes a whole bunch of data to the disk that has the tablespace where
bigbigtable lives as well as writes a little data to the main disk.  It
looks like its is actually WAL logging these writes.

Here is the EXPLAIN ANALYZE:
Aggregate  (cost=46520194.16..46520194.17 rows=1 width=0) (actual
time=4892191.995..4892191.995 rows=1 loops=1)
  ->  Hash IN Join  (cost=58.56..46203644.01 rows=126620058 width=0) (actual
time=2.938..4840349.573 rows=79815986 loops=1)
        Hash Cond: ((bigbigtable.customerid)::text =
(smallcustomertable.customerid)::text)
        ->  Seq Scan on bigbigtable  (cost=0.00..43987129.60 rows=126688839
width=11) (actual time=0.011..4681248.143 rows=128087340 loops=1)
              Filter: ((y IS NULL) AND ((x)::text <> 'special'::text))
        ->  Hash  (cost=35.47..35.47 rows=1847 width=18) (actual
time=2.912..2.912 rows=1847 loops=1)
              ->  Seq Scan on smallcustomertable  (cost=0.00..35.47
rows=1847 width=18) (actual time=0.006..1.301 rows=1847 loops=1)
Total runtime: 4892192.086 ms

Can someone point me to some documentation as to why this writes to disk?

Thanks,
Nik

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

<div dir="ltr">List,<br><br>I&#39;m a bit confused as to why this query writes to the disk:<br>SELECT count(*)<br>FROM&nbsp;&nbsp;&nbsp; bigbigtable<br>WHERE customerid IN (SELECT customerid FROM smallcustomertable)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
AND x != &#39;special&#39;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>AND y IS NULL&nbsp; <br><br>It writes a whole bunch of data to the disk that has the tablespace where bigbigtable lives as well as writes a little data to the main disk.&nbsp; It looks like its is actually WAL logging these writes.<br>
<br>Here is the EXPLAIN ANALYZE:<br>Aggregate&nbsp; (cost=46520194.16..46520194.17 rows=1 width=0) (actual time=4892191.995..4892191.995 rows=1 loops=1)<br>&nbsp; -&gt;&nbsp; Hash IN Join&nbsp; (cost=58.56..46203644.01 rows=126620058 width=0) (actual time=2.938..4840349.573 rows=79815986 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Hash Cond: ((bigbigtable.customerid)::text = (smallcustomertable.customerid)::text)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on bigbigtable&nbsp; (cost=0.00..43987129.60 rows=126688839 width=11) (actual time=0.011..4681248.143 rows=128087340 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: ((y IS NULL) AND ((x)::text &lt;&gt; &#39;special&#39;::text))<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Hash&nbsp; (cost=35.47..35.47 rows=1847 width=18) (actual time=2.912..2.912 rows=1847 loops=1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on smallcustomertable&nbsp; (cost=0.00..35.47 rows=1847 width=18) (actual time=0.006..1.301 rows=1847 loops=1)<br>
Total runtime: 4892192.086 ms<br><br>Can someone point me to some documentation as to why this writes to disk?<br><br>Thanks,<br>Nik<br></div>

------=_Part_24688_647193.1221759043016--