Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id DD2FE64FC15
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 24 Sep 2008 01:07:15 -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 06286-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 24 Sep 2008 01:07:13 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from wf-out-1314.google.com (wf-out-1314.google.com
 [209.85.200.175])
	by postgresql.org (Postfix) with ESMTP id E3ADD64FC04
	for <pgsql-performance@postgresql.org>;
	Wed, 24 Sep 2008 01:07:12 -0300 (ADT)
Received: by wf-out-1314.google.com with SMTP id 25so2633366wfc.28
	for <pgsql-performance@postgresql.org>;
	Tue, 23 Sep 2008 21:07:11 -0700 (PDT)
Received: by 10.142.251.9 with SMTP id y9mr2342343wfh.46.1222229231262;
	Tue, 23 Sep 2008 21:07:11 -0700 (PDT)
Received: by 10.143.39.19 with HTTP; Tue, 23 Sep 2008 21:07:11 -0700 (PDT)
Message-ID: <a1ec7d000809232107v310ca29cy28138f527947c30d@mail.gmail.com>
Date: Tue, 23 Sep 2008 21:07:11 -0700
From: "Scott Carey" <scott@richrelevance.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: Chaotically weird execution plan
Cc: Einars <einars@gmail.com>, pgsql-performance@postgresql.org
In-Reply-To: <17587.1222225090@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_137548_18567867.1222229231226"
References: <4f8bd5c90809231753xbcdacd2xfba4d38c2eb34faf@mail.gmail.com>
	<17587.1222225090@sss.pgh.pa.us>
X-Google-Sender-Auth: ef4c795d38c2b59c
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/211
X-Sequence-Number: 31524

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

Einars, you may be able to force a query plan similar to the first one, on
the second data set, if you decrease the random page cost or disable bitmap
scans.  If a regular index scan is faster than the bitmap scan with the same
query returning the same results, there may be some benefit that can be
gained with tuning further.  But it isn't likely and will depend on how
likely the pages are to be in RAM versus disk.  If this is a big problem for
you, it may be worth trying however.



On Tue, Sep 23, 2008 at 7:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Einars <einars@gmail.com> writes:
> > When displaying information about information about an user in our
> > site, I noticed an unreasonable slowdown. The culprit turned out to be
> > a trivial select, which determines the number of comments left by an
> > user:
>
> I don't see anything even slightly wrong here.  Your first example pulls
> 15888 rows from the table in 13134 ms.  Your second example pulls 3
> rows from the table in 0.121 ms --- which I rather imagine is because
> those three rows are already in cache; it would take a lot longer if it
> actually had to go to disk several times.
>
> The planner's rowcount estimates are on target and it appears to be
> choosing appropriate plans in each case.  It just takes longer to
> process 5000 times as many rows ...
>
>                        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_137548_18567867.1222229231226
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div dir="ltr"><br>
Einars, you may be able to force a query plan similar to the first one, on the second data set, if you decrease the random page cost or disable bitmap scans.&nbsp; If a regular index scan is faster than the bitmap scan with the same query returning the same results, there may be some benefit that can be gained with tuning further.&nbsp; But it isn&#39;t likely and will depend on how likely the pages are to be in RAM versus disk.&nbsp; If this is a big problem for you, it may be worth trying however.<br>
<br><br><br><div class="gmail_quote">On Tue, Sep 23, 2008 at 7:58 PM, 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">Einars &lt;<a href="mailto:einars@gmail.com">einars@gmail.com</a>&gt; writes:<br>
&gt; When displaying information about information about an user in our<br>
&gt; site, I noticed an unreasonable slowdown. The culprit turned out to be<br>
&gt; a trivial select, which determines the number of comments left by an<br>
&gt; user:<br>
<br>
</div>I don&#39;t see anything even slightly wrong here. &nbsp;Your first example pulls<br>
15888 rows from the table in 13134 ms. &nbsp;Your second example pulls 3<br>
rows from the table in 0.121 ms --- which I rather imagine is because<br>
those three rows are already in cache; it would take a lot longer if it<br>
actually had to go to disk several times.<br>
<br>
The planner&#39;s rowcount estimates are on target and it appears to be<br>
choosing appropriate plans in each case. &nbsp;It just takes longer to<br>
process 5000 times as many rows ...<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_137548_18567867.1222229231226--