Oh, I didn't know this. We are mainly working with MySQL which implicitly creates an index for every foreign key. I agree that indexes are certainly needed on the big tables.
I guess we could make the 'updateindexes.sh' script check that each foreign key definition has an associated index. Right now it can only be used to check that indexes that have been explicitly defined really exists in the database. I'll add this for the 2.9 release. /Nicklas Pawel Sztromwasser wrote: > 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 ------------------------------------------------------------------------- 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
