X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id C56F0D1C9EC for ; Fri, 1 Aug 2003 19:19:21 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 65981-02 for ; Fri, 1 Aug 2003 16:18:14 -0300 (ADT) Received: from lakemtao03.cox.net (lakemtao03.cox.net [68.1.17.242]) by svr1.postgresql.org (Postfix) with ESMTP id DAB0DD1C9EE for ; Fri, 1 Aug 2003 16:19:19 -0300 (ADT) Received: from [192.168.1.1] ([68.11.66.83]) by lakemtao03.cox.net (InterMail vM.5.01.04.05 201-253-122-122-105-20011231) with ESMTP id <20030801191922.GYEW23518.lakemtao03.cox.net@[192.168.1.1]> for ; Fri, 1 Aug 2003 15:19:22 -0400 Subject: Re: How number of columns affects performance From: Ron Johnson To: pgsql-performance@postgresql.org In-Reply-To: <200308011044.03493.josh@agliodbs.com> References: <20030801115257.I92555@zoraida.natserv.net> <1059755687.7508.638.camel@haggis> <20030801130507.M93127@zoraida.natserv.net> <200308011044.03493.josh@agliodbs.com> Content-Type: text/plain Message-Id: <1059765560.22384.67.camel@haggis> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.3 Date: 01 Aug 2003 14:19:20 -0500 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/14 X-Sequence-Number: 2831 On Fri, 2003-08-01 at 12:44, Josh Berkus wrote: > Francisco, > > > Yes all fields belong to the same entity. I used 100 as an example it may > > be something like 60 to 80 fields (there are two tables in question). I > > don't formally do 3rd normal form, but for the most part I do most of > > the general concepts of normalization. > > > > > If not, then good design says to split the table. > > Actually, no, it doesn't. If all 60-80 fields are unitary and required > characteristics of the row-entity, normalization says keep them in one table. You snipped out too much, because that's exactly what I said... Another way of writing it: only split the table if some of the fields are not unitary to the entity. > The only time NF would recommend splitting the table is for fields which are > frequenly NULL for reasons other than missing data entry. For those, you'd > create a child table. Although while this is good 4NF, it's impractical in > PostgreSQL, where queries with several LEFT OUTER JOINs tend to be very slow > indeed. Good to know. > My attitude toward these normalization vs. performance issues is consistenly > the same: First, verify that you have a problem. That is, build the > database with everything in one table (or with child tables for Nullable > fields, as above) and try to run your application. If performance is > appalling, *then* take denormalization steps to improve it. The OP was not talking about denormalizing ... It was: will vertically partitioning a table increase performance. And the answer is "sometimes", -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+