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: calle.hedberg@gmail.com
Skype: calle_hedberg