Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id A9D729FB78C for ; Mon, 23 Apr 2007 06:47:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 89653-05 for ; Mon, 23 Apr 2007 06:46:58 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ik-out-1112.google.com (ik-out-1112.google.com [66.249.90.181]) by postgresql.org (Postfix) with ESMTP id EFF489FB783 for ; Mon, 23 Apr 2007 06:47:01 -0300 (ADT) Received: by ik-out-1112.google.com with SMTP id c30so575675ika for ; Mon, 23 Apr 2007 02:47:00 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=csh2daJ8vaUIdquSJJ9GXkc5ViCnZm09m4T+psUEC658EmKA2wQWrCitcNGGaKaS0uj+pISyH88DllYadDkKXnGoC8hREyYL/TOt+j4lrjfv9vwV4bULBYsgUF8Km9AbyxmHiM1S4P0Z0+mDqcMrxrXyR6oC5KVMInkPfgCvark= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=BtlS4FqEw+ArQlVhE2gcPZrj9fbl4xiFqeFDlGLtO4rPvqnLMvL9xO3Na3+TjIj+goXN3wmnRCudCok/LZfT9nVdEZVUPMy4kVLgPuNcDUB4h3ATnxRHmKY3qCD0UM9Y7OAEUwEqjCofwgqx52Jx+8bCahSLcZ7Ota4MjWEK4L4= Received: by 10.82.113.6 with SMTP id l6mr8676426buc.1177321620164; Mon, 23 Apr 2007 02:47:00 -0700 (PDT) Received: from ?172.24.32.17? ( [62.232.55.118]) by mx.google.com with ESMTP id e23sm11698369ugd.2007.04.23.02.46.56; Mon, 23 Apr 2007 02:46:57 -0700 (PDT) Message-ID: <462C8085.3020602@enterprisedb.com> Date: Mon, 23 Apr 2007 10:46:45 +0100 From: Heikki Linnakangas User-Agent: Icedove 1.5.0.8 (X11/20061116) MIME-Version: 1.0 To: Nelson Kotowski CC: pgsql-performance@postgresql.org Subject: Re: TPC-H Scaling Factors X PostgreSQL Cluster Command References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/388 X-Sequence-Number: 24265 Nelson Kotowski wrote: > So far, i need to do it in three different scale factors (1, 2 and 5GB > databases). > > My build process comprehends creating the tables without any foreign keys, > indexes, etc. - Running OK! > Then, i load the data from the flat files generated through DBGEN software > into these tables. - Running OK! > > Finally, i run a "optimize" script that does the following: > > - Alter the tables to add the mandatory foreign keys; > - Create all mandatory indexes; > - Cluster the orders table by the orders table index; > - Cluster the lineitem table by the lineitem table index; > - Vacuum the database; > - Analyze statistics. Cluster will completely rewrite the table and indexes. On step 2, you should only create the indexes you're clustering on, and create the rest of them after clustering. Or even better, generate and load the data in the right order to start with, so you don't need to cluster at all. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com