Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 96BDF64FE73
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 13:58:44 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 71491-02-2
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 13:58:37 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.30])
	by mail.postgresql.org (Postfix) with ESMTP id 0710965026B
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Nov 2008 13:58:36 -0400 (AST)
Received: by yw-out-2324.google.com with SMTP id 3so229621ywj.73
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Nov 2008 09:58:36 -0800 (PST)
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:cc:in-reply-to:mime-version:content-type:references;
	bh=yh9gtOK6zCNEgk02kBudn5pQgKay0SEWaboeGGTBEVE=;
	b=q7p7P5x0WTkxoTubhed/SCePTWY9jeAvg9uwjgQZEv1IfXgYBy/tzUNMB8BaoouCuJ
	p6k5s5SYmG9UJrzLG+5fthPzNcSyYmyiFyyHYxvORRbmY7GjifK0UFbfLNqSEAB4BiHO
	3QK2G3KwybOBhRcF3JX3poHw78mxG74jSsV50=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:references;
	b=rleNOrGpLICDWx/GMButxpiQexXLpKcVq6RVIUPjH1f+0wdo1i3iuZmOQTfwMmzAAt
	fZ7WRoiIZhyNMuXCiqBuekiSB99ijXa9vptHu6cA4SN7qVmoFJ6aBgjIdxeZJmG1zoTV
	D44P/s1+boA1FLDpOwM0gvlC/ZZiK7E0+yk2g=
Received: by 10.142.174.8 with SMTP id w8mr3583515wfe.318.1226512715687;
	Wed, 12 Nov 2008 09:58:35 -0800 (PST)
Received: by 10.142.79.21 with HTTP; Wed, 12 Nov 2008 09:58:35 -0800 (PST)
Message-ID: <1d709ecc0811120958k5be6235vc3dbeb6fb58f4a68@mail.gmail.com>
Date: Wed, 12 Nov 2008 09:58:35 -0800
From: "Vladimir Sitnikov" <sitnikov.vladimir@gmail.com>
To: "Richard Huxton" <dev@archonet.com>
Subject: Re: slow full table update
Cc: firerox@centrum.cz, tv@fuzzy.cz, pgsql-performance@postgresql.org
In-Reply-To: <491B1653.3040600@archonet.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_16029_11942909.1226512715681"
References: <200811101729.14977@centrum.cz> <200811101730.11057@centrum.cz>
	<200811101730.15902@centrum.cz>
	<61593.89.102.139.23.1226335300.squirrel@sq.gransy.com>
	<200811121725.30238@centrum.cz>
	<63509.89.102.139.23.1226508479.squirrel@sq.gransy.com>
	<200811121825.26487@centrum.cz> <491B1653.3040600@archonet.com>
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: 200811/102
X-Sequence-Number: 31833

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

> >
> >   Recheck Cond: ((sid > 20000) AND (sid < 30000))
> >
> >   ->  Bitmap Index Scan on pk_songs2  (cost=0.00..151.59 rows=8931
> width=0) (actual time=4.071..4.071 rows=9579 loops=1)
> >
> >         Index Cond: ((sid > 20000) AND (sid < 30000))
> >
> > Is there a way to run this query on sigle  throughpass with no Recheck
> Cond?
>
> "Recheck Cond" is somewhat misleading here.

Bitmap Index Scan has almost void "recheck" impact in case the whole bitmap
fits in work_mem. That means bitmap scan degrades when the number of rows in
table (not the total number of returned rows) is greater than
work_mem*1024*8. 60'000 rows bitmap scan will require 60'000/8=7'500 bytes ~
8Kbytes of memory to run without additional recheck, thus I do not believe
it hurts you in this particular case


Regards,
Vladimir Sitnikov

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

<br><div class="gmail_quote"><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">
&gt;<br>
&gt; &nbsp; Recheck Cond: ((sid &gt; 20000) AND (sid &lt; 30000))<br>
&gt;<br>
&gt; &nbsp; -&gt; &nbsp;Bitmap Index Scan on pk_songs2 &nbsp;(cost=0.00..151.59 rows=8931 width=0) (actual time=4.071..4.071 rows=9579 loops=1)<br>
&gt;<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; Index Cond: ((sid &gt; 20000) AND (sid &lt; 30000))<br>
&gt;<br>
&gt; Is there a way to run this query on sigle &nbsp;throughpass with no Recheck Cond?<br>
<br></div>
</blockquote><div>&quot;Recheck Cond&quot; is somewhat misleading here. <br>&nbsp;<br>Bitmap Index Scan has almost void &quot;recheck&quot; impact in case the whole bitmap fits in work_mem. That means bitmap scan degrades when the number of rows in table (not the total number of returned rows) is greater than work_mem*1024*8. 60&#39;000 rows bitmap scan will require 60&#39;000/8=7&#39;500 bytes ~ 8Kbytes of memory to run without additional recheck, thus I do not believe it hurts you in this particular case<br>
<br><br>Regards,<br>Vladimir Sitnikov<br></div></div>

------=_Part_16029_11942909.1226512715681--