Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 2DCFD6333DE
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 11 Jan 2010 04:36:53 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 40456-08
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 11 Jan 2010 08:36:38 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ew0-f216.google.com (mail-ew0-f216.google.com
	[209.85.219.216])
	by mail.postgresql.org (Postfix) with ESMTP id 235706326DD
	for <pgsql-performance@postgresql.org>;
	Mon, 11 Jan 2010 04:36:41 -0400 (AST)
Received: by ewy8 with SMTP id 8so21907347ewy.35
	for <pgsql-performance@postgresql.org>;
	Mon, 11 Jan 2010 00:36:40 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:sender:received:in-reply-to
	:references:date:x-google-sender-auth:message-id:subject:from:to:cc
	:content-type; bh=zJdriCTJvgUa9mbrkdsHZ8CSEoY1Ffmy/twyxm2duIk=;
	b=mqw4NLpTb0q+B99uMGotJDIVOYwrWCU2DyXrhM7Wp+XpUi6GKWZSqq856WnDxmP+gI
	rZkGQCXUxQxY1VFFMIz2o5vYGyNTVwRUPhzWHpEApc+nYLZD8k4Qn/Owu8S2fa38OGk+
	rAVf0U+WeznZ5encFex+4FjmvVjEH2cbt1QgM=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:sender:in-reply-to:references:date
	:x-google-sender-auth:message-id:subject:from:to:cc:content-type;
	b=vPso+5sZ0QqTRbwVSTo2TkMugJuK5vSQmKNptgMQXDWj3ermk8dhFkrsnFAZhts+0R
	L+NOugR54R0yfkqyvojCwZIrMF2cwsOmukF2tvjzJSbbygI+WKTaoLUIAVmLxcvILt90
	AyNsLWpawQ33Cu0iElUeFrknyJuNHp/RuPhmQ=
MIME-Version: 1.0
Received: by 10.213.42.83 with SMTP id r19mr1705979ebe.1.1263199000315; Mon,
	11 Jan 2010 00:36:40 -0800 (PST)
In-Reply-To: <603c8f071001101852n7ce69d9atfc0b7aa65d8e357d@mail.gmail.com>
References: <4B49A3AF020000250002E138@gw.wicourts.gov>
	<603c8f071001101852n7ce69d9atfc0b7aa65d8e357d@mail.gmail.com>
Date: Mon, 11 Jan 2010 09:36:40 +0100
X-Google-Sender-Auth: a5176c0aa1e1d1ac
Message-ID: <d4468d971001110036s22796d2bud01f8ec38fd21ce0@mail.gmail.com>
Subject: Re: Choice of bitmap scan over index scan
From: Mathieu De Zutter <mathieu@dezutter.org>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=00148531b663b1c82f047cdf6f73
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201001/183
X-Sequence-Number: 37063

--00148531b663b1c82f047cdf6f73
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Jan 11, 2010 at 3:52 AM, Robert Haas <robertmhaas@gmail.com> wrote:

> On Sun, Jan 10, 2010 at 10:53 AM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
> > seq_page_cost = 0.1
> > random_page_cost = 0.1
>
> These might not even be low enough.  The reason why bitmap index scans
> win over plain index scans, in general, is because you make one pass
> through the heap to get all the rows you need instead of bouncing
> around doing lots of random access.  But of course if all the data is
> in RAM then this argument falls down.
>
> If these aren't enough to get the query planner to DTRT, then the OP
> might want to try lowering them further and seeing how low he has to
> go to flip the plan...
>

So if this query usually does *not* hit the cache, it will be probably
faster if I leave it like that? While testing a query I execute it that much
that it's always getting into the cache. However, since other applications
run on the same server, I think that infrequently used data gets flushed
after a while, even if the DB could fit in the RAM.

-- 
Mathieu

--00148531b663b1c82f047cdf6f73
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div class=3D"gmail_quote">On Mon, Jan 11, 2010 at 3:52 AM, Robert Haas <sp=
an dir=3D"ltr">&lt;<a href=3D"mailto:robertmhaas@gmail.com">robertmhaas@gma=
il.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"=
border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; paddi=
ng-left: 1ex;">
On Sun, Jan 10, 2010 at 10:53 AM, Kevin Grittner<br>
<div class=3D"im">&lt;<a href=3D"mailto:Kevin.Grittner@wicourts.gov">Kevin.=
Grittner@wicourts.gov</a>&gt; wrote:<br>
</div><div class=3D"im">&gt; seq_page_cost =3D 0.1<br>
&gt; random_page_cost =3D 0.1<br>
<br>
</div>These might not even be low enough. =A0The reason why bitmap index sc=
ans<br>
win over plain index scans, in general, is because you make one pass<br>
through the heap to get all the rows you need instead of bouncing<br>
around doing lots of random access. =A0But of course if all the data is<br>
in RAM then this argument falls down.<br>
<br>
If these aren&#39;t enough to get the query planner to DTRT, then the OP<br=
>
might want to try lowering them further and seeing how low he has to<br>
go to flip the plan...<br></blockquote><div>=A0<br>So if this query usually=
 does *not* hit the cache, it will be probably faster if I leave it like th=
at? While testing a query I execute it that much that it&#39;s always getti=
ng into the cache. However, since other applications run on the same server=
, I think that infrequently used data gets flushed after a while, even if t=
he DB could fit in the RAM.<br>
<br>-- <br>Mathieu<br></div></div>

--00148531b663b1c82f047cdf6f73--