Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 54C9019F3D3
	for <pgsql-performance@postgresql.org>;
	Mon, 11 Jun 2012 20:54:14 -0300 (ADT)
Received: from mail-gh0-f174.google.com ([209.85.160.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <pgsql@j-davis.com>) id 1SeER3-0005PW-Di
	for pgsql-performance@postgresql.org; Mon, 11 Jun 2012 23:54:13 +0000
Received: by ghrr11 with SMTP id r11so2997833ghr.19
	for <pgsql-performance@postgresql.org>;
	Mon, 11 Jun 2012 16:54:00 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=subject:from:to:cc:in-reply-to:references:content-type:date
	:message-id:mime-version:x-mailer:content-transfer-encoding
	:x-gm-message-state;
	bh=FimGxfLWmc4/oq+sv3Et+TGnnG8XVIwAhCbp+TO1upI=;
	b=RvR0Le+lOYWWpy4yfHdYvhhrxJrw/cpg/LTvCBGeX4ispu1S5R5VZudrjQu6oF0t7B
	QLIp7QjAuHGziOlYUoy++knkTsLM2MufCpIIZ1cb9S8PRbQmlTotee0yQIqTC91DAwIU
	JNiIogfcWr+f3CImrRbi077w0eY2Afd6j/gjSEayFGgkYsY7OKaauUpPEAA/rsCK0Try
	hyzwsIolTLtWHoVvsEfRIHf9/e9/ylLzy7II8EczshsjaCGDRVY14hCQaHc3TP9nQQS4
	bMzKrpX19ZHo7V0C/nCjfPKng8nvBKxqKN1WibXyHRi1EJPJdvOMayC3iLIIamcBbem8
	+CKQ==
Received: by 10.236.75.196 with SMTP id z44mr12025858yhd.40.1339458840605;
	Mon, 11 Jun 2012 16:54:00 -0700 (PDT)
Received: from ?IPv6:::1? ([2600:3c01::f03c:91ff:fe93:2ae4])
	by mx.google.com with ESMTPS id p14sm28332824ani.8.2012.06.11.16.53.58
	(version=SSLv3 cipher=OTHER); Mon, 11 Jun 2012 16:53:59 -0700 (PDT)
Subject: Re: Performance of CLUSTER
From: Jeff Davis <pgsql@j-davis.com>
To: sthomas@optionshouse.com
Cc: Mark Thornton <mthornton@optrak.com>, pgsql-performance@postgresql.org
In-Reply-To: <4FD5F5DC.3080507@optionshouse.com>
References: <4FD458CA.3070902@optrak.com> <4FD5F5DC.3080507@optionshouse.com>
Content-Type: text/plain; charset="ISO-8859-1"
Date: Mon, 11 Jun 2012 16:53:57 -0700
Message-ID: <1339458837.12042.9.camel@sussancws0025>
Mime-Version: 1.0
X-Mailer: Evolution 2.28.3
Content-Transfer-Encoding: 7bit
X-Gm-Message-State: 
 ALoCoQmb7VIikaJUnZWICvGXd4X1SGNtpFuLWPcBy7pd53bDFYYyILiIvPNGChw6lbH44zLYkqD3
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201206/67
X-Sequence-Number: 47093

On Mon, 2012-06-11 at 08:42 -0500, Shaun Thomas wrote:
> On 06/10/2012 03:20 AM, Mark Thornton wrote:
> 
> > 4. 3980922 rows, 1167MB, 276s
> > 5. 31843368 rows, 9709MB, ~ 10 hours
> 
> Just judging based on the difference between these two, it would appear 
> to be from a lot of temp space thrashing. An order of magnitude more 
> rows shouldn't take over 100x longer to cluster, even with GIST. What's 
> your maintenance_work_mem setting?

GiST can have a large impact depending on all kinds of factors,
including data distribution.

9.2 contains some important improvements in GiST build times for cases
where the index doesn't fit in memory. Mark, can you please try your
experiments on the 9.2beta and tell us whether that helps you?

Regards,
	Jeff Davis