From: "Milen Kulev" <makulev@gmx.net>
To: "'Luke Lonergan'" <llonergan@greenplum.com>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Quick Performance Poll
Date: Thu, 20 Apr 2006 21:45:09 +0200
Message-ID: <000001c664b2$eef4ba50$0a00a8c0@trivadis.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Importance: Normal
In-Reply-To: <C06CF0BE.21E2F%llonergan@greenplum.com>

Hi Luke, 
I (still) haven't tried Bizgres, but what do you mean  with "The current drawback to bitmap index is that it isn't very
maintainable under insert/update, although it is safe for those operations"?

Do you mean that INSERT/UPDATE operations against bitmap indexes are imperformant ?
If yes, to what extend ?

Or you mean that bitmap index corruption is possible when issueing DML  againts BMP indexes?
Or  BMP indexes are growing too fast as a result of DML ?

I am asking this question because Oracle needed 3 years to solve its BMP index problems (BMP index corruption/ space
usage explosion when several processes are performing DML operations ).

Is Bizgres implementation  suffering from this kind child deseases ?

Regards . Milen 


-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Luke Lonergan
Sent: Thursday, April 20, 2006 5:03 PM
To: jim@contactbda.com; Simon Dale; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Quick Performance Poll


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  


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match