From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Cc: Stacy White <harsh@computer.org>
Subject: Re: Partitioned table performance
Date: Sun, 5 Dec 2004 15:06:40 -0800
Message-ID: <200412051506.41025.josh@agliodbs.com> (raw)
In-Reply-To: <000a01c4da74$84e072e0$0200a8c0@grownups>
References: <000a01c4da74$84e072e0$0200a8c0@grownups>
Stacy,
Thanks for the stats!
> In some cases we've seen some increased performance in tests by splitting
> the table into several smaller tables. Both 'UNION ALL' views, and the
> superclass/subclass scheme work well at pruning down the set of rows a
> query uses, but they seem to introduce a large performance hit to the time
> to process each row (~50% for superclass/subclass, and ~150% for union
> views).
This seems reasonable, actually, given your test. Really, what you should be
comparing it against is not against selecting from an individual partition,
but selecting from the whole business as one large table.
I also suspect that wider rows results in less overhead proportionally; note
that your test contains *only* the indexed rows. I should soon have a test
to prove this, hopefully.
However, I would be interested in seeing EXPLAIN ANALYZE from your tests
rather than just EXPLAIN.
--
Josh Berkus
Aglio Database Solutions
San Francisco
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: josh@agliodbs.com, harsh@computer.org
Subject: Re: Partitioned table performance
In-Reply-To: <200412051506.41025.josh@agliodbs.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