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/>
-- Mailing list: https://launchpad.net/~dhis2-devs-core Post to : dhis2-devs-core@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs-core More help : https://help.launchpad.net/ListHelp
