------------------------------------------------------------------------------------------------------------ slow query(10 sec): select ent.ID,ent.TYPE,ent.STATUS,ent.NAME from (select e.ID, e.TYPE, e.STATUS, e.NAME from ENT_PROJECT e, (select h.*, CASE WHEN f1.ID=-1 THEN '' ELSE f1.NAME || CASE WHEN f2.ID=-1 THEN '' ELSE ' > ' || f2.NAME || CASE WHEN f3.ID=-1 THEN '' ELSE ' > ' || f3.NAME || CASE WHEN f4.ID=-1 THEN '' ELSE ' > ' || f4.NAME || CASE WHEN f5.ID=-1 THEN '' ELSE ' > ' || f5.NAME || CASE WHEN f6.ID=-1 THEN '' ELSE ' > ' || f6.NAME END END END END END END as PATH from COMN_ATTR_HIERARCH h join ENT_FOLDER f1 on h.FOLDER_ID_1=f1.ID join ENT_FOLDER f2 on h.FOLDER_ID_2=f2.ID join ENT_FOLDER f3 on h.FOLDER_ID_3=f3.ID join ENT_FOLDER f4 on h.FOLDER_ID_4=f4.ID join ENT_FOLDER f5 on h.FOLDER_ID_5=f5.ID join ENT_FOLDER f6 on h.FOLDER_ID_6=f6.ID ) path where e.STATUS!=cast(-1 as numeric) and e.ID = path.NODE_ID) ent , COMN_ATTR_HIERARCH hier where hier.NODE_ID=ent.ID and hier.HIERARCHY_ID='IMPLEMENTATION' and hier.DOMAIN=1 ------------------------------------------------------------------------------------------------------------ QUERY PLAN Nested Loop (cost=1808.05..1955.27 rows=14 width=660) Join Filter: ("outer".id = "inner".node_id) -> Nested Loop (cost=0.00..10.82 rows=1 width=244) -> Index Scan using idx_hierarch_hierarch_id on comn_attr_hierarch hier (cost=0.00..5.98 rows=1 width=32) Index Cond: ((hierarchy_id = 'IMPLEMENTATION'::bpchar) AND ("domain" = 1::numeric)) -> Index Scan using pk_ent_project on ent_project e (cost=0.00..4.83 rows=1 width=212) Index Cond: ("outer".node_id = e.id) Filter: (status <> -1::numeric) -> Materialize (cost=1910.33..1910.33 rows=2730 width=416) -> Merge Join (cost=1808.05..1910.33 rows=2730 width=416) Merge Cond: ("outer".id = "inner".folder_id_6) -> Index Scan using pk_ent_folder on ent_folder f6 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=1808.05..1814.88 rows=2730 width=384) Sort Key: h.folder_id_6 -> Merge Join (cost=1275.45..1377.73 rows=2730 width=384) Merge Cond: ("outer".id = "inner".folder_id_5) -> Index Scan using pk_ent_folder on ent_folder f5 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=1275.45..1282.28 rows=2730 width=352) Sort Key: h.folder_id_5 -> Merge Join (cost=1017.37..1119.64 rows=2730 width=352) Merge Cond: ("outer".id = "inner".folder_id_4) -> Index Scan using pk_ent_folder on ent_folder f4 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=1017.37..1024.19 rows=2730 width=320) Sort Key: h.folder_id_4 -> Merge Join (cost=759.28..861.56 rows=2730 width=320) Merge Cond: ("outer".id = "inner".folder_id_3) -> Index Scan using pk_ent_folder on ent_folder f3 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=759.28..766.11 rows=2730 width=288) Sort Key: h.folder_id_3 -> Merge Join (cost=501.20..603.47 rows=2730 width=288) Merge Cond: ("outer".id = "inner".folder_id_2) -> Index Scan using pk_ent_folder on ent_folder f2 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=501.20..508.02 rows=2730 width=256) Sort Key: h.folder_id_2 -> Merge Join (cost=243.11..345.39 rows=2730 width=256) Merge Cond: ("outer".id = "inner".folder_id_1) -> Index Scan using pk_ent_folder on ent_folder f1 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=243.11..249.94 rows=2730 width=224) Sort Key: h.folder_id_1 -> Seq Scan on comn_attr_hierarch h (cost=0.00..87.30 rows=2730 width=224) ------------------------------------------------------------------------------------------------------------ Fast query (.6 sec): select ent.ID,ent.TYPE,ent.STATUS,ent.NAME from (select e.ID, e.TYPE, e.STATUS, e.NAME from ENT_PROJECT e, (select h.*, CASE WHEN f1.ID=-1 THEN '' ELSE f1.NAME || CASE WHEN f2.ID=-1 THEN '' ELSE ' > ' || f2.NAME || CASE WHEN f3.ID=-1 THEN '' ELSE ' > ' || f3.NAME || CASE WHEN f4.ID=-1 THEN '' ELSE ' > ' || f4.NAME || CASE WHEN f5.ID=-1 THEN '' ELSE ' > ' || f5.NAME || CASE WHEN f6.ID=-1 THEN '' ELSE ' > ' || f6.NAME END END END END END END as PATH from COMN_ATTR_HIERARCH h join ENT_FOLDER f1 on h.FOLDER_ID_1=f1.ID join ENT_FOLDER f2 on h.FOLDER_ID_2=f2.ID join ENT_FOLDER f3 on h.FOLDER_ID_3=f3.ID join ENT_FOLDER f4 on h.FOLDER_ID_4=f4.ID join ENT_FOLDER f5 on h.FOLDER_ID_5=f5.ID join ENT_FOLDER f6 on h.FOLDER_ID_6=f6.ID ) path where e.STATUS!=cast(-1 as numeric) and e.ID = path.NODE_ID) ent , COMN_ATTR_HIERARCH hier where hier.NODE_ID=ent.ID and exists( select * from COMN_ATTR_HIERARCH h2 where h2.HIERARCHY_ID='IMPLEMENTATION' and h2.DOMAIN=1 and h2.NODE_ID=hier.NODE_ID and h2.HIERARCHY_ID=hier.HIERARCHY_ID and h2.DOMAIN=hier.DOMAIN) ------------------------------------------------------------------------------------------------------------ QUERY PLAN Merge Join (cost=16145.60..16289.84 rows=18539 width=660) Merge Cond: ("outer".id = "inner".node_id) -> Merge Join (cost=13782.29..13863.08 rows=1358 width=244) Merge Cond: ("outer".id = "inner".node_id) -> Index Scan using pk_ent_project on ent_project e (cost=0.00..54.50 rows=995 width=212) Filter: (status <> -1::numeric) -> Sort (cost=13782.29..13785.70 rows=1365 width=32) Sort Key: hier.node_id -> Seq Scan on comn_attr_hierarch hier (cost=0.00..13711.21 rows=1365 width=32) Filter: (subplan) SubPlan -> Index Scan using pk_comn_attr_hierarch on comn_attr_hierarch h2 (cost=0.00..4.99 rows=1 width=316) Index Cond: (("domain" = 1::numeric) AND ("domain" = $2) AND (node_id = $0)) Filter: ((hierarchy_id = 'IMPLEMENTATION'::bpchar) AND (hierarchy_id = $1)) -> Sort (cost=2363.32..2370.14 rows=2730 width=416) Sort Key: h.node_id -> Merge Join (cost=1808.05..1910.33 rows=2730 width=416) Merge Cond: ("outer".id = "inner".folder_id_6) -> Index Scan using pk_ent_folder on ent_folder f6 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=1808.05..1814.88 rows=2730 width=384) Sort Key: h.folder_id_6 -> Merge Join (cost=1275.45..1377.73 rows=2730 width=384) Merge Cond: ("outer".id = "inner".folder_id_5) -> Index Scan using pk_ent_folder on ent_folder f5 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=1275.45..1282.28 rows=2730 width=352) Sort Key: h.folder_id_5 -> Merge Join (cost=1017.37..1119.64 rows=2730 width=352) Merge Cond: ("outer".id = "inner".folder_id_4) -> Index Scan using pk_ent_folder on ent_folder f4 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=1017.37..1024.19 rows=2730 width=320) Sort Key: h.folder_id_4 -> Merge Join (cost=759.28..861.56 rows=2730 width=320) Merge Cond: ("outer".id = "inner".folder_id_3) -> Index Scan using pk_ent_folder on ent_folder f3 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=759.28..766.11 rows=2730 width=288) Sort Key: h.folder_id_3 -> Merge Join (cost=501.20..603.47 rows=2730 width=288) Merge Cond: ("outer".id = "inner".folder_id_2) -> Index Scan using pk_ent_folder on ent_folder f2 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=501.20..508.02 rows=2730 width=256) Sort Key: h.folder_id_2 -> Merge Join (cost=243.11..345.39 rows=2730 width=256) Merge Cond: ("outer".id = "inner".folder_id_1) -> Index Scan using pk_ent_folder on ent_folder f1 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=243.11..249.94 rows=2730 width=224) Sort Key: h.folder_id_1 -> Seq Scan on comn_attr_hierarch h (cost=0.00..87.30 rows=2730 width=224) ------------------------------------------------------------------------------------------------------------