Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id DA55F2E0056 for ; Thu, 27 Mar 2008 13:54:07 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 61274-04 for ; Thu, 27 Mar 2008 13:53:49 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from h07.llord.com (unknown [195.140.142.194]) by postgresql.org (Postfix) with ESMTP id 1F3BC2E0053 for ; Thu, 27 Mar 2008 13:53:51 -0300 (ADT) Received: from par69-8-88-161-102-87.fbx.proxad.net ([88.161.102.87] helo=apollo13.peufeu.com) by h07.llord.com with esmtpa (Exim 4.68) (envelope-from ) id 1JevMK-00073U-Oi; Thu, 27 Mar 2008 17:53:48 +0100 Date: Thu, 27 Mar 2008 17:57:06 +0100 To: "Jesper Krogh" , pgsql-performance@postgresql.org Subject: Re: "Slow" query or just "Bad hardware"? From: PFC Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 References: <17799.195.41.66.226.1206632068.squirrel@mail.jabbernet.dk> Content-Transfer-Encoding: 7bit Message-ID: In-Reply-To: <17799.195.41.66.226.1206632068.squirrel@mail.jabbernet.dk> User-Agent: Opera Mail/9.24 (Linux) X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - h07.llord.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - peufeu.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.132 tagged_above=0 required=5 tests=AWL=1.032, RDNS_NONE=0.1 X-Spam-Level: * X-Archive-Number: 200803/504 X-Sequence-Number: 29458 Also, sometimes offine TOASTing is evil : Say you have a forum, you want the posts table to be CLUSTER'ed on (topic_id, post_id) so displaying 1 page with 30 posts on it uses 1 seek, not 30 seeks. But CLUSTER doesn't touch the data that has been pushed offline in the toast table. So, in that case, it can pay (big time actually) to disable toasting, store the data inline, and benefit from cluster. So basically : Data that is seldom used or used only in queries returning/examining 1 row bu otherwise eats cache -> push it away (toast or partition) Data that is used very often in queries that return/examine lots of rows, especially if said rows are in sequence (cluster...) -> keep it inline