Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 031F5632FAB
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 01:37:52 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 44610-06
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 04:37:40 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-yw0-f184.google.com (mail-yw0-f184.google.com
	[209.85.211.184])
	by mail.postgresql.org (Postfix) with ESMTP id 551EA632815
	for <pgsql-performance@postgresql.org>;
	Wed, 31 Mar 2010 01:37:41 -0300 (ADT)
Received: by ywh14 with SMTP id 14so426150ywh.15
	for <pgsql-performance@postgresql.org>;
	Tue, 30 Mar 2010 21:37:40 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:received:message-id:subject:from:to:cc:content-type;
	bh=dAqdxk5EmI3RN3Yt4PFr55g3p8oNB//eQmJ/MvoNtlA=;
	b=nh5Z3xEPLR+Ln7GUTrgyi7Hx667IeJsfESJReHhTC1noT2nAxXlS8qeHQN6fB+97GJ
	rEhUEG7Qeb5MsFkc0V9WJLJZFBcdoOap6BmTnLOoBBHq0saZee6YozUAxRbicjRxqfmE
	AZEglVMyHeyQSs8BBZYM8KbSvHLs6VkMqjsP0=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=JoeZ5NCDpzceab2Uyoxl+KqYIdlRmEBNm16zwdiLaDMkC6nwxgzAWAem6FnsOef1AM
	T2XGagxwUWTtQmiq2kO+UtHRC0EvEewJYigQ1f98eArnVDUTXnjvgflghm4IC1yV5udi
	vSv+hMpDGgKoBWQZSf4fVQvH6ctbTJIwV/NDM=
MIME-Version: 1.0
Received: by 10.90.100.11 with HTTP; Tue, 30 Mar 2010 21:37:40 -0700 (PDT)
In-Reply-To: <4BB2CB78.1020409@ca.com>
References: <4BB2CB78.1020409@ca.com>
Date: Wed, 31 Mar 2010 00:37:40 -0400
Received: by 10.90.2.4 with SMTP id 4mr1615191agb.14.1270010260446; Tue, 30
	Mar 2010 21:37:40 -0700 (PDT)
Message-ID: <x2oe7f9235d1003302137p2f41110bu97dd919cdd4772b@mail.gmail.com>
Subject: Re: query has huge variance in execution times
From: David Wilson <david.t.wilson@gmail.com>
To: Brian Cox <brian.cox@ca.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary=001636284ca26f5e850483114e15
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.391 tagged_above=-10 required=5 tests=AWL=-1.207,
	BAYES_40=-0.185, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201003/402
X-Sequence-Number: 38227

--001636284ca26f5e850483114e15
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Mar 31, 2010 at 12:11 AM, Brian Cox <brian.cox@ca.com> wrote:

>
>  2010-03-30 18:41:11.685261-07 | select b.ts_id from
> ts_stats_tranunit_user_daily b, ts_stats_tranunit_user_interval c where
> b.ts_transet_incarnation_id = c.ts_transet_incarnation_id and
> b.ts_tranunit_id = c.ts_tranunit_id and b.ts_user_incarnation_id =
> c.ts_user_incarnation_id and c.ts_interval_start_time >= $1 and
> c.ts_interval_start_time < $2 and b.ts_interval_start_time >= $3 and
> b.ts_interval_start_time < $4
> (1 row)
>
> about 5 mins later, I, suspecting problems, do (the values are the same as
> for $1 et al above; EXPLAIN was done on purpose to keep stats [hopefully]
> the same as when pid 10022 started; there are 80,000 rows in each of the 2
> tables at the time of this EXPLAIN and when 10022 started):
>
> cemdb=> explain select b.ts_id from ts_stats_tranunit_user_daily b,
> ts_stats_tranunit_user_interval c where b.ts_transet_incarnation_id =
> c.ts_transet_incarnation_id and b.ts_tranunit_id = c.ts_tranunit_id and
> b.ts_user_incarnation_id = c.ts_user_incarnation_id and
> c.ts_interval_start_time >= '2010-3-29 01:00' and c.ts_interval_start_time <
> '2010-3-29 02:00' and b.ts_interval_start_time >= '2010-3-29' and
> b.ts_interval_start_time < '2010-3-30';
>
>
These won't necessarily get the same plan. If you want to see what plan the
prepared query is getting, you'll need to prepare it ("prepare foo as
<query>") and then explain *that* via "explain execute foo".

The prepared version likely has a much more generic plan, whereas the
regular query gets optimized for the actual values provided.


-- 
- David T. Wilson
david.t.wilson@gmail.com

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

<br><br><div class=3D"gmail_quote">On Wed, Mar 31, 2010 at 12:11 AM, Brian =
Cox <span dir=3D"ltr">&lt;<a href=3D"mailto:brian.cox@ca.com">brian.cox@ca.=
com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"mar=
gin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-=
left: 1ex;">
<br>
=A02010-03-30 18:41:11.685261-07 | select b.ts_id from ts_stats_tranunit_us=
er_daily b, ts_stats_tranunit_user_interval c where b.ts_transet_incarnatio=
n_id =3D c.ts_transet_incarnation_id and b.ts_tranunit_id =3D c.ts_tranunit=
_id and b.ts_user_incarnation_id =3D c.ts_user_incarnation_id and c.ts_inte=
rval_start_time &gt;=3D $1 and c.ts_interval_start_time &lt; $2 and b.ts_in=
terval_start_time &gt;=3D $3 and b.ts_interval_start_time &lt; $4<br>

(1 row)<br>
<br>
about 5 mins later, I, suspecting problems, do (the values are the same as =
for $1 et al above; EXPLAIN was done on purpose to keep stats [hopefully] t=
he same as when pid 10022 started; there are 80,000 rows in each of the 2 t=
ables at the time of this EXPLAIN and when 10022 started):<br>

<br>
cemdb=3D&gt; explain select b.ts_id from ts_stats_tranunit_user_daily b, ts=
_stats_tranunit_user_interval c where b.ts_transet_incarnation_id =3D c.ts_=
transet_incarnation_id and b.ts_tranunit_id =3D c.ts_tranunit_id and b.ts_u=
ser_incarnation_id =3D c.ts_user_incarnation_id and c.ts_interval_start_tim=
e &gt;=3D &#39;2010-3-29 01:00&#39; and c.ts_interval_start_time &lt; &#39;=
2010-3-29 02:00&#39; and b.ts_interval_start_time &gt;=3D &#39;2010-3-29&#3=
9; and b.ts_interval_start_time &lt; &#39;2010-3-30&#39;;<br>
<br></blockquote><div><br>These won&#39;t necessarily get the same plan. If=
 you want to see what plan the prepared query is getting, you&#39;ll need t=
o prepare it (&quot;prepare foo as &lt;query&gt;&quot;) and then explain *t=
hat* via &quot;explain execute foo&quot;. <br>
<br>The prepared version likely has a much more generic plan, whereas the r=
egular query gets optimized for the actual values provided.<br></div></div>=
<br clear=3D"all"><br>-- <br>- David T. Wilson<br><a href=3D"mailto:david.t=
.wilson@gmail.com">david.t.wilson@gmail.com</a><br>


--001636284ca26f5e850483114e15--