Missing index on datavalue table cause extreme slowness


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


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?




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