Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 406654772FF for ; Thu, 30 Jan 2003 13:02:44 -0500 (EST) Received: from mail.libertyrms.com (unknown [209.167.124.227]) by postgresql.org (Postfix) with ESMTP id 8D3254770B4 for ; Thu, 30 Jan 2003 13:02:40 -0500 (EST) Received: from andrew by mail.libertyrms.com with local (Exim 3.22 #3 (Debian)) id 18eJ1M-0003CI-00 for ; Thu, 30 Jan 2003 13:02:40 -0500 Date: Thu, 30 Jan 2003 13:02:40 -0500 From: Andrew Sullivan To: pgsql-performance@postgresql.org Subject: Re: One large v. many small Message-ID: <20030130130240.I983@mail.libertyrms.com> Mail-Followup-To: Andrew Sullivan , pgsql-performance@postgresql.org References: <1A107C56-3479-11D7-A3A3-000393AA8F3C@allresearch.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <1A107C56-3479-11D7-A3A3-000393AA8F3C@allresearch.com>; from noah@allresearch.com on Thu, Jan 30, 2003 at 12:34:36PM -0500 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/391 X-Sequence-Number: 1038 On Thu, Jan 30, 2003 at 12:34:36PM -0500, Noah Silverman wrote: > Select (*) from client_4 where foo=2; > > A query from the new, proposed system would be > > Select (*) from big_results where client=4 and foo=2. > > The big questions is, WHICH WILL BE FASTER with Postgres. Is there any > performance improvement or cost to switching to this new structure. Faster overall, or faster for that operation? I can't prove it, but I suspect that the first one will return faster just because both the index and the table itself is smaller. The possibility is thatit will cause you problems overall, however, because of the large number of files you have to keep if you use 3000 tables. This is dependent on your filesytem (and its implementation). Note, too, that a lot of transactions frequently updating the table might make a difference. A large number of dead tuples sitting on a 10 million row table will make anything crawl. A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada M2P 2A8 +1 416 646 3304 x110