Re: Quick Performance Poll

Hi Luke,
Thank you very much for your prompt reply.
I have got ***much*** more information than expected ;)

Obviously there are thing to be improved in the current implementation of BMP indexes,
But anyway they are worth usung (I wa pretty impressed from the BMP index performance, after
Reading a PDF document on Bizgres site).

Thanks ahain for the information.

Regards. Milen

-----Original Message-----
From: Luke Lonergan [mailto:llonergan(at)greenplum(dot)com]
Sent: Thursday, April 20, 2006 11:28 PM
To: Milen Kulev
Cc: pgsql-performance(at)postgresql(dot)org; bizgres-general
Subject: Re: [PERFORM] Quick Performance Poll

Milen,

On 4/20/06 12:45 PM, "Milen Kulev" <makulev(at)gmx(dot)net> wrote:

> 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"?

Yes.

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

Insert/Update (but not delete) operations will often invalidate a bitmap index in our current implementation because we
have not implemented a maintenance method for them when insertions re-use TIDs. We are in the planning stages for an
update that will fix this.

> Or you mean that bitmap index corruption is possible when issueing DML
> againts BMP indexes?

We check for the case of an insertion that causes a re-used TID and issue an error that indicates the index should be
removed before the operation is retried. This isn't particularly useful for cases where inserts occur frequently, so
the current use-case if for tables where DML should be done in batches after removing the index, then the index
re-applied.

> 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 ).

We will be much faster than that! Concurrency will be less than ideal with our maintenance approach initially, but
there shouldn't be a corruption problem.

> Is Bizgres implementation suffering from this kind child deseases ?

Sneeze, cough.

- Luke
>
>
> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org
> [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Luke
> Lonergan
> Sent: Thursday, April 20, 2006 5:03 PM
> To: jim(at)contactbda(dot)com; Simon Dale; pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Quick Performance Poll
>
>
> Jim,
>
> On 4/20/06 7:40 AM, "Jim Buttafuoco" <jim(at)contactbda(dot)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
>
>
>