Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 2F97963396D
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 15:46:16 -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 75553-02-7
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 18:46:01 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-pv0-f174.google.com (mail-pv0-f174.google.com
	[74.125.83.174])
	by mail.postgresql.org (Postfix) with ESMTP id C7313632613
	for <pgsql-performance@postgresql.org>;
	Wed, 31 Mar 2010 15:44:45 -0300 (ADT)
Received: by pva18 with SMTP id 18so6339pva.19
	for <pgsql-performance@postgresql.org>;
	Wed, 31 Mar 2010 11:44:44 -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=zsrGSlgyoispVWGvNlUjcM7OzSTUGJdap6nnY3SqxgM=;
	b=Yjc5lKMdLcNYTfCrM1fXkfpqByuba9Hz7E1JGlrgj6Ix+LoPUUEC7C0t8FhgMa9Jq6
	WZi0ajheEfAb1t68YY2Fo3KdJj0g04agcjHdyMayOZHmvghZ8dOUKDvJ29G6r4foa9IW
	vKzl/QFB/wkoU1kLnMu7ECJRTYNDT9Q7CXK+U=
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=j27iknKdmk6iymcNrKMMtgo/oacr2RF80xcXvVHez1JwurnhJdr5b0Ng6aeR6mHa8X
	RAVkjItX8pZuQUKGA6U14KbpjR677Iy4tG4UN8RMxvT15da7Q+QzzNQKj0GfZJFthp3f
	0m2lfbYq/AU6NhjtFEH800rVcjFUamZtYR+F4=
MIME-Version: 1.0
Received: by 10.231.204.143 with HTTP; Wed, 31 Mar 2010 11:44:44 -0700 (PDT)
In-Reply-To: <4BB3902D.7010205@ca.com>
References: <4BB3902D.7010205@ca.com>
Date: Wed, 31 Mar 2010 14:44:44 -0400
Received: by 10.142.209.12 with SMTP id h12mr1104310wfg.104.1270061084280;
	Wed, 31 Mar 2010 11:44:44 -0700 (PDT)
Message-ID: <e7f9235d1003311144i5cf86e6cw67cc6540ec6cd6f8@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=000e0cd32e12c587ea04831d231a
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.184 tagged_above=-10 required=5
	tests=BAYES_40=-0.185, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201003/411
X-Sequence-Number: 38236

--000e0cd32e12c587ea04831d231a
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Mar 31, 2010 at 2:10 PM, Brian Cox <brian.cox@ca.com> wrote:

>
>
> so the question still remains: why did it take > 20 mins? To see if it was
> due to autovacuum running ANALYZE, I turned off autovacuum, created a table
> using SELECT * INTO temp FROM ts_stats_tranunit_user_daily, added the index
> on ts_interval_start_time and ran the prepared query with temp, but the
> query completed in a few secs.
>
> It's possible that statistics were updated between the >20 minute run and
your most recent prepared query test. In fact, comparing the plans between
your two emails, it's quite likely, as even the non-prepared versions are
not producing the same plan or the same estimates; it's therefore possible
that your problem has already corrected itself if you're unable to duplicate
the 20 minute behaviour at this point.

Taking a look at the statistics accuracy with an explain analyze might still
be informative, however.

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

--000e0cd32e12c587ea04831d231a
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 2:10 PM, Brian C=
ox <span dir=3D"ltr">&lt;<a href=3D"mailto:brian.cox@ca.com">brian.cox@ca.c=
om</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"marg=
in: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-l=
eft: 1ex;">
<br>
<br>
so the question still remains: why did it take &gt; 20 mins? To see if it w=
as due to autovacuum running ANALYZE, I turned off autovacuum, created a ta=
ble using SELECT * INTO temp FROM ts_stats_tranunit_user_daily, added the i=
ndex on ts_interval_start_time and ran the prepared query with temp, but th=
e query completed in a few secs.<br>
<font color=3D"#888888">
</font><br></blockquote></div>It&#39;s possible that statistics were update=
d between the &gt;20 minute run and your most recent prepared query test. I=
n fact, comparing the plans between your two emails, it&#39;s quite likely,=
 as even the non-prepared versions are not producing the same plan or the s=
ame estimates; it&#39;s therefore possible that your problem has already co=
rrected itself if you&#39;re unable to duplicate the 20 minute behaviour at=
 this point.<br>
<br>Taking a look at the statistics accuracy with an explain analyze might =
still be informative, however.<br clear=3D"all"><br>-- <br>- David T. Wilso=
n<br><a href=3D"mailto:david.t.wilson@gmail.com">david.t.wilson@gmail.com</=
a><br>


--000e0cd32e12c587ea04831d231a--