dhis2-devs team mailing list archive

[Calle Hedberg <calle.hedberg@xxxxxxxxx>]

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@xxxxxxxxx

Skype: calle_hedberg

*******************************************