PostgreSQL 실행 계획 보는 팁과 테스트 데이터 생성

PostgreSQL 실행 계획

PostgreSQL 17버전에서 실행 계획을 효율적으로 보는 방법과 테스트 데이터로 INDEX 성능 테스트 하는 방법에 대해서 알아보자.

 

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

 

보통 EXPLAIN 으로 실행계획을 보면 되지만 Datagrip 사용하는 필자는 귀찮아서 단축키 커스텀마이징 설정함 ㅎㅎ

 

1. 쿼리 실행 계획 순서 보는 방법

• 동일 위치에선 위에서 아래로 실행되며
• 그룹 내에 계층구조에서는 가장 아래부터 시작합니다.

실제로 엄청 긴 실행 계획을 보면 어지러우니
가장 쉽게 보는 방법은 역시 툴을 사용하는 방법입니다..

밑에 링크 들어가서 json 형식이나 실행계획 긁어서 복붙하면

explain.dalibo

위에 사진 처럼 Cost, Rows, 시간 INDEX 종류 자세하게 알려줍니다.
이상 꿀팁 끝!!

2. 테스트 데이터로 인덱스 활용해보기

create table test_order(
    id          bigserial not null constraint test_order_pk primary key,
    customer_id varchar(20),
    comment     varchar(100),
    order_date  timestamp with time zone
);

CREATE TABLE test_order_detail(
    id         bigserial not null constraint test_order_detail_pk primary key,
    order_id   BIGINT    NOT NULL,
    product_id BIGINT    NOT NULL,
    comment    varchar(100),
    amount     BIGINT
);

CREATE INDEX idx_test_order_detail_01 ON test_order_detail (order_id);
CREATE INDEX idx_test_order_detail_02 ON test_order_detail (product_id);

CREATE TABLE test_product(
    id   bigserial    not null constraint test_product_pk primary key,
    name VARCHAR(100) NOT NULL
);

insert into test_order (customer_id, comment, order_date)
select 'C' || mod(i, 10) as customer_id, 
       lpad('X', 10, 'Y') as comment, 
       timestamp '1970-01-01 00:00:01' + random() * (timestamp '1970-01-01 00:00:01' - timestamp '2021-05-23 23:59:59') as order_date
from generate_series(1, 1000000) a(i);

INSERT INTO test_order_detail (order_id, product_id, comment, amount)
SELECT mod(i, 1000000) as order_id, 
       MOD(i, 5) as product_id, 
       lpad('X', 10, 'Y') as comment, 
       case when i < 1000 then i * 100 else i end as amount
FROM generate_series(1, 10000000) a(i);

INSERT INTO test_product (id, name)
SELECT product_id, MAX(order_id) || 'TEST_NAME'
FROM test_order_detail
GROUP BY product_id;

간편하게 위 테스트 데이터 만들어서 LOOP, JOIN 문 연습하자.