From: Stacy White <harsh@computer.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Stark <gsstark@mit.edu>
Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>
Cc: PFC <lists@boutiquenumerique.com>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: What needs to be done for real Partitioning?
Date: Sun, 20 Mar 2005 21:40:10 -0800
Message-ID: <007401c52dd8$73395450$0200a8c0@grownups> (raw)
References: <200503191202.38089.josh@agliodbs.com>
<op.snwojdwith1vuj@localhost>
<20050319225624.GA8467@dcc.uchile.cl>
<3404.1111277153@sss.pgh.pa.us>
<002a01c52d87$543d1010$0200a8c0@grownups>
<14172.1111359709@sss.pgh.pa.us>
<871xa9fyn4.fsf@stark.xeocode.com>
From: "Greg Stark" <gsstark@mit.edu>
> Tom Lane <tgl@sss.pgh.pa.us> writes:
> Not as good as pruning partitions entirely but if you're doing a
sequential
> scan the performance hit of a few index lookups isn't a problem.
Greg, I think you've got the right idea. For large databases, though, it
won't be uncommon to have large numbers of partitions, in which case we're
not talking about a few index lookups. The database I used in my example
wasn't huge, but the table in question had over 800 partitions. A larger
database could have thousands. I suppose the importance of global indexes
depends on the sizes of the databases your target audience is running.
Here's some more detail on our real-world experience: The group made the
decision to partition some of the larger tables for better performance. The
idea that global indexes aren't useful is pretty common in the database
world, and 2 or 3 good DBAs suggested that the 'product_no' index be local.
But with the local indexes, performance on some queries was bad enough that
the group actually made the decision to switch back to unpartitioned tables.
(The performance problems came about because of the overhead involved in
searching >800 indices to find the relevant rows).
Luckily they that had the chance to work with a truly fantastic DBA (the
author of an Oracle Press performance tuning book even) before they could
switch back. He convinced them to make some of their indexes global.
Performance dramatically improved (compared with both the unpartitioned
schema, and the partitioned-and-locally-indexed schema), and they've since
stayed with partitioned tables and a mix of local and global indexes.
But once again, I think that global indexes aren't as important as the Phase
I items in any of the Phase I/Phase II breakdowns that have been proposed in
this thread.
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: harsh@computer.org, tgl@sss.pgh.pa.us, gsstark@mit.edu, alvherre@dcc.uchile.cl, lists@boutiquenumerique.com, josh@agliodbs.com
Subject: Re: What needs to be done for real Partitioning?
In-Reply-To: <007401c52dd8$73395450$0200a8c0@grownups>
* 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