PostgreSQL 반복 조회를 진단하려면 후보를 구성하는 작업과 후보를 소비하는 작업을 구분하세요. 내부 스캔의 실행 횟수를 확인하고, 캐싱으로 반복 작업을 피하는지 살펴본 다음, 그룹화하여 재사용하기, CTE 인라이닝 또는 인덱스가 있는 임시 테이블을 비교하세요.

이 안내서는 느린 기사 쿼리를 설명해야 하는 Mac 사용자 개발자를 위한 것입니다. 후보 기사를 한 번 선택한 다음, 각 기사의 주제에서 ID가 가장 큰 후보 기사를 찾습니다. 전체 과정에서 합성 데이터를 사용합니다. 목표는 작업이 반복되는 지점을 파악하고 결과를 보존하는 쿼리 형태를 선택하는 것입니다.

재현 가능한 후보 쿼리 설정하기

psql을 사용할 수 있는 기존 로컬 PostgreSQL 설치를 이용하세요. Terminal을 열고 평소 연결 설정으로 psql을 실행한 다음, SQL 예제를 진행하는 동안 같은 세션을 열어 두세요. PostgreSQL 문서에서는 psql을 쿼리를 제출하고 결과를 확인하는 대화형 터미널로 설명합니다. psql

먼저 SHOW server_version;으로 서버 버전을 확인하세요. 이 글을 위해 검색한 최신 문서는 PostgreSQL 18을 현재 버전으로 안내합니다. 예제에서는 PostgreSQL 12 이상 구문을 사용합니다. 이는 기능이 제공되는 경계일 뿐, 이전 버전을 사용하라는 권고가 아닙니다. PostgreSQL 12에서 CTE 인라이닝과 MATERIALIZED, NOT MATERIALIZED 제어 기능이 도입되었습니다. WITH 쿼리, 릴리스 12

새 세션에서 다음 설정을 한 번 실행하세요. 합성 기사 20,000개와 주제 및 ID 조회용 인덱스를 생성합니다. 아래에서 사용할 후보 조건은 기사 앞부분 2,000개를 선택합니다.

CREATE TEMP TABLE article_demo (
    id integer PRIMARY KEY,
    topic_id integer NOT NULL,
    title text NOT NULL
);

INSERT INTO article_demo (id, topic_id, title)
SELECT g, 1 + (g % 50), 'Article ' || g
FROM generate_series(1, 20000) AS s(g);

CREATE INDEX article_demo_topic_id_idx
    ON article_demo (topic_id, id);

ANALYZE article_demo;

임시 테이블은 기본적으로 세션이 끝날 때까지 유지됩니다. 데이터를 적재한 뒤 ANALYZE을 명시적으로 실행하세요. PostgreSQL의 autovacuum 프로세스는 임시 테이블을 분석하지 않습니다. CREATE TABLE

이제 기준 결과를 확인하세요. 측면 하위 쿼리는 현재 기사의 주제에서 ID가 가장 큰 후보를 계산합니다. 여기서 “가장 큰 ID”는 정의된 작업일 뿐, 게시 날짜를 뜻하지 않습니다.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate AS x
    WHERE x.topic_id = c.topic_id
) AS m;

명시적인 MATERIALIZED은 실험에서 재사용이 이뤄지는 경계를 분명하게 합니다. 구체화된 CTE는 한 번 생성한 뒤 소비자가 저장된 행을 반복해서 읽을 수 있습니다. PostgreSQL 문서에서는 여러 번 참조하는 구체화된 CTE가 중간 결과를 검색할 때 기본 테이블 인덱스의 이점을 잃는 사례를 보여 줍니다. 공통 테이블 표현식의 구체화

이 글의 SQL 예제는 검토했지만 PostgreSQL 서버에서 실행하지 않았습니다. 합성 행 수 계산은 별도로 확인했으며, 실행 시간이나 캡처한 실행 계획을 측정값으로 제시하지 않습니다.

실행 계획을 읽고 반복 작업 찾기

후보를 생성하는 분기에서 시작해 각 소비자를 따라가세요. 중첩 루프에서는 바깥쪽 입력과 내부 조회를 파악하세요. PostgreSQL은 노드의 실행 횟수를 loops로 표시합니다. 실제 행 수와 노드 시간은 실행당 평균입니다. 상관 스칼라 하위 쿼리 버전에서는 대신 SubPlan 아래에 반복 작업이 나타날 수 있으므로, 특정 레이블을 찾기보다 실제로 실행되는 노드를 따라가며 진단해야 합니다. EXPLAIN 사용하기

