dhis2-devs-core team mailing list archive

Thread
Date

Performance of organisationunit.path prefix matching syntax

To: DHIS 2 Core developers list <dhis2-devs-core@xxxxxxxxxxxxxxxxxxx>, Jason Pickering <jason.p.pickering@xxxxxxxxx>
From: Jim Grace <jim@xxxxxxxxx>
Date: Fri, 4 Mar 2016 08:37:02 -0500

Hi All,

Just so you know, PostgreSQL performance depends on which syntax you use
when matching a prefix of the path column in the organisationunit table.
Here are some tests I did just now on my laptop to count the number of
PEPFAR organisationunits under Africa in the DATIM database.

370 ms: select count(*) from organisationunit where path ~
'/ybg3MO3hcf4/KSkooYTy8FB/'

270 ms: select count(*) from organisationunit where path ~
'^/ybg3MO3hcf4/KSkooYTy8FB/'

34 ms: select count(*) from organisationunit where path like
'/ybg3MO3hcf4/KSkooYTy8FB/%'

I did each query several times in a row, so the result would be unaffected
by database caching. All queries returned the same count (66,407).

Feel free to share this with others who might be coding this type of query.

Cheers,
Jim

-- 
Jim Grace
Core developer, DHIS 2
HISP US Inc.
http://www.dhis2.org <https://www.dhis2.org/>