From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: david@lang.hm, Gregory Stark <stark@enterprisedb.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: Wed, 06 Jun 2007 10:17:29 -0500
Message-ID: <4666D009.9030505@g2switchworks.com> (raw)
In-Reply-To: <10525.1181080765@sss.pgh.pa.us>
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>
<10525.1181080765@sss.pgh.pa.us>
Tom Lane wrote:
> 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 did some testing a while back on some of this, and with 400 or so
partitions, the select time was still very fast.
We were testing grabbing 50-80k rows from 12M at a time, all adjacent to
each other. With the one big table and one big two way index method, we
were getting linearly increasing select times as the dataset grew larger
and larger. The indexes were much larger than available memory and
shared buffers. The retrieval time for 50-80k rows was on the order of
2 to 6 seconds, while the retrieval time for the same number of rows
with 400 partitions was about 0.2 to 0.5 seconds.
I haven't tested with more partitions than that, but might if I get a
chance. What was really slow was the inserts since I was using rules at
the time. I'd like to try re-writing it to use triggers, since I would
then have one trigger on the parent table instead of 400 rules. Or I
could imbed the rules into the app that was creating / inserting the
data. The insert performance dropped off VERY fast as I went over 100
rules, and that was what primarily stopped me from testing larger
numbers of partitions.
The select performance stayed very fast with more partitions, so I'm
guessing that the constraint exclusion is pretty well optimized.
I'll play with it some more when I get a chance. For certain operations
like the one we were testing, partitioning seems to pay off big time.
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: smarlowe@g2switchworks.com, tgl@sss.pgh.pa.us, stark@enterprisedb.com, tandrews@soliantconsulting.com, mark.lewis@mir3.com
Subject: Re: Thousands of tables versus on table?
In-Reply-To: <4666D009.9030505@g2switchworks.com>
* 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