0Pricing
PostgreSQL Performance & Query Optimization · 강의

재귀 CTE 및 그래프 쿼리

재귀 CTE를 사용하여 계층형 데이터와 그래프 탐색을 포함한 쿼리를 최적화하는 방법을 살펴봅니다.

재귀 CTE 및 그래프 쿼리은(는) CoddyKit의 무료 PostgreSQL Performance & Query Optimization 강의입니다. 이것은 4개 중 2번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 PostgreSQL Performance & Query Optimization 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

이 강의의 일부는 아직 번역되지 않았으며 영어로 표시됩니다.

What is Hierarchical Data?

Many real-world datasets have a natural hierarchy. Think of an organizational chart where employees report to managers, or a bill of materials where components are made of sub-components.

Standard SQL queries can struggle to navigate these relationships efficiently across multiple levels without complex, nested subqueries or joins. This is where Recursive Common Table Expressions shine!

Meet Recursive CTEs

A Common Table Expression (CTE) acts like a temporary, named result set you can reference within a single SQL statement. They improve readability and organize complex queries.

A Recursive CTE is special because it can refer to itself, allowing it to repeatedly execute to process hierarchical or graph-like data. It's perfect for "find all descendants" or "trace a path" types of problems.

The Starting Point: Base Member

Every recursive CTE has two main parts, combined with UNION ALL. The first is the base member.

This non-recursive part defines the initial set of rows for the recursion. It's the "root" or starting point of your traversal. Think of it as the first step in your journey through the data.

Let's use an employees table with employee_id, name, and manager_id.

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(50),
  manager_id INT
);

INSERT INTO employees (employee_id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2);

WITH RECURSIVE subordinates AS (
  SELECT employee_id, name, manager_id, 0 AS level
  FROM employees
  WHERE employee_id = 1
)
SELECT * FROM subordinates;

Iterating with the Recursive Member

The second part is the recursive member. This part references the CTE itself (subordinates in our example) and joins it with the base table (employees) to find the next level of data.

It runs repeatedly, processing the results from the previous iteration, until no new rows are returned. This is the "step-by-step" part of the journey.

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(50),
  manager_id INT
);

INSERT INTO employees (employee_id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2);

WITH RECURSIVE subordinates AS (
  -- Base Member
  SELECT employee_id, name, manager_id, 0 AS level
  FROM employees
  WHERE employee_id = 1

  UNION ALL

  -- Recursive Member
  SELECT e.employee_id, e.name, e.manager_id, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates WHERE level = 1; -- Just showing the first recursive step

How Recursion Stops

A recursive CTE needs a way to stop! The recursion automatically terminates when the recursive member produces no new rows. If it kept finding new rows forever, you'd have an infinite loop!

It's crucial that your recursive member's join condition and filters eventually stop matching rows, ensuring the query finishes. In our example, it stops when there are no more employees whose manager_id matches an employee_id found so far.

Tracing an Org Chart

Let's put it all together to find all subordinates of 'Alice' (employee ID 1), along with their reporting level.

The base member starts with Alice. The recursive member then finds Alice's direct reports (level 1), then their reports (level 2), and so on, until no more subordinates are found.

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(50),
  manager_id INT
);

INSERT INTO employees (employee_id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);

