Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 22E579FB5D7 for ; Tue, 5 Jun 2007 18:59:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 30357-10 for ; Tue, 5 Jun 2007 18:59:33 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 8639A9FB518 for ; Tue, 5 Jun 2007 18:59:33 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l55LxPwW010526; Tue, 5 Jun 2007 17:59:25 -0400 (EDT) To: david@lang.hm cc: Gregory Stark , Scott Marlowe , Thomas Andrews , Mark Lewis , pgsql-performance@postgresql.org Subject: Re: Thousands of tables versus on table? In-reply-to: References: <1180980943.31471.322.camel@archimedes> <46645DD9.7030805@soliantconsulting.com> <877iqjmro5.fsf@oxford.xeocode.com> <46647427.4030103@g2switchworks.com> <466593E5.1010904@g2switchworks.com> <87sl96z0wf.fsf@oxford.xeocode.com> Comments: In-reply-to david@lang.hm message dated "Tue, 05 Jun 2007 13:33:23 -0700" Date: Tue, 05 Jun 2007 17:59:25 -0400 Message-ID: <10525.1181080765@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/81 X-Sequence-Number: 25114 david@lang.hm writes: > however I really don't understand why it is more efficiant to have a 5B > line table that you do a report/query against 0.1% of then it is to have > 1000 different tables of 5M lines each and do a report/query against 100% > of. Essentially what you are doing when you do that is taking the top few levels of the index out of the database and putting it into the filesystem; plus creating duplicative indexing information in the database's system catalogs. The degree to which this is a win is *highly* debatable, and certainly depends on a whole lot of assumptions about filesystem performance. You also need to assume that constraint-exclusion in the planner is pretty doggone cheap relative to the table searches, which means it almost certainly will lose badly if you carry the subdivision out to the extent that the individual tables become small. (This last could be improved in some cases if we had a more explicit representation of partitioning, but it'll never be as cheap as one more level of index search.) I think the main argument for partitioning is when you are interested in being able to drop whole partitions cheaply. regards, tom lane