기사 본문을 선택하면 인덱스 전용 스캔이 불가능해질 수 있습니다. PostgreSQL이 인덱스에 없는 열을 가져와야 하기 때문입니다. 이 가이드에서는 Mac에서 이러한 변화를 진단하고 Node.js에서 그 영향을 측정하는 방법을 설명합니다. 인덱스 전용 스캔 요건.

선택하는 열을 바꾸면 계획이 달라지는 이유는 무엇인가요?

id, category, body 열이 있는 기사 테이블과 (category, id)에 대한 B-tree 인덱스를 생각해 보세요. 카테고리별 ID를 선택하는 쿼리는 필터 값과 결과를 모두 해당 인덱스에서 가져올 수 있습니다. body를 추가하면 인덱스 전용 스캔에 그 인덱스만으로는 부족해집니다. PostgreSQL 인덱스는 힙이라고 하는 테이블의 기본 저장소와 분리되어 있습니다. 일반 인덱스 스캔은 테이블 데이터도 가져옵니다. 인덱스 전용 스캔과 커버링 인덱스.

이제 플래너는 필요한 테이블 행을 가져오는 여러 방법을 비교합니다. Index Scan을 유지하거나, Bitmap Index Scan과 Bitmap Heap Scan을 조합하거나, Seq Scan을 선택할 수 있습니다. 비트맵 스캔은 물리적 위치를 기준으로 힙 접근을 묶습니다. 분산된 여러 행을 가져올 때는 각각 가져오는 것보다 테이블을 스캔하는 편이 저렴할 수 있습니다. 최상위 스캔 노드에 힙이 표시되더라도 비트맵 계획은 여전히 인덱스를 사용합니다. EXPLAIN 사용하기.

또한 원래 계획을 주의 깊게 확인하세요. Index Only Scan에 0이 아닌 Heap Fetches가 표시될 수 있습니다. PostgreSQL은 가시성을 확인하기 위해 때때로 테이블 행을 방문해야 합니다. 이러한 방문을 피하려면 필요한 열이 인덱스에 있어야 할 뿐 아니라 테이블의 가시성 맵도 적절해야 합니다. 인덱스 전용 스캔의 가시성 확인.

Mac에서 비교 재현하기

로컬 PostgreSQL 데이터베이스가 실행 중이고, 현재 지원되는 Node.js 릴리스와 npm이 설치되어 있어야 합니다. 기본 예제는 PostgreSQL 14~18에서 사용할 수 있는 기능을 사용합니다. 조회한 최신 PostgreSQL 설명서는 버전 18이라고 표시되어 있습니다. 아래의 추가 직렬화 진단 기능을 사용하려면 PostgreSQL 17 이상이 필요합니다. node-postgres 설명서에는 Node.js 호환성과 설치 명령이 나와 있습니다. PostgreSQL 설명서, node-postgres 설치 및 호환성.

Terminal을 열고 새 폴더에서 작업하세요. 다음 명령으로 프로젝트를 만들고 데이터베이스 드라이버를 설치합니다.

mkdir article-plan-lab
cd article-plan-lab
npm init -y
npm install pg

프로그램은 PGHOST, PGPORT, PGDATABASE, PGUSER을 사용해 기존 로컬 연결 설정을 읽습니다. 이미 연결할 수 있는 데이터베이스에 맞게 설정하세요. node-postgres는 명시적인 연결 옵션 없이 Client를 만들 때 이 환경 변수를 읽습니다. 환경 변수로 연결하기.

다음 전체 프로그램을 article-plan.cjs으로 저장하세요. 임시 테이블에 테스트용 행을 만들고, 인덱스를 생성하고, 두 실행 계획을 출력한 다음 일반 쿼리 두 개를 측정합니다. 두 비교 모두 같은 연결과 카테고리 값을 사용합니다. 쿼리 설정에는 문서화된 node-postgres API에 따라 text에 SQL을, values에 매개변수를 지정합니다. 쿼리 설정.

const { Client } = require('pg');
const { performance } = require('node:perf_hooks');

