PostgreSQL은 공유 SQL 영역이 없고, 매 SQL 수행 시 마다 파싱이 발생 된다.
자주 사용되거나, 특별한 설정 값을 가지고 수행 될 필요가 있는 SQL의 경우는 PREPARE 명령을 이용하여 SQL문과 PLAN을 세션 프로세스에 저장해서 사용할 수 있다.
|
# 명령어 PREPARE name [ ( data_type [, ...] ) ] AS statement EXECUTE name [ ( parameter [, ...] ) ] DEALLOCATE [ PREPARE ] { name | ALL }
# 자신의 세션에서 Prepared 시킨 SQL문 확인 select * from pg_prepared_statements ; +------+--------------------------------------------------------------+----------------------------------+-----------------+----------+ | name | statement | prepare_time | parameter_types | from_sql | +------+--------------------------------------------------------------+----------------------------------+-----------------+----------+ | u | prepare u(integer) as update t2 set n = n + 1 where id = $1; | 21-SEP-16 15:12:28.079927 +09:00 | {integer} | t | +------+--------------------------------------------------------------+----------------------------------+-----------------+----------+
|
다음의 테스트는 SQL 수행 시 마다 파싱이 발생하여서, 동일한 일을 하지만 수행 시간이 증가된 경우의 예이다.
|
일반 SQL문 수행 |
Prepare 사용 |
|
#테스트 테이블 생성 create table t1( id serial primary key, n numeric not null );
#테스트 데이터 삽입 insert into t1(n) select 1 from generate_series(1,100000);
#Analyze analyze t1;
#Update 속도 테스트 do $$ declare i record; begin for i in (select id from t1) loop execute 'update t1 set n = n + 1 where id = '||i.id; end loop; end; $$ language plpgsql;
Time: 8586.600 ms 하드파싱으로 인한 쿼리 수행 시간 지연 발생
|
#테스트 테이블 생성 create table t2( id serial primary key, n numeric not null );
#테스트 데이터 삽입 insert into t2(n) select 1 from generate_series(1,100000);
#Analyze analyze t2;
#Prepare 구문을 이용하여 Plan생성 및 저장 prepare u(integer) as update t2 set n = n + 1 where id = $1;
#Update 속도 테스트 do $$ declare i integer ; begin for i in 1..100000 loop execute 'execute u(' || i || ')'; end loop; end; $$ language plpgsql;
Time: 2997.875 ms
|
다음의 테스트는 동일 세션에서 특정 파라미터를 변경하여 SQL PLAN을 고정 시킨 테스트
|
# 일반적인 상황에서의 Plan 확인 explain select count(*) from t1 ; +----------------------------------------------------------------+ | QUERY PLAN | +----------------------------------------------------------------+ | Aggregate (cost=2332.00..2332.01 rows=1 width=0) | | -> Seq Scan on t1 (cost=0.00..2082.00 rows=100000 width=0) | +----------------------------------------------------------------+
#Full Scan 못하게 파라미터 변경 set enable_seqscan = OFF;
explain select count(*) from t1 ; +-------------------------------------------------------------------------------------+ | QUERY PLAN | +-------------------------------------------------------------------------------------+ | Aggregate (cost=5050.42..5050.43 rows=1 width=0) | | -> Index Only Scan using t1_pkey on t1 (cost=0.42..4800.42 rows=100000 width=0) | +-------------------------------------------------------------------------------------+
# Full Scan을 못하게 Plan이 만들어지는 시점의 PLAN을 저장하기 위하여 Prepare 구문 사용 prepare index_scan as select count(*) from t1 ;
explain execute index_scan ; +-------------------------------------------------------------------------------------+ | QUERY PLAN | +-------------------------------------------------------------------------------------+ | Aggregate (cost=5050.42..5050.43 rows=1 width=0) | | -> Index Only Scan using t1_pkey on t1 (cost=0.42..4800.42 rows=100000 width=0) | +-------------------------------------------------------------------------------------+
#Full Scan 가능하도록 파라미터 변경 set enable_seqscan = ON ;
explain execute index_scan ; +-------------------------------------------------------------------------------------+ | QUERY PLAN | +-------------------------------------------------------------------------------------+ | Aggregate (cost=5050.42..5050.43 rows=1 width=0) | | -> Index Only Scan using t1_pkey on t1 (cost=0.42..4800.42 rows=100000 width=0) | +-------------------------------------------------------------------------------------+
explain select count(*) from t1 ; +----------------------------------------------------------------+ | QUERY PLAN | +----------------------------------------------------------------+ | Aggregate (cost=2332.00..2332.01 rows=1 width=0) | | -> Seq Scan on t1 (cost=0.00..2082.00 rows=100000 width=0) | +----------------------------------------------------------------+
#실제 쿼리 수행 execute index_scan ; +--------+ | count | +--------+ | 100000 | +--------+ Time: 10.733 ms
select count(*) from t1 ; +--------+ | count | +--------+ | 100000 | +--------+ Time: 7.517 ms |
변수를 사용하는 SQL 처리
|
PREPARE p1 (int, text, bool, numeric) AS SELECT * FROM t3 WHERE id = $1 AND name = $2 AND payed = $3 AND price > $4 ;
EXECUTE p1(1, 'Hunter Valley', 't', 200.00);
|