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 EA967D1B4E9 for ; Thu, 9 Oct 2003 17:20:04 +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 77301-08 for ; Thu, 9 Oct 2003 14:19:15 -0300 (ADT) Received: from fuji.krosing.net (217-159-136-226-dsl.kt.estpak.ee [217.159.136.226]) by svr1.postgresql.org (Postfix) with ESMTP id 7A1E4D1B545 for ; Thu, 9 Oct 2003 14:19:13 -0300 (ADT) Received: from fuji.krosing.net (localhost.localdomain [127.0.0.1]) by fuji.krosing.net (8.12.8/8.12.8) with ESMTP id h99HGk6N002573; Thu, 9 Oct 2003 20:16:46 +0300 Received: (from hannu@localhost) by fuji.krosing.net (8.12.8/8.12.8/Submit) id h99HGNYk002571; Thu, 9 Oct 2003 20:16:23 +0300 X-Authentication-Warning: fuji.krosing.net: hannu set sender to hannu@tm.ee using -f Subject: Re: Compare rows From: Hannu Krosing To: Josh Berkus Cc: Christopher Browne , pgsql-performance@postgresql.org In-Reply-To: <200310082236.59305.josh@agliodbs.com> References: <3F841A28.8060504@cranel.com> <200310081611.59179.josh@agliodbs.com> <200310082236.59305.josh@agliodbs.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1065719782.2529.7.camel@fuji.krosing.net> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Thu, 09 Oct 2003 20:16:22 +0300 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/287 X-Sequence-Number: 4035 Josh Berkus kirjutas N, 09.10.2003 kell 08:36: > Chris, > > The need to do a lot of joins would likely hurt performance somewhat, > > as well as the way that it greatly increases the number of rows. > > Although you could always split it into several tables, one for each > > "value_type", and UNION them into a view... > > It increases the number of rows, yes, but *decreases* the storage size of data > by eliminating thousands ... or millions ... of NULL fields. I'm not sure I buy that. Null fields take exactly 1 *bit* to store (or more exactly, if you have any null fields in tuple then one 32bit int for each 32 fields is used for NULL bitmap), whereas the same fields in "vertical" table takes 4 bytes for primary key and 1-4 bytes for category key + tuple header per value + neccessary indexes. So if you have more than one non-null field per tuple you will certainly lose in storage. > How would splitting the vertical values into dozens of seperate tables help things? If you put each category in a separate table you save 1-4 bytes for category per value, but still store primary key and tuple header *per value*. Jou may stii get better performance for single-column comparisons as fewer pages must be touched. > Personally, I'd rather have a table with 3 columns and 8 million rows than a > table with 642 columns and 100,000 rows. Much easier to deal with. Same here ;) ------------------ Hannu