async function main() {
  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_plan_demo (
      id integer PRIMARY KEY,
      category integer NOT NULL,
      body text NOT NULL
    )`);
    await client.query(`INSERT INTO article_plan_demo
      SELECT g, g % 10, repeat('Synthetic article paragraph. ', 40)
      FROM generate_series(1, 50000) AS s(g)`);
    await client.query(`CREATE INDEX article_plan_demo_category_id
      ON article_plan_demo (category, id)`);
    await client.query('VACUUM (ANALYZE) article_plan_demo');

    const queries = [
      ['IDs', 'SELECT id FROM article_plan_demo WHERE category = $1'],
      ['Bodies', 'SELECT id, body FROM article_plan_demo WHERE category = $1']
    ];

    for (const [label, text] of queries) {
      const plan = await client.query({
        text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE) ${text}`,
        values: [3]
      });
      console.log(label);
      console.log(plan.rows.map(row => row['QUERY PLAN']).join('\n'));

      const start = performance.now();
      const result = await client.query({ text, values: [3] });
      const elapsed = performance.now() - start;
      const bodyBytes = result.rows.reduce((sum, row) =>
        sum + (row.body === undefined ? 0 : Buffer.byteLength(row.body)), 0);
      console.log({
        rows: result.rows.length,
        elapsedMs: elapsed.toFixed(2),
        bodyBytes
      });
    }
  } finally {
    await client.end();
  }
}

main().catch(error => {
  console.error(error.message);
  process.exitCode = 1;
});

같은 폴더에서 실행하세요.

node article-plan.cjs

설정 과정에서는 VACUUM (ANALYZE)을 별도 명령으로 보냅니다. 이 명령은 vacuum 작업 뒤에 통계 수집을 수행하며 트랜잭션 블록 안에서는 실행할 수 없습니다. 예제에 추가하는 트랜잭션 래퍼 밖에서 실행하세요. VACUUM.

JavaScript 구문 검사를 통과했고, 별도의 결정론적 계산으로 테스트 데이터의 행 수와 본문 바이트 총량을 확인했습니다. 조사 환경에서는 PostgreSQL을 사용할 수 없어 데이터베이스 쿼리를 검토만 하고 실행하지는 못했습니다. 특정 계획이나 실행 시간을 실제 관측 결과인 것처럼 제시하지 않습니다.

계획을 유용한 순서로 읽기

먼저 스캔 이름을 확인한 다음 추정 행 수와 실제 행 수를 비교하세요. 그다음 테이블 접근과 버퍼 활동을 살펴보세요. 이 순서를 따르면 사용 가능한 접근 방법의 변화와 부정확한 추정 또는 비용이 큰 결과를 구분할 수 있습니다. VERBOSE은 각 노드의 출력 열도 보여 줍니다. EXPLAIN 사용하기, EXPLAIN 옵션.

관찰 내용 해석 다음 확인 사항
ID 쿼리는 Index Only Scan, 본문 쿼리는 Index Scan 사용 본문 쿼리에 테이블 데이터가 필요함 버퍼와 일반 쿼리 실행 시간 비교
본문 쿼리는 Bitmap Heap Scan 사용 자식 노드에서 인덱스 사용 여부 확인 힙 블록 수와 반환 행 수 비교
본문 쿼리는 Seq Scan 사용 플래너가 테이블 스캔을 선호함 일치하는 행의 추정치와 실제 수 비교
ID 쿼리에 상당한 Heap Fetches가 표시됨 인덱스 전용 실행도 힙을 방문함 최근 테이블 변경의 영향인지 확인
추정치와 실제 행 수가 크게 다름 통계가 데이터를 제대로 반영하지 못할 수 있음 통계를 수집한 뒤 다시 비교

ANALYZE는 플래너가 계획을 선택하는 데 사용하는 통계를 수집합니다. 테스트용 카테고리 분포를 변경하면 다음 비교를 해석하기 전에 통계를 다시 수집하세요. 표본 추출 때문에 실행할 때마다 추정치가 조금씩 달라질 수 있습니다. ANALYZE.

width는 행당 예상 출력 바이트 수이지, 전송된 바이트를 측정한 값이 아닙니다. 마찬가지로 플래너 비용은 밀리초가 아닌 상대 단위로 표시됩니다. 버퍼 적중은 PostgreSQL 캐시에서 블록을 찾았다는 뜻입니다. 아무 작업도 일어나지 않았다는 뜻은 아닙니다. 계획 추정치, 버퍼 보고.

이 테스트 데이터에서는 두 쿼리가 모두 5,000개 행을 반환해야 합니다. 테스트용 본문은 각각 ASCII 1,160바이트이므로 본문 쿼리는 ID와 프로토콜 오버헤드를 제외하고 본문 바이트 5,800,000바이트, 즉 약 5.53 MiB를 반환합니다. 이는 샘플 입력을 바탕으로 계산한 값이지 데이터베이스 성능 측정값이 아닙니다. 프로그램의 bodyBytes 필드에서 이 차이를 확인할 수 있습니다.

일반 쿼리는 계획 측정 후 실행되므로, 쿼리 실행 시간은 캐시가 예열된 실행 순서를 반영합니다. 작은 시간 차이를 근거로 결론을 내리기 전에 비교를 반복하고 실행 순서를 번갈아 바꿔 보세요. 한 번 실행하면 진단에는 도움이 되지만, 신뢰할 수 있는 성능 비율을 입증하지는 못합니다.

