Series EXPLAIN ANALYZE로 쿼리 분석하기

PostgreSQL로 쿼리 분석해보기 - 1 편 시나리오 생성

본 글은 실제 프로젝트에서 '속도'를 고려하며 쿼리를 작성했던 경험에서 실제로 Performance를 검증해본 포스트입니다.
https://velog.io/@goat_hoon/SQL-서브쿼리로-연산속도를-늘릴-수만-있다면-추측글

ORM은 개발자들에게 매우 편리한 수단입니다. 어플리케이션 개발 언어로 SQL을 작성해준다고? 완전 개꿀이죠!
하지만 DBA분들에게 ORM은 꽤나 실무에서 골칫덩이라고 합니다. 데이터베이스 성능을 저하시킬 수 있는 가능성이 있기 때문이죠.
ORM을 잘 사용하는 것. 개발 생산성에 매우 지대한 영향을 미치기 때문에 중요한 요소입니다.
하지만 그것보다 더 중요한 것은 ORM의 기저에 깔려있는 '근본' SQL 아닐까요?
그래서 준비한 칼럼 제 1편 '여러분들은 '속도'를 고려하며 쿼리를 작성하고 계신가요?' 입니다.
특정 시나리오 상황에서 어떤 쿼리가 최적의 쿼리인지 직접 비교해보겠습니다. (사용된 DBMS는 PostgreSQL입니다.)

1. 시나리오 가정

1) 요구사항

특정 게시물에 달린 댓글들의 List를 받아옵니다. (대댓글x)
이 때, 반환되는 댓글들 List의 원소들, 즉 댓글 한개마다 로그인한 유저가 '좋아요'를 했는지 아닌지 알 수 있는 쿼리를 작성해주세요.

- 테이블 정보

(comment.info) comment 스키마에 속한 info 테이블
(member.like_comment) member 스키마에 속한 like_comment 테이블

- 상황 가정

어떠한 테이블도 FK를 가지고 있지 않습니다. (update, delete를 하지 않기 때문에 요구상황을 구현하는데 문제는 없습니다. 시나리오를 위한 세팅 또한 무결성을 해치지 않게 구성하였습니다.)
게시물의 타입은 두가지입니다. ('BLOG' , 'QUESTION')
게시물의 타입별 post_id는 해당 게시물 타입에서 유일합니다.
comment의 수는 포스트 타입별로 100만개, 등록된 유저의 수도 100만명이라고 가정합니다.
member.like_comment에 등록된 row의 개수는 100만 + 1개입니다. (유저별로 한개의 댓글을 좋아요했다고 가정.
왜 100만 + 1개냐구요? 8번 상황을 고려하기 위해서 하나 더 추가한 row입니다.)
post type 'BLOG' 의 '1'번 게시글에 해당하는 댓글 리스트를 가져옵니다.
로그인한 사용자의 user_id 는 '1' 이라고 가정합니다.
user_id '1'의 사용자는 'BLOG'의 '1'번 게시글의 댓글중 좋아요한 댓글이 적어도 한개 있습니다.

2) 시나리오 세팅

1. comment.info 테이블

--- schema 생성 ---
CREATE SCHEMA comment;

--- table 생성 ---
CREATE table comment.info (
	id serial4 primary key,
	author_id int8 NOT NULL,
	author_image_path varchar(300) NULL,
	author_name varchar(15) NOT NULL,
	post_type varchar NOT NULL,
	post_id int8 NOT NULL,
	recomments int8 NOT NULL DEFAULT 0,
	comment_text text NOT NULL,
	created_date timestamp NULL DEFAULT CURRENT_TIMESTAMP,
	updated_date timestamp NULL DEFAULT CURRENT_TIMESTAMP
);

2. member.like_comment 테이블

--- schema 생성 ---
CREATE SCHEMA member;

