From: Luke Lonergan <llonergan@greenplum.com>
To: jim@contactbda.com, "Simon Dale" <sdale@rm.com>
To: pgsql-performance@postgresql.org
Subject: Re: Quick Performance Poll
Date: Thu, 20 Apr 2006 08:03:10 -0700
Message-ID: <C06CF0BE.21E2F%llonergan@greenplum.com> (raw)
In-Reply-To: <20060420143830.M5360@contactbda.com>
Jim,
On 4/20/06 7:40 AM, "Jim Buttafuoco" <jim@contactbda.com> wrote:
> First of all this is NOT a single table and yes I am using partitioning and
> the constaint exclusion stuff. the largest
> set of tables is over 2T. I have not had to rebuild the biggest database yet,
> but for a smaller one ~1T the restore
> takes about 12 hours including many indexes on both large and small tables
You would probably benefit greatly from the new on-disk bitmap index feature
in Bizgres Open Source. It's 8.1 plus the sort speed improvement and
on-disk bitmap index.
Index creation and sizes for the binary version are in the table below (from
a performance report on bizgres network. The version in CVS tip on
pgfoundry is much faster on index creation as well.
The current drawback to bitmap index is that it isn't very maintainable
under insert/update, although it is safe for those operations. For now, you
have to drop index, do inserts/updates, rebuild index.
We'll have a version that is maintained for insert/update next.
- Luke
# Indexed Columns Create Time (seconds) Space Used (MBs)
BITMAP BTREE BITMAP BTREE
1 L_SHIPMODE 454.8 2217.1 58 1804
2 L_QUANTITY 547.2 937.8 117 1804
3 L_LINENUMBER 374.5 412.4 59 1285
4 L_SHIPMODE, L_QUANTITY 948.7 2933.4 176 2845
5 O_ORDERSTATUS 83.5 241.3 5 321
6 O_ORDERPRIORITY 108.5 679.1 11 580
7 C_MKTSEGMENT 10.9 51.3 1 45
8 C_NATIONKEY 8.3 9.3 2 32
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: llonergan@greenplum.com, sdale@rm.com
Subject: Re: Quick Performance Poll
In-Reply-To: <C06CF0BE.21E2F%llonergan@greenplum.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