본문을 가져오는 시간이 EXPLAIN에서 예상한 것보다 긴 이유는 무엇인가요?

큰 텍스트 값은 압축되거나 PostgreSQL의 TOAST 메커니즘을 통해 기본 테이블 바깥에 저장될 수 있습니다. 따라서 행의 기본 항목을 가져오는 작업과 클라이언트에 전달할 전체 본문을 만드는 작업은 필요한 양이 서로 다를 수 있습니다. 위의 짧고 반복적인 테스트 데이터는 열 포함 여부를 보여 주기 위한 것으로, 모든 본문 저장 방식을 대표하지는 않습니다. TOAST 저장.

기본 설정에서 EXPLAIN ANALYZE는 쿼리 결과를 전달용으로 변환하지 않고 버립니다. PostgreSQL 17 이상에서는 SERIALIZE TEXT를 추가해 출력 변환을 조사할 수 있습니다. 필요한 경우 외부 저장 값도 가져옵니다. 이 옵션은 PostgreSQL 17에서 도입되었습니다. EXPLAIN 직렬화 동작, PostgreSQL 17 릴리스 노트.

사용하려면 프로그램의 기존 EXPLAIN 템플릿 줄을 다음 코드 조각으로 바꾸고 전체 프로그램을 다시 실행하세요.

text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SERIALIZE TEXT) ${text}`,

PostgreSQL 14~16에서는 원래 줄을 유지하고 일반 쿼리 타이머로 결과를 받는 시간을 측정하세요. SERIALIZE은 여전히 결과 데이터를 클라이언트로 보내지 않으므로 전송 시간이나 JavaScript 처리 시간을 측정할 수 없습니다. EXPLAIN의 한계.

node-postgres는 일반적으로 결과 행을 JavaScript 객체로 수집합니다. rowMode: 'array' 옵션은 결과 표현 방식을 바꾸지만 SQL 결과에서 선택한 본문 값을 제거하지는 않습니다. 따라서 쿼리에 body을 추가해 사라진 열 포함 범위를 복구할 수 없습니다. 행 모드.

기사 작업에 맞는 변경 선택하기

데스크톱 작업에 ID 목록만 필요하면 선택 열을 최소화하세요. 전체 기사 텍스트가 필요하다면 본문 쿼리의 실제 실행 시간과 결과 크기를 평가하세요. 진단의 목적은 요청된 결과를 유지하면서 불필요한 작업을 찾아내는 것입니다.

카테고리의 기사 중 처음 50개만 표시하는 화면이라면 요청 결과를 제한하는 것이 효과적인 대안입니다. 프로그램의 queries 배열에서 본문 쿼리 문자열을 다음 SQL로 바꾸세요.

SELECT id, body
FROM article_plan_demo
WHERE category = $1
ORDER BY id
LIMIT 50

이렇게 하면 일치하는 모든 기사를 가져오는 작업이 정렬된 한 페이지를 가져오는 작업으로 바뀝니다. 카테고리가 고정되어 있을 때 기존 (category, id) 인덱스는 ID 순서로 가져오는 후보 경로를 제공합니다. 일치하는 B-tree 인덱스가 있으면 실행이 충분한 행을 찾는 즉시 멈출 수 있어 ORDER BY와 LIMIT을 효율적으로 처리할 수 있습니다. 어떤 계획이 선택되는지는 계속 확인해야 합니다. 인덱스와 ORDER BY.

INCLUDE (body)을 자동 해결책으로 취급하지 마세요. 포함 열은 테이블 데이터를 중복 저장해 인덱스를 키우며, 인덱스 튜플이 크기 제한을 넘으면 삽입이 실패할 수 있습니다. 작은 페이로드 열은 측정 실험의 후보로 유용할 수 있습니다. INCLUDE는 PostgreSQL 11에서 도입되었으며 그 이전 버전에서는 사용할 수 없습니다. 커버링 인덱스 주의 사항, PostgreSQL 11 릴리스 노트.

조사를 마무리할 때는 다음 세 가지를 구체적으로 확인하세요. 본문을 선택해 인덱스 전용 스캔을 사용할 수 없게 되었는지, 새 스캔의 성능이 적절한지, 전체 결과가 Node.js에 도달하는 데 시간이 얼마나 걸리는지입니다. 이 결과를 바탕으로 현재 계획을 유지할지, 요청하는 기사 수를 줄일지, 실제 쿼리에 다른 인덱스를 시험할지 결정할 수 있습니다.

출처