Project

General

Profile

Actions
Copy link

Task #1114

open

Big table idm_forest_index for 18k tree nodes

Added by Alena Peterová almost 6 years ago. Updated almost 6 years ago.

Status:
New
Priority:
Low
Assignee:
Radek Tomiška
Category:
Tree structures
Target version:
-
Start date:
05/29/2018
Due date:
% Done:

0%

Estimated time:
Owner:

Description

I synchronized 18 000 tree nodes and in the end, the size of the DB table idm_forest_index was 17 GB. After manually running "vacuum full idm_forest_index", the size decreased to 8 MB.

Tested on the version 7.8.5 on Windows. Test CSV with the organizations structure is attached.
In my local environment, I got to 4 GB for 9 000 tree nodes.

It would be nice to look into it when there is time.

SQL script to get the Size and its metadata of the table (my local environment):

SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size" 
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
              Table                |    Size    | External Size 
------------------------------------+------------+---------------
idm_forest_index                   | 4673 MB    | 3049 MB

VACUUM FULL idm_forest_index;

idm_forest_index                   | 3280 kB    | 2144 kB

Files

org_structure.csv.zip (41.6 KB) org_structure.csv.zip Alena Peterová, 05/29/2018 03:16 PM
Actions
Copy link
 #1

Updated by Alena Peterová almost 6 years ago

Note about the data:
The test CSV contains 1 root (it has empty MANAGER_UTVAR) and cca 250 organisations, that have non-existent parent (the code in MANAGER_UTVAR doesn't exist). Those 250 organisations won't be handled by synchronization. The depth of the structure is cca 6 levels.

Actions
Copy link

Also available in: Atom PDF