WITH RECURSIVE subordinates AS (
  SELECT employee_id, name, manager_id, 0 AS level
  FROM employees
  WHERE employee_id = 1

  UNION ALL

  SELECT e.employee_id, e.name, e.manager_id, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT employee_id, name, level
FROM subordinates
ORDER BY level, employee_id;

`UNION ALL` for Performance

You might wonder why we use UNION ALL and not just UNION.

  • UNION ALL: Combines all rows from both result sets, including duplicates. It's generally faster because it doesn't need to check for and remove duplicates.
  • UNION: Combines rows and removes any duplicates. In a recursive CTE, duplicate checking can add significant overhead and is often not necessary if your logic ensures unique paths or elements at each level.

For recursive traversals, UNION ALL is almost always preferred unless you specifically need to eliminate duplicates that your logic might produce.

Navigating Graphs: Friends of Friends

Recursive CTEs are also powerful for graph traversal. Imagine finding all connections in a social network or tracing dependencies.

Let's use a simple connections table to find all people connected to 'Alice' (ID 1) up to 2 levels deep.

CREATE TABLE connections (
  person_id INT,
  connected_to_id INT
);

INSERT INTO connections (person_id, connected_to_id) VALUES
(1, 2), -- Alice -> Bob
(1, 3), -- Alice -> Charlie
(2, 4), -- Bob -> David
(3, 5), -- Charlie -> Eve
(4, 6), -- David -> Frank
(5, 7); -- Eve -> Grace

WITH RECURSIVE path_finder AS (
  SELECT person_id AS start_node,
         connected_to_id AS end_node,
         1 AS depth
  FROM connections
  WHERE person_id = 1

  UNION ALL

  SELECT pf.start_node, c.connected_to_id, pf.depth + 1
  FROM connections c
  JOIN path_finder pf ON c.person_id = pf.end_node
  WHERE pf.depth < 2 -- Limit depth to avoid infinite loops or excessive recursion
)
SELECT DISTINCT start_node, end_node, depth
FROM path_finder
ORDER BY depth, end_node;

Optimizing Recursive Queries

Recursive CTEs can be powerful, but also resource-intensive if not managed well. Here are some tips:

  • Limit Depth: Always include a termination condition for depth (like level < max_depth) to prevent infinite loops or excessively long queries.
  • Index Keys: Ensure columns used in join conditions (e.g., employee_id, manager_id, person_id, connected_to_id) are indexed.
  • Filter Early: Apply filters in the base member to reduce the initial dataset.
  • Avoid Cycles: If your data can contain cycles (e.g., A -> B -> A), you might need to track the path taken (e.g., an array of visited nodes) to prevent infinite loops. PostgreSQL 14+ offers CYCLE clause for this.

Recursive CTE Structure

Consider a recursive CTE used to find all parts in a bill of materials, starting from a final product. The CTE is named bom_path.

Which of the following describes the correct structure and purpose of the recursive member of this CTE?

Recursive CTEs: A Recap

You've explored the power of Recursive CTEs!

  • They are essential for querying hierarchical data (like organizational charts) and performing graph traversals (like finding paths or connections).
  • A recursive CTE consists of a base member (starting point) and a recursive member (iterative step), combined by UNION ALL.
  • Recursion stops when the recursive member produces no new rows, but adding a depth limit is often a good practice.
  • Always consider indexing relevant columns and filtering early for optimal performance.

Mastering recursive CTEs opens up new possibilities for querying complex, interconnected datasets in PostgreSQL!

자주 묻는 질문

“재귀 CTE 및 그래프 쿼리” 강의는 무료인가요?

네 — “재귀 CTE 및 그래프 쿼리” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 PostgreSQL Performance & Query Optimization 강의 전체를 잠금 해제할 수 있습니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

“재귀 CTE 및 그래프 쿼리”에서 뭘 배우나요?

재귀 CTE를 사용하여 계층형 데이터와 그래프 탐색을 포함한 쿼리를 최적화하는 방법을 살펴봅니다. 브라우저에서 직접 실행하는 실습 코드로 PostgreSQL Performance & Query Optimization을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

PostgreSQL Performance & Query Optimization을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 PostgreSQL Performance & Query Optimization은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 2번째 강의입니다.

“재귀 CTE 및 그래프 쿼리” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 PostgreSQL Performance & Query Optimization 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 PostgreSQL Performance & Query Optimization 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

  1. 집계 및 윈도 함수 최적화
  2. 재귀 CTE 및 그래프 쿼리
  3. 성능 향상을 위한 구체화된 뷰 사용
  4. FILTER와 조건부 집계로 쿼리 최적화하기
← PostgreSQL Performance & Query Optimization(으)로 돌아가기