Hello, 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: http://archives.postgresql.org/pgsql-admin/2006-02/msg00238.php 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: http://picasaweb.google.com/pawel.sztromwasser/Other#5257654388791782770). 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, Pawel ------------------------------------------------------------------------- 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 http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ basedb-devel mailing list basedb-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/basedb-devel
