인덱스 사용 및 생성

PostgreSQL에서 쿼리 성능 개선하기

Amy McCarty

Instructor

인덱스 개요

무엇

검색 향상을 위한 정렬 키 생성 방법
책의 색인과 유사
데이터 위치 참조

왜

더 빠른 쿼리

어디에

자주 필터하는 열
기본 키

인덱스 예시

ingredient	recipe
tomatoes	spaghetti & meatballs
green onions	fried rice
eggs	fried rice
ground beef	spaghetti & meatballs
pasta	spaghetti & meatballs
rice	fried rice
soy sauce	fried rice

SELECT *
FROM cookbook
WHERE recipe = 'fried rice'

키와 포인터로서의 인덱스

두 테이블. 인덱스 테이블에는 recipe 열과 _12부터 _18까지 순차적인 포인터 열이 있음. 인덱스가 있는 테이블은 왼쪽 테이블에 포인터 열이 추가된 형태. 포인터 값은 두 테이블에서 동일함. 화살표가 인덱스 테이블의 볶음밥 네 값이 인덱스가 있는 테이블의 볶음밥 네 값과 어떻게 대응하는지 보여줌.

기존 인덱스 찾기

pg_tables

information_schema와 유사
- Postgres 전용
데이터베이스 메타데이터

기존 인덱스 찾기

pg_tables

information_schema와 유사
- Postgres 전용
데이터베이스 메타데이터

SELECT * FROM pg_indexes

schemaname	tablename	indexname	tablespace	indexdef
food	dinner	recipe_index	null	CREATE INDEX recipe_index ...

인덱스 생성하기

재료, 레시피, 1회분 열이 있는 식탁. 토마토 - 스파게티와 미트볼 - 4. 파 - 볶음밥 - 2. 계란 - 볶음밥 - 2. 다진 소고기 - 스파게티와 미트볼 - 4. 파스타 - 스파게티와 미트볼 - 4. 쌀 - 볶음밥 - 2. 간장 - 볶음밥 - 2.

CREATE INDEX recipe_index 
ON cookbook (recipe);

CREATE INDEX CONCURRENTLY recipe_index
ON cookbook (recipe, serving_size);

사용할지 말지

인덱스 사용 권장

대용량 테이블
자주 쓰는 필터 조건
기본 키

인덱스 지양

소용량 테이블
null 값이 많은 열
갱신이 잦은 테이블
- 인덱스 단편화 발생
- 데이터가 두 곳에 기록됨

갱신이 잦은 테이블

두 테이블. 인덱스 테이블에는 recipe 열과 _12부터 _19까지 순차적인 포인터 열이 있음. 인덱스가 있는 테이블은 왼쪽 테이블에 포인터 열이 추가된 형태. 포인터 값은 두 테이블에서 동일함. 화살표가 인덱스 테이블의 스파게티와 미트볼 네 값이 인덱스가 있는 테이블의 네 값과 어떻게 대응하는지 보여줌. 세 값은 인덱스 테이블 상단에 정렬되어 있고, 하나는 하단(인덱스가 있는 테이블의 새 basil 항목에 해당).

인덱스 쿼리 평가

쿼리 플래너

큰 냄비 주변의 여러 요리사

EXPLAIN
SELECT * 
FROM cookbook

쿼리 계획

Seq scan on cookbook (cost=0.00...22.70
  rows = 1270 width = 36)

비용(시간) 추정치

Passons à la pratique !

PostgreSQL에서 쿼리 성능 개선하기