사진 2,000장에 갤러리가 5.6초 걸렸습니다: SQLite FTS5 CTE에 MATERIALIZED 한 단어를 붙인 기록

인스타그램 카드를 만드는 개인 편집 도구(비공개)에 사진 갤러리를 붙인 지 사흘 만에, 갤러리 대화상자가 열릴 때마다 초 단위로 멈추기 시작했습니다.

원인은 SQLite가 전문 검색(FTS5) CTE를 LEFT JOIN 안으로 펼쳐서 갤러리 행마다 MATCH를 처음부터 다시 실행한 것이었습니다. CTE 두 개에 AS MATERIALIZED를 붙이자 합성 사진 2,000장 기준으로 인물 카드 질의가 5,624ms에서 40ms로 줄었습니다.

다만 이 글의 숫자는 측정할 때마다 크게 달랐습니다. 그 부분은 뒤에서 미결로 남깁니다.

갤러리가 하는 일#

도구는 Python 표준 라이브러리 HTTP 서버 하나와 SQLite 파일 하나로 돕니다. 사진은 gallery 표에 쌓이고, 같은 rowid를 쓰는 FTS5 표 gallery_fts(trigram 토크나이저라서 한글 부분 문자열도 찾습니다)가 파일 제목과 설명을 색인합니다.

카드에서 "갤러리에서 고르기"를 누르면 검색어 없이도 갤러리 전체를 "그 카드와 가장 비슷한 순"으로 보여 줍니다.

파일 제목에 인물 이름이 있는 사진, 엔진이 후보로 올린 적 있는 사진, 제목·설명에 카드 낱말이 있는 사진 순서입니다. 이 마지막 두 조건을 CTE m과 k가 전문 검색으로 계산하고, 본 질의가 gallery를 기준으로 둘을 LEFT JOIN합니다.

WITH m AS (SELECT rowid, bm25(gallery_fts, 5.0, 1.0, 3.0, 0.5, 0.5) b
           FROM gallery_fts WHERE gallery_fts MATCH ?),
     k AS (...)
SELECT g.* FROM gallery g
LEFT JOIN m ON m.rowid = g.rowid
LEFT JOIN k ON k.rowid = g.rowid
WHERE ... ORDER BY ... LIMIT ? OFFSET ?

배포한 갤러리는 10월 10일에 667장이었고 11일 수집에서 103장이 더해졌습니다. 하루에 100~400장씩 늘어나는 구조입니다. 서버는 4코어 ARM VPS입니다.

질의 계획에 행마다 도는 MATCH가 보였습니다#

EXPLAIN QUERY PLAN을 돌리자 SCAN g 바로 아래에 이 줄이 나왔습니다.

SCAN gallery_fts VIRTUAL TABLE INDEX 0:=M5 LEFT-JOIN

gallery를 한 줄씩 훑으면서 그 행마다 가상 표를 다시 연다는 뜻입니다.

SQLite는 힌트가 없으면 CTE를 서브쿼리처럼 본 질의에 펼칠 수 있고(query flattening), 여기서는 그 결과로 전문 검색이 행 수만큼 실행됐습니다.

사진이 늘면 바깥 루프도 길어지고 안쪽 검색 대상도 커지니 제곱으로 느려집니다.

합성 사진으로 잰 숫자는 이랬습니다(10월 12일 분석 단계).

두 방식의 첫 결과 id는 같았습니다.

고친 것은 한 줄입니다#

CTE를 만드는 문자열에 MATERIALIZED를 넣었습니다. 정렬 순서와 인자 바인딩 순서는 건드리지 않았습니다.

# MATERIALIZED: each full-text match runs once. Flattened into the LEFT JOIN, SQLite ran it again for every gallery
# row (2026-10-13: 2,000 photos took 5.6 s, 10,000 took 146 s)
with_ = ", ".join(f"{n} AS MATERIALIZED ({s})" for n, (s, _) in ctes.items())

바꾼 뒤 질의 계획은 MATERIALIZE m으로 시작하고, 행마다 하던 가상 표 검색이 자동 인덱스 검색으로 바뀌었습니다.