실제 실행 계획에서 다음 네 가지를 기록하세요.

  1. 바깥쪽 조회로 들어가는 행은 몇 개인가요?
  2. 내부 스캔은 몇 번 실행되나요?
  3. 해당 스캔이 후보를 읽은 뒤 필터로 대부분을 버리나요?
  4. 바깥쪽 행과 비용이 큰 내부 작업 사이에 Memoize 노드가 있나요?

각 계층을 구분하세요. 집계 노드는 실행당 최댓값 하나를 반환하면서도 이를 계산하기 위해 자식 노드에서 많은 행을 스캔할 수 있습니다. 따라서 후보 생성기가 한 번 실행되었다고 해서 후보 소비 비용이 낮다는 뜻은 아닙니다.

이 예제에서는 후보 2,000개가 주제 50개에 걸쳐 있으며, 주제마다 기사 40개가 있습니다. 다음 계산은 가능한 작업 패턴을 설명할 뿐, 실행 계획이나 실행 시간의 보장은 아닙니다.

소비 패턴 예시 후보 행 검사 횟수
각 바깥쪽 기사마다 후보 전체를 스캔 2,000 × 2,000 = 4,000,000
주제마다 한 번 전체 스캔하고 이후 조회는 모두 캐시 50 × 2,000 = 100,000
후보를 주제별로 한 번 그룹화 그룹화 단계에 들어가는 행 2,000개와 이후 조인 작업

PostgreSQL 14 이상에서는 이 차이가 중요합니다. 이 버전에서 내부 중첩 루프 결과의 메모이제이션이 도입되었습니다. 최신 문서에서는 Memoize가 매개변수화된 스캔 결과를 캐싱하고, 캐시 적중 시 기본 스캔을 건너뛸 수 있다고 설명합니다. 항목이 제거될 수도 있습니다. 바깥쪽 행 수천 개가 전체 스캔 수천 회를 뜻한다고 판단하기 전에 캐시 키와 보고된 적중, 누락, 제거 및 오버플로를 확인하세요. 릴리스 14, 쿼리 계획

추정 행 수와 실제 행 수도 가장 먼저 크게 어긋나는 지점에서 비교하세요. PostgreSQL은 이러한 추정값을 확인하라고 권장하며, 작은 테스트 테이블의 실행 계획이 더 큰 입력에도 그대로 적용되지는 않는다고 경고합니다. 부모 노드와 자식 노드의 시간을 더하지 마세요. 두 값은 실행 작업이 서로 겹치는 구간을 설명합니다. EXPLAIN 사용하기

반복 작업에 맞는 재작성 방식 선택하기

핵심은 각 소비자가 후보 집합에서 필요한 작업량입니다. 다음 안내표를 보고 첫 실험을 선택하세요.

관찰된 요구 사항 첫 실험 보존해야 할 조건
모든 후보에 주제별 최댓값이 필요함 한 번 그룹화한 뒤 조인 주제마다 요약 행 하나 유지
각 소비자가 원본에서 인덱스로 조회할 작은 부분만 필요함 NOT MATERIALIZED 시도 전체 후보 조건 유지
여러 문장에서 명시적으로 인덱스를 둔 후보 관계가 필요함 인덱스가 있는 임시 테이블 생성 같은 세션 유지 및 설정 비용 포함
반복 매개변수에서 이미 캐시 적중이 많음 쿼리 형태를 바꾸기 전에 전체 실행 시간 비교 기본 스캔이 실제로 비용이 큰지 확인

주제별 결과를 한 번 계산하기

