From: Tom Lane <tgl@sss.pgh.pa.us>
To: david@lang.hm
Cc: Gregory Stark <stark@enterprisedb.com>
Cc: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: Thomas Andrews <tandrews@soliantconsulting.com>
Cc: Mark Lewis <mark.lewis@mir3.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Thousands of tables versus on table?
Date: Tue, 05 Jun 2007 17:59:25 -0400
Message-ID: <10525.1181080765@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.64.0706051325500.24361@asgard.lang.hm>
References: <C289C6B1.A63%tandrews@soliantconsulting.com>
<1180980943.31471.322.camel@archimedes>
<46645DD9.7030805@soliantconsulting.com>
<877iqjmro5.fsf@oxford.xeocode.com>
<46647427.4030103@g2switchworks.com>
<Pine.LNX.4.64.0706041631490.11737@asgard.lang.hm>
<466593E5.1010904@g2switchworks.com>
<87sl96z0wf.fsf@oxford.xeocode.com>
<Pine.LNX.4.64.0706051325500.24361@asgard.lang.hm>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, david@lang.hm, stark@enterprisedb.com, smarlowe@g2switchworks.com, tandrews@soliantconsulting.com, mark.lewis@mir3.com
Subject: Re: Thousands of tables versus on table?
In-Reply-To: <10525.1181080765@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox