PostgreSQL: 표현식 인덱스

PostgreSQL

PostgreSQL: 표현식 인덱스

dewstream 2025. 2. 19. 08:00

※ PostgreSQL: Indexes on Expressions.

안녕하세요. 듀스트림입니다.

오늘은 표현식(Expressions) 인덱스에 대해 알아보겠습니다.

11.7. Indexes on Expressions

11.7. Indexes on Expressions # An index column need not be just a column of the underlying table, but can be …

www.postgresql.org

1. 표현식 인덱스?

표현식 인덱스는 하나 이상의 열에 함수나 계산식을 적용한 결과를 인덱싱합니다.

단순히 컬럼 자체가 아니라 그 컬럼에 특정 연산을 수행한 값을 미리 계산하여 인덱스로 저장하는 방식입니다.

예를 들면 아래와 같이 사용할 수 있습니다.

케이스 무시 검색:
이메일 주소의 대소문자 구분 없이 검색할 때 lower(email)과 같이 인덱싱할 수 있습니다.
시간 단위 변환:
날짜나 시간 데이터를 특정 단위(예: 일, 월)로 변환하여 검색할 수 있습니다.
복잡한 데이터 타입:
JSONB와 같이 구조화된 데이터에서 특정 키 값을 추출해 인덱싱할 수 있습니다.

2. 표현식 인덱스 테스트

1에서 작성한 3가지 예를 테스트해 보겠습니다.

2.1. 테스트 테이블 및 데이터 생성

▸ 테이블 생성

-- 사용자 테이블 생성
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- 주문 테이블 생성
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date TIMESTAMP NOT NULL,
    customer_id INTEGER NOT NULL,  -- 필요 시 users 테이블과 FOREIGN KEY 연결 가능
    total_amount NUMERIC(10,2) NOT NULL
);


-- 제품 테이블 생성 (JSONB 데이터 사용)
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    json_data JSONB NOT NULL
);

▸ 데이터 삽입

-- users 테이블 100만 건 INSERT
INSERT INTO users (email, username, created_at)
SELECT
  'user' || i || '@example.com' AS email,
  'user' || i AS username,
  NOW() - (random() * interval '365 days') AS created_at
FROM generate_series(1, 1000000) AS gs(i);


-- orders 테이블 100만 건 INSERT
INSERT INTO orders (order_date, customer_id, total_amount)
SELECT
  NOW() - (random() * interval '30 days') AS order_date,
  (floor(random() * 1000000) + 1)::integer AS customer_id,
  round((random() * 1000)::numeric, 2) AS total_amount
FROM generate_series(1, 1000000) AS gs(i);


-- products 테이블 100만 건 INSERT
INSERT INTO orders (order_date, customer_id, total_amount)
SELECT
  NOW() - (random() * interval '30 days') AS order_date,
  (floor(random() * 1000000) + 1)::integer AS customer_id,
  round((random() * 1000)::numeric, 2) AS total_amount
FROM generate_series(1, 1000000) AS gs(i);

2.2. 인덱스 생성 전후 테스트 결과

2.2.1. 이메일 대소문자 무시 인덱스 테스트

▸ 인덱스 생성 전

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE lower(email) = lower('uSer500000@example.com');