전문 검색이 한 번으로 줄자 그다음 비용이 보였습니다. 순위가 행마다 부르는 name_in()이 제목의 문자를 하나씩 정규식으로 검사하고 있었습니다. 이름이 제목에 아예 없으면 단어 경계를 보기 전에 바로 거짓을 내도록 앞단 검사를 넣었습니다.

정리 전후 결과는 무작위 이름·제목 204,748쌍에서 모두 같았습니다.

회귀를 막는 검사#

자체 검사(selfcheck.py)에 합성 사진 2,000장을 더하고, 인물 카드·장면 카드·검색어 질의가 각각 0.3초 안에 끝나는지 봅니다.

같은 검사가 MATERIALIZED를 지운 질의로 한 번 더 돌려서 상위 60장의 순서가 같은지도 확인합니다. 지운 질의를 SQL 문자열 치환으로 만드는 작은 래퍼를 썼습니다.

일부러 MATERIALIZED를 빼고 돌리면 이 검사가 실패합니다. 그때 시간은 인물 1.53초, 장면 1.09초, 검색 0.79초였습니다. 검사는 163개 모두 통과했습니다.

미결: 측정할 때마다 열 배씩 달랐습니다#

구현 단계(10월 13일)에서 다시 잰 숫자는 분석 단계와 많이 달랐습니다. 힌트 없는 2,000장 인물 카드 질의가 512ms였고 MATERIALIZED로 38ms가 됐습니다. 4,000장은 4,225ms에서 63ms였습니다.

분석 단계에서 같은 2,000장이 5,624ms였으니 열 배 넘게 차이가 납니다.

두 측정 모두 합성 데이터였지만, 두 번 사이에 무엇이 달랐는지는 기록에 남기지 않았습니다. 그래서 어느 쪽이 실제 VPS에 가까운지는 지금도 모릅니다.

모양은 두 측정이 같았습니다. 구현 단계에서는 사진이 두 배 늘 때 힌트 없는 질의가 8.3배 느려졌고, 분석 단계에서는 다섯 배 늘 때 26배 느려졌습니다. MATERIALIZED 쪽은 두 배 늘 때 1.7배였습니다.

이 글을 쓰면서 로컬 Mac(SQLite 3.53.4)에 CTE 하나만 있는 최소 재현을 만들어 다시 쟀습니다. 앱 질의보다 정렬 조건이 단순해서 절대값은 훨씬 작습니다.

최소 재현에서도 질의 계획은 같았습니다. 힌트 없는 쪽은 SCAN gallery_fts VIRTUAL TABLE INDEX 0:=M2 LEFT-JOIN, 힌트를 붙인 쪽은 MATERIALIZE m 뒤에 SEARCH m USING AUTOMATIC COVERING INDEX (rowid=?) LEFT-JOIN이 나왔습니다. 사진 수가 2.5배 늘 때(4,000장에서 10,000장) 시간은 6배 늘었습니다.

공식 문서가 말하는 것#

SQLite 문서의 Materialization Hints 절은 AS MATERIALIZED가 PostgreSQL에서 가져온 비표준 문법이고, SQLite 3.35.0(2021-03-12)부터 쓸 수 있다고 적습니다.

구체화하면 펼치기와 push-down 최적화를 잃는데, 문서는 이것을 버그가 아니라 기능이라고 하며 "optimization fence"로 쓰라고 합니다. 동시에 강한 이유가 없으면 힌트를 쓰지 말라고도 적어 두었습니다.

저는 이번 경우가 그 강한 이유에 해당한다고 봅니다. 결과가 작은 전문 검색을 훨씬 큰 표에 LEFT JOIN하는 모양이고, 펼쳤을 때 손해가 사진 수의 제곱으로 커지기 때문입니다.

다만 모든 CTE에 습관처럼 붙일 생각은 없습니다. 질의 계획에서 가상 표 줄에 LEFT-JOIN이 붙어 있는지 먼저 보고, 그때만 붙입니다.