I found something interesting regarding BASE2 performance on Postgres 
(we have 8.3 but I suppose it is valid for previous versions as well). 
Recently I was unable to delete ArrayDesign (Illumina Human 8Ref, no 
items connected to it, features imported), because database query was 
endless. I stopped it after 3 days and started similar on development 
installation (much, much smaller). It took almost 20h to complete.

We started digging and found (among other similar) this: 
In short, postgres does not create indexes on foreign keys by default. 
That has huge impact on performance when updating/deleting items from 
table that is referenced by other big table. In case above Features 
(that are deleted with ArrayDesign) are referenced by all RawData* 
tables. I created indexes on feature_id column in all of these tables 
and delete took less than a minute.

That reminds me of another case where performance for postgres could be 
improved. When testing variuos kinds of jobs I noticed that concurent 
jobs creating root bioassayset took surprisingly long and did not scale 
well. That was due to sequential lookup in RawData* table on 
rawbioassay_id column. Creating index on that column improved 
performance by more than 50% (see: 

I know that indexes have drawbacks as well, but in cases above they are 
a huge advantage. Could these indexes be incorporated in 
updateDb/indexes scripts? If you know about any other possible places in 
BASE database where similar things may occur, let me know and I will 
test them.

Best regards,

This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
basedb-devel mailing list

Reply via email to