--- QUERY PLAN ---
 Gather  (cost=1000.00..17095.01 rows=5000 width=44) (actual time=139.153..143.143 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on users  (cost=0.00..15595.01 rows=2083 width=44) (actual time=113.503..135.434 rows=0 loops=3)
         Filter: (lower((email)::text) = 'user500000@example.com'::text)
         Rows Removed by Filter: 333334
 Planning Time: 0.575 ms
 Execution Time: 143.178 ms
(8 rows)

▸ 인덱스 생성

-- 1. 이메일 대소문자 무시 인덱스
CREATE INDEX idx_lower_email ON users ((lower(email)));

▸ 인덱스 생성 후

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE lower(email) = lower('uSer500000@example.com');

--- QUERY PLAN ---
 Index Scan using idx_lower_email on users  (cost=0.42..8.44 rows=1 width=44) (actual time=0.053..0.054 rows=1 loops=1)
   Index Cond: (lower((email)::text) = 'user500000@example.com'::text)
 Planning Time: 0.141 ms
 Execution Time: 0.069 ms
(4 rows)

2.2.2. 주문 날짜의 일(day) 단위 인덱스 테스트

▸ 인덱스 생성 전

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE date_trunc('day', order_date) = date_trunc('day', now() - interval '15 days');

--- QUERY PLAN ---
 Gather  (cost=1000.00..18228.02 rows=5000 width=22) (actual time=0.536..148.826 rows=33343 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on orders  (cost=0.00..16728.02 rows=2083 width=22) (actual time=0.060..140.166 rows=11114 loops=3)
         Filter: (date_trunc('day'::text, order_date) = date_trunc('day'::text, (now() - '15 days'::interval)))
         Rows Removed by Filter: 322220
 Planning Time: 0.711 ms
 Execution Time: 150.614 ms
(8 rows)

▸ 인덱스 생성

-- 2. 주문 날짜의 일(day) 단위 인덱스
CREATE INDEX idx_order_date_day ON orders ((date_trunc('day', order_date)));

▸ 인덱스 생성 후

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE date_trunc('day', order_date) = date_trunc('day', now() - interval '15 days');

--- QUERY PLAN ---
 Bitmap Heap Scan on orders  (cost=361.86..8450.61 rows=32700 width=22) (actual time=7.741..22.189 rows=33347 loops=1)
   Recheck Cond: (date_trunc('day'::text, order_date) = date_trunc('day'::text, (now() - '15 days'::interval)))
   Heap Blocks: exact=7277
   ->  Bitmap Index Scan on idx_order_date_day  (cost=0.00..353.68 rows=32700 width=0) (actual time=4.851..4.851 rows=33347 loops=1)
         Index Cond: (date_trunc('day'::text, order_date) = date_trunc('day'::text, (now() - '15 days'::interval)))
 Planning Time: 0.639 ms
 Execution Time: 23.817 ms
(7 rows)

2.2.3. JSONB 컬럼에서 제품 이름 인덱스 테스트

▸ 인덱스 생성 전

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE json_data->>'name' = 'Product 500000';

--- QUERY PLAN ---
 Gather  (cost=1000.00..21709.01 rows=5000 width=83) (actual time=47.309..98.069 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on products  (cost=0.00..20209.01 rows=2083 width=83) (actual time=75.243..90.792 rows=0 loops=3)
         Filter: ((json_data ->> 'name'::text) = 'Product 500000'::text)
         Rows Removed by Filter: 333334
 Planning Time: 0.187 ms
 Execution Time: 98.091 ms
(8 rows)

▸ 인덱스 생성

-- 3. JSONB 컬럼에서 특정 키 값 인덱스 (제품 이름)
CREATE INDEX idx_product_name ON products ((json_data->>'name'));

▸ 인덱스 생성 후

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE json_data->>'name' = 'Product 500000';

--- QUERY PLAN ---
 Bitmap Heap Scan on products  (cost=119.17..10145.29 rows=5000 width=83) (actual time=0.035..0.036 rows=1 loops=1)
   Recheck Cond: ((json_data ->> 'name'::text) = 'Product 500000'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_product_name  (cost=0.00..117.92 rows=5000 width=0) (actual time=0.030..0.030 rows=1 loops=1)
         Index Cond: ((json_data ->> 'name'::text) = 'Product 500000'::text)
 Planning Time: 0.221 ms
 Execution Time: 0.053 ms
(7 rows)

3. 표현식 인덱스의 장단점

표현식 인덱스는 조회 성능 향상에는 큰 이점이 있지만, 쓰기 작업과 관리 측면에서는 추가 비용이 발생할 수 있습니다.

데이터 특성과 사용 패턴에 따라 전략을 잘 세워야 합니다.

3.1. 장점

복잡한 조건 최적화:
단순 컬럼 인덱스 외에도, 함수나 계산식을 적용한 결과를 미리 인덱싱하여 복잡한 검색 조건에서도 빠른 조회가 가능합니다.
유연한 데이터 처리:
케이스 무시 검색, 날짜/시간 변환, JSONB 데이터 내 특정 키 인덱싱 등 다양한 데이터 처리 방식에 대응할 수 있습니다.
Index-only Scan 활용:
표현식 인덱스를 통해 쿼리에서 필요한 모든 데이터가 인덱스에 포함될 경우, 테이블 전체 스캔 없이 인덱스만으로 데이터를 읽어오는 Index-only Scan이 가능해집니다.
성능 향상:
대량의 데이터가 있는 경우, 복잡한 함수 계산을 매번 수행하는 대신 미리 계산된 값을 활용함으로써 검색 성능을 크게 개선할 수 있습니다.

3.2. 단점

쓰기 성능 저하:
데이터가 삽입, 업데이트 또는 삭제될 때마다 표현식 결과를 계산하여 인덱스를 업데이트해야 하므로, 쓰기 작업의 비용이 증가할 수 있습니다.
추가 저장 공간 사용:
표현식 인덱스는 일반 인덱스와 마찬가지로 별도의 저장 공간을 차지합니다. 따라서 불필요하게 많은 표현식 인덱스를 생성하면 디스크 공간이 증가할 수 있습니다.
쿼리 일관성 요구:
인덱스를 활용하려면 쿼리에서 사용하는 표현식이 인덱스 생성 시 사용한 표현식과 정확히 일치해야 합니다. 표현식이 조금이라도 다르면 인덱스가 사용되지 않을 수 있으므로, 쿼리 작성 시 주의가 필요합니다.
복잡한 표현식의 비용:
인덱스 생성 시 복잡한 함수나 계산식이 사용되면, 인덱스 생성 및 유지 관리에 추가적인 리소스가 소모될 수 있습니다.

+ 표현식 인덱스를 유니크 인덱스로 만든다면?

표현식 인덱스를 유니크 인덱스로 만드는 주된 이유는 '데이터 무결성 보장'을 위해서입니다.

이메일 대소문자 무시 인덱스를 예로 들어 이메일 주소의 대소문자를 구분하지 않고 유일성을 유지하려면,

단순히 email 컬럼에 유니크 제약을 걸면 대소문자가 다른 값들이 중복으로 삽입될 수 있습니다.

이를 해결하기 위해 lower(email)과 같은 표현식을 사용해 유니크 인덱스를 생성하면, 대소문자 구분 없이 중복된 이메일이 입력되는 것을 방지할 수 있습니다.

데이터 무결성 강화:
표현식을 통해 변환된 값(예: lower(email))에 대해 유일성 제약을 적용함으로써, 논리적으로 동일한 값이 중복 입력되는 것을 방지할 수 있습니다.
비즈니스 규칙 반영:
특정 비즈니스 요구사항에 따라 변환된 값(예: 공백 제거, 정규화된 전화번호 등)이 유일해야 할 때, 표현식 유니크 인덱스를 사용하면 해당 규칙을 데이터베이스 수준에서 강제할 수 있습니다.
효율적인 중복 체크:
삽입 및 업데이트 시, 유니크 인덱스를 통해 빠르게 중복 여부를 검사할 수 있어 애플리케이션 로직보다 더 안전하게 데이터 무결성을 보장할 수 있습니다.

++ 유니크 표현식 인덱스 예시

▸ 유니크 인덱스 생성

CREATE UNIQUE INDEX idx_unique_lower_email ON users ((lower(email)));

▸ 데이터 INSERT: 첫 번째 행 삽입

INSERT INTO users (email, username, created_at) VALUES ('Test@Example.com', 'testuser1', NOW());

→ 첫 번째 INSERT는 정상적으로 삽입됩니다.

▸ 데이터 INSERT: 두 번째 행 삽입 (대소문자만 다름)

INSERT INTO users (email, username, created_at) VALUES ('test@example.com', 'testuser2', NOW());

→ 두 번째 INSERT 시, lower('Test@Example.com')와 lower('test@example.com') 모두 'test@example.com'으로 평가되므로, 인덱스의 유일성 제약에 의해 중복 키 에러(duplicate key error)가 발생하게 됩니다.

잘~ 사용하면 조회 쿼리에 아주 큰 성능 향상을 기대할 수 있는 게 표현식 인덱스입니다.

데이터 특성을 잘 파악하여 전략을 잘 세우시길 바랍니다.

오늘은 여기까지~

저작자표시 비영리 변경금지

'PostgreSQL' 카테고리의 다른 글

PostgreSQL: 조인 알고리즘과 유형 (0)	2025.02.23
PostgreSQL: Citus ERROR: connection to the remote node postgres@192.168.1.100:5432 failed with the following error: server closed the connection unexpectedly (0)	2025.02.21
PostgreSQL: Query Planning(Optimizer) 파라미터 (0)	2025.02.17
PostgreSQL: Run-time Statistics 파라미터 (0)	2025.02.16
PostgreSQL: DDoS(Distributed Denial of Service) 공격 방어 (0)	2025.02.15

현재글PostgreSQL: 표현식 인덱스

DewStream

Contact: dewstream.kr@gmail.com

HA, 포스트그레스, essay, 일상, Query, 최적화, PostgreSQL, linux, tuning, life, 파라미터, 일기, SQL, 에세이, 튜닝, Parameter, PG, 고가용성, Optimization, 리눅스,

Today :
Yesterday :

DewStream