PostgreSQL - Recursive Query 심화(?), 트리형태 데이터 조회

"1층"이라는 단어로 조회시 자신 및 자신의 하위 노드들에 대한 path까지 조회한다. 일단 내가 원하는 결과물은 나오는데, 성능은 보장못한다. @_@

WITH RECURSIVE tb_result1( pos_name, pos_code, pos_lv, pos_parent, pos_path ) AS (
	WITH RECURSIVE tb_result( pos_name, pos_code, pos_lv, pos_parent, pos_path ) AS (
		SELECT	alias_nm, pos_cd, 0, pos_parent, pos_nm
		FROM	tb_position
		WHERE	pos_level = 0
		UNION ALL
		SELECT	a.alias_nm, a.pos_cd, b.pos_lv+1, a.pos_parent, pos_path || ' > ' || a.pos_nm
		FROM	tb_position AS a JOIN tb_result AS b
		ON 	a.pos_parent = b.pos_code
	)
	SELECT	* FROM tb_result
	WHERE	pos_name LIKE '%1층%'	
	UNION ALL
	SELECT a.alias_nm, a.pos_cd, b.pos_lv+1, a.pos_parent, pos_path || ' > ' || a.pos_nm
	FROM	tb_position AS a JOIN tb_result1 AS b
	ON 	a.pos_parent = b.pos_code
)
SELECT	pos_name, pos_code, pos_lv, pos_parent, pos_path
FROM	tb_result1
GROUP BY pos_name, pos_code, pos_lv, pos_parent, pos_path
ORDER BY pos_path

결과물

대상 테이블