Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-performance-owner+M49370=pgsql-performance-ar@postgresql.org>)
	id 1UB9rn-0005d1-Gh for pgsql-performance@arkaria.postgresql.org;
	Thu, 28 Feb 2013 20:14:11 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-performance-owner+M49370=pgsql-performance-ar@postgresql.org>)
	id 1UB9rm-00056N-SB for pgsql-performance@arkaria.postgresql.org;
	Thu, 28 Feb 2013 20:14:10 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <stonec.register@sympatico.ca>) id 1UB9rl-000565-Lj
	for pgsql-performance@postgresql.org; Thu, 28 Feb 2013 20:14:09 +0000
Received: from toroondcbmts05-srv.bellnexxia.net ([207.236.237.39])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <stonec.register@sympatico.ca>) id 1UB9rh-0007kj-FI
	for pgsql-performance@postgresql.org; Thu, 28 Feb 2013 20:14:08 +0000
Received: from toip41-bus.srvr.bell.ca ([67.69.240.42])
	by toroondcbmts05-srv.bellnexxia.net
	(InterMail vM.8.00.01.00 201-2244-105-20090324) with ESMTP id
	<20130228201403.YAPZ26520.toroondcbmts05-srv.bellnexxia.net@toip41-bus.srvr.bell.ca>;
	Thu, 28 Feb 2013 15:14:03 -0500
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: 
 AgIFAMisL1FGMVM9/2dsb2JhbABFgkODI7xJfBZzgiABBQgCHgVMDQMCBgMNORk+AgQBHQWIA7ksiAaOcCQHg0ADgSGHSYUtjkOKUYMm
X-IronPort-AV: E=Sophos;i="4.84,756,1355115600";
	d="scan'208,217";a="420322863"
Received: from unknown (HELO CAPRICA) ([70.49.83.61])
	by toip41-bus.srvr.bell.ca with ESMTP; 28 Feb 2013 15:13:51 -0500
From: "Carlo Stonebanks" <stonec.register@sympatico.ca>
To: "'Gavin Flower'" <GavinFlower@archidevsys.co.nz>,
	"'Nikolas Everett'" <nik9000@gmail.com>
Cc: "'Marc Mamin'" <M.Mamin@intershop.de>,
	"'Jeff Janes'" <jeff.janes@gmail.com>, <pgsql-performance@postgresql.org>
References: <005701ce0fb5$6f841f30$4e8c5d90$@sympatico.ca>
	<001501ce1054$7b518380$71f48a80$@sympatico.ca>
	<CAMkU=1yG884Nak4zPoNHwDGpC7w2nrKH6_xztPw4b7P9am0wdg@mail.gmail.com>
	<B6F6FD62F2624C4C9916AC0175D56D8801B17F@jenmbs01.ad.intershop.net>
	<006201ce1125$2c554150$84ffc3f0$@sympatico.ca>
	<CAPmjWd1On_uAp6suqYoHuh3ksKD0xn6tOhOM_Z2vaweO-vexSA@mail.gmail.com>
	<5127CFF8.4010402@archidevsys.co.nz>
In-Reply-To: <5127CFF8.4010402@archidevsys.co.nz>
Subject: Re: Are bitmap index scans slow to start?
Date: Thu, 28 Feb 2013 15:13:50 -0500
Message-ID: <004601ce15f0$1fc6ce20$5f546a60$@sympatico.ca>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0047_01CE15C6.36F13B50"
X-Mailer: Microsoft Outlook 14.0
Thread-Index: 
 AQHaXprYpEPEIaXdLmMZOCaAwfiQhwMAB23aAo8fOP4CRcCoxQGZrFXnAfFz5MACHRVr3pgLM94A
Content-Language: en-ca
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multipart message in MIME format.

------=_NextPart_000_0047_01CE15C6.36F13B50
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

<<Could you use CLUSTER on the table after it had been closed off?  If
appropriate, that should make the queries run much faster, as elated entries
will be in the same or nearby blocks on disk.

>> 

 

Technically, yes. That would really help, but the issue is scheduling.
Although the logs are closed off for writes, they aren't closed off for
reads, ref PG documentation: "When a table is being clustered, an ACCESS
EXCLUSIVE lock is acquired on it. This prevents any other database
operations (both reads and writes) from operating on the table until the
CLUSTER is finished."

 

Not ideal, but a lot better than doing nothing at all!


------=_NextPart_000_0047_01CE15C6.36F13B50
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40"><head><META =
HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii"><meta name=3DGenerator content=3D"Microsoft Word 14 =
(filtered medium)"><style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman","serif";
	color:black;}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
	{mso-style-priority:99;
	mso-style-link:"Balloon Text Char";
	margin:0cm;
	margin-bottom:.0001pt;
	font-size:8.0pt;
	font-family:"Tahoma","sans-serif";
	color:black;}
span.BalloonTextChar
	{mso-style-name:"Balloon Text Char";
	mso-style-priority:99;
	mso-style-link:"Balloon Text";
	font-family:"Tahoma","sans-serif";
	color:black;}
span.EmailStyle19
	{mso-style-type:personal-reply;
	font-family:"Calibri","sans-serif";
	color:#1F497D;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-size:10.0pt;}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]--></head><body bgcolor=3Dwhite =
lang=3DEN-CA link=3Dblue vlink=3Dpurple><div class=3DWordSection1><p =
class=3DMsoNormal><span =
style=3D'font-size:10.0pt;color:#1F497D'>&lt;&lt;</span><span =
style=3D'font-size:10.0pt'>Could you use CLUSTER on the table after it =
had been closed off?&nbsp; If appropriate, that should make the queries =
run much faster, as elated entries will be in the same or nearby blocks =
on disk.</span><span =
style=3D'font-size:10.0pt;color:#1F497D'><o:p></o:p></span></p><p =
class=3DMsoNormal><span =
style=3D'font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497=
D'>&gt;&gt;<o:p>&nbsp;</o:p></span></p><p class=3DMsoNormal><span =
style=3D'font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497=
D'><o:p>&nbsp;</o:p></span></p><p class=3DMsoNormal><span =
style=3D'font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497=
D'>Technically, yes. That would really help, but the issue is =
scheduling. Although the logs are closed off for writes, they =
aren&#8217;t closed off for reads, ref PG documentation: &#8220;When a =
table is being clustered, an ACCESS EXCLUSIVE lock is acquired on it. =
This prevents any other database operations (both reads and writes) from =
operating on the table until the CLUSTER is =
finished.&#8221;<o:p></o:p></span></p><p class=3DMsoNormal><span =
style=3D'font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497=
D'><o:p>&nbsp;</o:p></span></p><p class=3DMsoNormal><span =
style=3D'font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497=
D'>Not ideal, but a lot better than doing nothing at =
all!<o:p></o:p></span></p></div></body></html>
------=_NextPart_000_0047_01CE15C6.36F13B50--