How can I make this big nasty query more efficient?

Question

This is for a PostgreSQL database. I have a view where I am joining a table to itself 15 times to create what we call levels, from there I am doing a COALESCE function on the 'levels'- then a little manipulation on that field, as well. I am also pulling what is a description field for each of the 15 levels. This is where my query became sluggishly slow. I am joining the SETHEADERT table to the multiple levels to get the description field for each level. As you can see I only have 3 description fields and it is currently taking very long to run. When I had 2 it took a little bit but wasn't bad. I hope this makes sense. My code is below. Any help on how to make this more efficient is greatly appreciated.

SELECT 

subset_cls,
prctr1,

CASE
WHEN prctr1 LIKE 'PC%' THEN split_part( overlay(prctr1 placing '00000' from 1 for 2 ),'.',1)
ELSE prctr1 end as pctrl2,

LVL01,
desc01,
LVL02,
desc02


FROM
( SELECT
SRC.SAP_SETNODE.SUBSET_CLS AS SUBSET_CLS,
SRC.SAP_SETHEADERT.DESCRIPTION AS desc01,
DESC_02.DESCRIPTION AS desc02,
DESC_03.DESCRIPTION AS desc03,
SRC.SAP_SETNODE.SET_NAME AS LVL01, 
SRC.SAP_SETNODE.SUBSET_NAME AS LVL02, 
SETNODE_1.SUBSET_NAME AS LVL03, 
SETNODE_2.SUBSET_NAME AS LVL04, 
SETNODE_3.SUBSET_NAME AS LVL05, 
SETNODE_4.SUBSET_NAME AS LVL06, 
SETNODE_5.SUBSET_NAME AS LVL07, 
SETNODE_6.SUBSET_NAME AS LVL08, 
SETNODE_7.SUBSET_NAME AS LVL09, 
SETNODE_8.SUBSET_NAME AS LVL10, 
SETNODE_9.SUBSET_NAME AS LVL11, 
SETNODE_10.SUBSET_NAME AS LVL12, 
SETNODE_11.SUBSET_NAME AS LVL13, 
SETNODE_12.SUBSET_NAME AS LVL14, 
SETNODE_13.SUBSET_NAME AS LVL15,
COALESCE(
SETNODE_13.SUBSET_NAME,
SETNODE_12.SUBSET_NAME,
SETNODE_11.SUBSET_NAME,
SETNODE_10.SUBSET_NAME,
SETNODE_9.SUBSET_NAME,
SETNODE_8.SUBSET_NAME,
SETNODE_7.SUBSET_NAME,
SETNODE_6.SUBSET_NAME,
SETNODE_5.SUBSET_NAME,
SETNODE_4.SUBSET_NAME,
SETNODE_3.SUBSET_NAME,
SETNODE_2.SUBSET_NAME,
SETNODE_1.SUBSET_NAME,
SRC.SAP_SETNODE.SUBSET_NAME,
SRC.SAP_SETNODE.SET_NAME)
AS prctr1 
FROM SRC.SAP_SETNODE
LEFT JOIN SRC.SAP_SETHEADERT  ON SRC.SAP_SETHEADERT.SET_NAME = SRC.SAP_SETNODE.SET_NAME

LEFT JOIN SRC.SAP_SETNODE AS SETNODE_1 ON SRC.SAP_SETNODE.SUBSET_NAME = SETNODE_1.SET_NAME
AND SRC.SAP_SETNODE.SUBSET_CLS = SETNODE_1.SUBSET_CLS

LEFT JOIN SRC.SAP_SETHEADERT as DESC_02  ON DESC_02.SET_NAME = SETNODE_1.SET_NAME

LEFT JOIN SRC.SAP_SETNODE AS SETNODE_2 ON SETNODE_1.SUBSET_NAME = SETNODE_2.SET_NAME 
AND SETNODE_1.SUBSET_CLS = SETNODE_2.SUBSET_CLS

LEFT JOIN SRC.SAP_SETHEADERT as DESC_03  ON DESC_03.SET_NAME = SETNODE_2.SET_NAME



LEFT JOIN SRC.SAP_SETNODE AS SETNODE_3 ON SETNODE_2.SUBSET_NAME = SETNODE_3.SET_NAME 
AND SETNODE_2.SUBSET_CLS = SETNODE_3.SUBSET_CLS



LEFT JOIN SRC.SAP_SETNODE AS SETNODE_4 ON SETNODE_3.SUBSET_NAME = SETNODE_4.SET_NAME 
AND SETNODE_3.SUBSET_CLS = SETNODE_4.SUBSET_CLS



LEFT JOIN SRC.SAP_SETNODE AS SETNODE_5 ON SETNODE_4.SUBSET_NAME = SETNODE_5.SET_NAME
AND SETNODE_4.SUBSET_CLS = SETNODE_5.SUBSET_CLS


LEFT JOIN SRC.SAP_SETNODE AS SETNODE_6 ON SETNODE_5.SUBSET_NAME = SETNODE_6.SET_NAME
AND SETNODE_5.SUBSET_CLS = SETNODE_6.SUBSET_CLS


LEFT JOIN SRC.SAP_SETNODE AS SETNODE_7 ON SETNODE_6.SUBSET_NAME = SETNODE_7.SET_NAME
AND SETNODE_6.SUBSET_CLS = SETNODE_7.SUBSET_CLS



LEFT JOIN SRC.SAP_SETNODE AS SETNODE_8 ON SETNODE_7.SUBSET_NAME = SETNODE_8.SET_NAME
AND SETNODE_7.SUBSET_CLS = SETNODE_8.SUBSET_CLS



LEFT JOIN SRC.SAP_SETNODE AS SETNODE_9 ON SETNODE_8.SUBSET_NAME = SETNODE_9.SET_NAME
AND SETNODE_8.SUBSET_CLS = SETNODE_9.SUBSET_CLS



LEFT JOIN SRC.SAP_SETNODE AS SETNODE_10 ON SETNODE_9.SUBSET_NAME = SETNODE_10.SET_NAME 
AND SETNODE_9.SUBSET_CLS = SETNODE_10.SUBSET_CLS 

LEFT JOIN SRC.SAP_SETNODE AS SETNODE_11 ON SETNODE_10.SUBSET_NAME = SETNODE_11.SET_NAME 
AND SETNODE_10.SUBSET_CLS = SETNODE_11.SUBSET_CLS

LEFT JOIN SRC.SAP_SETNODE AS SETNODE_12 ON SETNODE_11.SUBSET_NAME = SETNODE_12.SET_NAME
AND SETNODE_11.SUBSET_CLS = SETNODE_12.SUBSET_CLS

LEFT JOIN SRC.SAP_SETNODE AS SETNODE_13 ON SETNODE_12.SUBSET_NAME = SETNODE_13.SET_NAME
AND SETNODE_12.SUBSET_CLS = SETNODE_13.SUBSET_CLS

GROUP BY SRC.SAP_SETNODE.SUBSET_CLS, SRC.SAP_SETHEADERT.DESCRIPTION, DESC_02.DESCRIPTION,              
DESC_03.DESCRIPTION, SRC.SAP_SETNODE.SET_NAME, 
SRC.SAP_SETNODE.SUBSET_NAME, SETNODE_1.SUBSET_NAME, SETNODE_2.SUBSET_NAME, 
SETNODE_3.SUBSET_NAME, SETNODE_4.SUBSET_NAME, SETNODE_5.SUBSET_NAME, 
SETNODE_6.SUBSET_NAME, SETNODE_7.SUBSET_NAME, SETNODE_8.SUBSET_NAME, 
SETNODE_9.SUBSET_NAME, SETNODE_10.SUBSET_NAME, SETNODE_11.SUBSET_NAME, 
SETNODE_12.SUBSET_NAME, SETNODE_13.SUBSET_NAME

HAVING SRC.SAP_SETNODE.SUBSET_CLS='0101' AND SRC.SAP_SETNODE.SET_NAME='SISW.'  
||get_fy_part('YEAR',       clock_timestamp())

ORDER BY SRC.SAP_SETNODE.SET_NAME, SRC.SAP_SETNODE.SUBSET_NAME, SETNODE_1.SUBSET_NAME, 
SETNODE_2.SUBSET_NAME, SETNODE_3.SUBSET_NAME, SETNODE_4.SUBSET_NAME, SETNODE_5.SUBSET_NAME, 
SETNODE_6.SUBSET_NAME, SETNODE_7.SUBSET_NAME

) foo

Answer 1

If you move the filtering to the where clause there will be less rows to be joined. Change this:

group by ...
HAVING
    SRC.SAP_SETNODE.SUBSET_CLS = '0101' AND
    SRC.SAP_SETNODE.SET_NAME = 'SISW.' || get_fy_part('YEAR', clock_timestamp())

to

where
    SRC.SAP_SETNODE.SUBSET_CLS = '0101' AND
    SRC.SAP_SETNODE.SET_NAME = 'SISW.' || get_fy_part('YEAR', clock_timestamp())
group by ...