이 작업에서는 그룹 요약을 사용하면 재사용 의도가 분명해집니다. 설정을 마친 뒤 같은 세션에서 다음을 실행하세요.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
), topic_max AS (
    SELECT topic_id, max(id) AS highest_id
    FROM candidate
    GROUP BY topic_id
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
JOIN topic_max AS m USING (topic_id);

이 재작성은 주제마다 최댓값 하나를 계산하고 모든 후보 기사에 연결합니다. PostgreSQL은 max를 NULL이 아닌 입력값의 최댓값으로 정의합니다. 예제의 주제와 ID 열에는 NULL이 없으므로 동등성이 명확합니다. 모든 후보는 자신이 속한 그룹에 포함됩니다. 집계 함수

EXPLAIN 없이 쿼리를 실행하면 결과 행은 2,000개여야 합니다. 예를 들어 기사 1은 주제 2에 속하며 highest_id = 1951를 받습니다. 주제 1은 highest_id = 2000을 받습니다. 이는 샘플 생성기의 결정적인 결과입니다.

일반화하기 전에 결과의 의미를 확인하세요. 열을 추가로 그룹화하면 주제마다 요약 행이 여러 개 생겨 조인 결과가 늘어날 수 있습니다. 주제 ID에 NULL을 허용한다면 동등성 의미도 다시 검토해야 합니다. ID가 가장 큰 기사의 제목이 필요한 작업이라면 최댓값을 후보 행에 다시 조인해야 합니다. 제목의 최댓값을 별도로 구하면 다른 질문에 답하게 됩니다.

이 재작성으로 다른 실행 계획을 선택할 수 있지만, 해시 조인을 강제하거나 속도 향상을 보장하지는 않습니다. 그룹화와 조인 작업량을 기준 쿼리의 실제 내부 실행 횟수와 비교하세요.

원본 인덱스로 중간 스캔을 대체할 수 있는지 확인하기

대상 CTE가 적격이라면 기준 쿼리 전체에서 AS MATERIALIZED를 AS NOT MATERIALIZED로 바꾼 뒤 다시 실행하세요. 이렇게 하면 원본 관계와 함께 쿼리를 최적화하여 저장된 후보를 스캔하는 대신 원본을 조회할 수 있습니다. 단, 후보 계산이 중복될 수도 있습니다. 공통 테이블 표현식의 구체화

이 예제의 (topic_id, id) 인덱스는 선행 주제 열의 동등 조건과 ID 열의 제한 조건에 맞습니다. 따라서 테스트하기에 합리적인 인덱스입니다. 실제 실행 계획에서 사용되는지, 얼마나 많이 스캔하는지 확인하세요. PostgreSQL 문서는 다중 열 B-tree 스캔을 제한하는 조건으로 선행 열의 동등 조건과 그다음 열의 부등 조건을 설명합니다. 다중 열 인덱스

id <= 2000를 후보 정의 안에 유지하세요. 20,000개 원본 행 전체에서 최댓값을 조회하면 답이 달라집니다. 또한 문서에 명시된 경계를 지키세요. 재귀 CTE와 부작용이 없는 CTE가 아닌 경우에는 NOT MATERIALIZED가 무시됩니다. 적격하지 않은 CTE라면 지원되는 관계형 재작성 방식을 사용하거나 결과를 명시적으로 준비하세요. SELECT

여러 문장에 걸친 재사용에는 인덱스가 있는 후보 관계를 준비하기

CTE는 하나의 문장에만 적용됩니다. 여러 문장에서 같은 후보 행과 명시적 인덱스가 필요하다면 임시 테이블을 사용해 다른 수명과 인덱싱 방식을 선택할 수 있습니다. 원래 설정을 마친 뒤 다음 대안을 실행하세요.

CREATE TEMP TABLE candidate_demo AS
SELECT id, topic_id, title
FROM article_demo
WHERE id <= 2000;

CREATE INDEX candidate_demo_topic_id_idx
    ON candidate_demo (topic_id, id);

ANALYZE candidate_demo;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT c.id, c.title, m.highest_id
FROM candidate_demo AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate_demo AS x
    WHERE x.topic_id = c.topic_id
) AS m;

임시 테이블의 인덱스도 임시이며 관계는 세션 범위에 한정됩니다. 준비 작업이 도움이 되는지 평가할 때 생성, 인덱스 작성 및 분석을 포함하세요. 최종 조회만 준비 작업이 없는 전체 과정과 비교하지 마세요. CREATE TABLE

Mac에서 node-postgres로 동일한 실험을 캡처하기

애플리케이션 쿼리 경로를 확인하려면 현재 지원되는 Node.js 릴리스를 사용하고 문서에 나온 패키지를 npm install pg로 설치하세요. 아래 전체 프로그램을 해당 프로젝트의 diagnose.mjs로 저장한 뒤 node diagnose.mjs를 실행하세요. 이 프로그램은 자체 합성 입력을 생성하므로 앞에서 사용한 psql 세션에 의존하지 않습니다. node-postgres

프로그램은 기존 PostgreSQL 연결 환경 변수를 사용합니다. node-postgres는 psql과 같은 연결 환경 변수를 문서화하고 있으며, 여기에는 PGHOST, PGPORT, PGDATABASE이 포함됩니다. 연결