--- table 생성 ---
CREATE TABLE member.like_comment (
	id serial4 primary key,
	user_id int8 NOT NULL,
	comment_id int8 NOT NULL,
	is_recomment bool NOT NULL,
	CONSTRAINT unique_columns UNIQUE (user_id, comment_id, is_recomment)
);

like_comment 테이블에서 'id' 가 pk이지만, UNIQUE 제한을 건 user_id, comment_id, is_recomment를 묶은 Composite Key를 사용해도 무방합니다.
또한 PK와 UNIQUE column 에 대해서 자동으로 INDEX처리 해준다는 사실도 알고 가시면 좋습니다.

3. table에 정보를 insert 해봅시다.

100만개의 data를 table에 insert하는 방법은 많겠지만,
python을 이용한 간단한 script로 insert를 해봅시다.

Comment 정보 저장

import psycopg2
from psycopg2 import extras
import random

# PostgreSQL 데이터베이스 연결 설정
conn = psycopg2.connect(
    host="localhost", 
    database="postgres",
    user="",
    password=""
)

# 커서 생성
cursor = conn.cursor(cursor_factory=extras.RealDictCursor)
# 무작위 post_id 생성 함수
def generate_random_post_id():
    return random.randint(1, 1000001)

# 100만개의 데이터를 생성하여 삽입하는 예시
for i in range(1, 1000001):
    cursor.execute("INSERT INTO comment_info (author_id, author_name, post_type, post_id, comment_text) VALUES (%s, %s, %s, %s, %s)",
                   (i, '테스트' + str(i), 'BLOG', generate_random_post_id(), '테스트 댓글입니다'))

    cursor.execute("INSERT INTO comment_info (author_id, author_name, post_type, post_id, comment_text) VALUES (%s, %s, %s, %s, %s)",
                   (1000001 - i, '테스트' + str(1000001 - i), 'QUESTION', generate_random_post_id(), '테스트 댓글입니다'))

# 변경 내용을 커밋
conn.commit()

# 커넥션과 커서 닫기
cursor.close()
conn.close()

최대한 실제와 유사한 상황을 만들기 위해서, 무작위의 post id에 댓글을 작성하는 상황을 가정해보았습니다.
그럼 'BLOG' 타입, 'QUESTION' 타입의 포스트에 각각 100만개의 댓글이 작성됩니다.
이 때, comment.info.id는 like_comment 테이블의 comment_id 와 동일합니다.

random으로 생성했는데, 우연히 요구사항에 맞게 post_id 가 1이고, post_type이 'BLOG'인 댓글이 두개가 존재하네요! (없으면 임의로 insert해서 생성해보도록 합시다)
이 id를 like_comment 테이블에서 user_id가 1인 친구가 1095,567번의 comment_id 를 좋아한다는 정보를 추가해줍시다.

like 정보 저장

import psycopg2
from psycopg2 import extras
import random

# PostgreSQL 데이터베이스 연결 설정
conn = psycopg2.connect(
    host="localhost",
    database="postgres",
    user="",
    password=""
)

# 커서 생성
cursor = conn.cursor(cursor_factory=extras.RealDictCursor)
# 무작위 post_id 생성 함수
def generate_random_post_id():
    return random.randint(1, 1000001)

# 100만개의 데이터를 생성하여 삽입하는 예시
for i in range(1, 1000001):
    cursor.execute("INSERT INTO member.like_comment (user_id, comment_id, is_recomment) VALUES (%s, %s, %s)",
                   (i, generate_random_post_id(), False))
# 요구사항 8번에 맞게 정보 insert    
cursor.execute("INSERT INTO member.like_comment (user_id, comment_id, is_recomment) VALUES (%s, %s, %s)",
               (1,1095567,False))

# 변경 내용을 커밋
conn.commit()

# 커넥션과 커서 닫기
cursor.close()
conn.close()

그럼 준비는 끝났습니다 !

PostgreSQL

ORM

Series 1 / 3