Hi I've got an instance with around 50 mill datavalue records where I wanted to remove around 24,000 Organisation Units (none of them have any datavalue table records).
The delete process initially ran for around 5 hours, I killed it and used EXPLAIN ANALYZE DELETE to check what was going on - and found that deleting ONE organisation unit would take just over 5,030 milliseconds, with 5,018 of them due to checking for organisationunitid values in the data value table. I then added the following index to the datavalue table: CREATE INDEX in_datavalue_organisationunitid ON public.datavalue USING btree (sourceid); That took around 1 min 20 seconds to run. With that index in place, deleting ~24,000 organisationunits took 1 min 41 seconds. Question: WHY is there no indexes on the datavalue table for the key fields (dataelementid, periodid, organisationunitid, categoryoptioncomboid)?? It definitely looks like it will speed up a few things radically? Regards Calle -- ******************************************* Calle Hedberg 46D Alma Road, 7700 Rosebank, SOUTH AFRICA Tel/fax (home): +27-21-685-6472 Cell: +27-82-853-5352 Iridium SatPhone: +8816-315-19119 Email: [email protected] Skype: calle_hedberg *******************************************
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