import pg from 'pg';
const { Client } = pg;
const client = new Client();

try {
  await client.connect();
  const version = await client.query('SHOW server_version');
  console.log(version.rows[0]);

  await client.query(`
    CREATE TEMP TABLE article_demo (
      id integer PRIMARY KEY,
      topic_id integer NOT NULL,
      title text NOT NULL
    );
    INSERT INTO article_demo (id, topic_id, title)
    SELECT g, 1 + (g % 50), 'Article ' || g
    FROM generate_series(1, 20000) AS s(g);
    CREATE INDEX article_demo_topic_id_idx
      ON article_demo (topic_id, id);
    ANALYZE article_demo;
  `);

  const sql = `
    WITH candidate AS MATERIALIZED (
      SELECT id, topic_id, title
      FROM article_demo
      WHERE id <= $1::integer
    )
    SELECT c.id, c.title, m.highest_id
    FROM candidate AS c
    CROSS JOIN LATERAL (
      SELECT max(x.id) AS highest_id
      FROM candidate AS x
      WHERE x.topic_id = c.topic_id
    ) AS m
  `;

  const result = await client.query({
    text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) ${sql}`,
    values: [2000]
  });

  for (const row of result.rows) {
    console.log(row['QUERY PLAN']);
  }
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await client.end();
}

쿼리 설정은 문서화된 text 및 values 필드를 사용합니다. name을 추가하면 각 연결에서 준비된 문장이 생성됩니다. 후보 행을 캐싱하거나 반복되는 내부 실행을 없애지는 않습니다. 쿼리

기존 애플리케이션에서 풀을 사용한다면 임시 테이블 생성과 사용이 동일한 대여 클라이언트에서 이뤄지도록 한 뒤 finally에서 클라이언트를 반환하세요. 별도의 pool.query 호출은 서로 다른 클라이언트를 사용할 수 있습니다. 마찬가지로 node-postgres에서는 트랜잭션의 모든 문장을 같은 클라이언트 인스턴스로 실행해야 합니다. 풀링, 트랜잭션

매개변수 값에 따라 동작이 달라지는 이름 지정 문장이라면 계획을 별도로 조사하세요. PostgreSQL은 범용 계획과 매개변수별 사용자 지정 계획 중 하나를 선택할 수 있습니다. 문서에서 안내하는 확인 방법은 준비된 문장에 EXPLAIN EXECUTE를 사용하는 것입니다. 따라서 새로 설명한 이름 없는 쿼리는 이미 실행 중인 준비 문장과 다른 결과를 보일 수 있습니다. PREPARE

반복 작업이 실제로 개선되었는지 판단하기

각 비교에서 같은 후보 조건, 선택 열과 입력값을 사용하세요. 먼저 한 번 실행한 뒤 비교 가능한 실행을 반복해 캐시의 변동을 살펴보세요. 서버 버전, 후보 수, 내부 스캔 실행 횟수, 캐시 동작 및 전체 실행 시간을 기록하세요. PostgreSQL 17에서는 열 통계와 정렬 순서를 활용해 CTE 계획 기능이 개선되었으므로 서로 다른 컴퓨터의 결과를 비교할 때 버전을 기록하세요. 릴리스 17

두 번째 진단에서는 EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)을 사용하면 노드별 시계 측정을 피하면서 행 수와 문장 실행 시간을 유지할 수 있습니다. 부모 노드의 버퍼 합계에는 자식 노드 활동이 포함되므로 전체 트리의 값을 합산하지 마세요. EXPLAIN ANALYZE는 쿼리를 실행하지만 결과 행을 Node.js로 전송하는 시간은 측정하지 않습니다. EXPLAIN

계획기 설정은 실험으로 취급하세요. enable_nestloop을 끄면 중첩 루프가 덜 선택되지만 완전히 제거되지는 않습니다. enable_material도 모든 구체화를 막을 수는 없습니다. 두 설정 모두 후보 소비가 효율적이라는 증거가 되지 않습니다. 쿼리 계획

마지막으로 필요한 행을 보존하면서 전체 작업에서 측정한 작업량을 줄이는 쿼리 형태를 선택하세요. 주제별 최댓값 작업에서는 그룹화하여 재사용하는 방식이 가장 명확한 첫 실험입니다. 인덱스 조회가 저렴한 것으로 확인되면 계속 사용하고, 반복 소비에서 추가 설정 비용을 상쇄할 수 있다면 명시적 준비 방식을 사용하세요.

출처