postgresql 실행계획

1. 실행계획 명령어

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

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

    ANALYZE [ boolean ]

    VERBOSE [ boolean ]

    COSTS [ boolean ]

    BUFFERS [ boolean ]

    TIMING [ boolean ]

    FORMAT { TEXT | XML | JSON | YAML }:EXPLAIN [ ( option [, ...] ) ] statement

ANALYZE TEST  ;                    -- 통계정보 UPDATE  

    ALTER TABLE ... ALTER COLUMN ...SET STATISTICS

EXPLAIN SELECT * FROM TEST ;   -- 실행계획 확인

-- cost 비용 계산

SELECT relpages*current_setting('seq_page_cost')::numeric + reltuples*current_setting('cpu_tuple_cost')::numeric as cost

FROM pg_class

WHERE relname='test_explain_1';

-- 정확한 통계 정보 표시

EXPLAIN (ANALYZE) SELECT * FROM test_explain_1 WHERE id >= 10 and id < 20;

EXPLAIN SELECT * FROM test_explain_1 a JOIN test_explain_1 b ON (a.id = b.id) WHERE a.id < 100;

-- 통계 및 buffer 정보 표시

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM test_explain_1 WHERE id >= 10 and id < 20;

2. 실행계획 테스트

CREATE OR REPLACE FUNCTION generate_random_text ( int  ) RETURNS TEXT AS

$$

SELECT string_agg(substr('0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', trunc(random() * 62)::integer + 1, 1), '')  FROM  generate_series(1, $1)

$$

LANGUAGE SQL;

CREATE TABLE login as SELECT n, generate_random_text(8) as login_name  FROM generate_series(1, 1000) as foo(n);

CREATE INDEX ON login(login_name);

VACUUM ANALYZE login;

EXPLAIN SELECT * FROM login WHERE login_name = 'jxaG6gjJ';

Index Scan using login_login_name_idx on login  (cost=0.28..8.29 rows=1 width=13)

  Index Cond: (login_name = 'jxaG6gjJ'::text)  

EXPLAIN SELECT * FROM login WHERE login_name = lower('jxaG6gjJ');

Index Scan using login_login_name_idx on login  (cost=0.28..8.29 rows=1 width=13)

  Index Cond: (login_name = 'jxag6gjj'::text)

-- login_name에 function을 사용했기 때문에 seq scan 발생

EXPLAIN SELECT * FROM login WHERE lower(login_name) = lower('jxaG6gjJ');

"Seq Scan on login  (cost=0.00..21.00 rows=5 width=13)"

"  Filter: (lower(login_name) = 'jxag6gjj'::text)"

-- function base 인덱스 생성후 실행계획 확인

CREATE INDEX ON login(lower(login_name));

EXPLAIN SELECT * FROM login WHERE lower(login_name) = lower('jxaG6gjJ');

Index Scan using login_lower_idx on login  (cost=0.28..8.29 rows=1 width=13)"

  Index Cond: (lower(login_name) = 'jxag6gjj'::text)

CREATE INDEX on test_explain_1 (name text_pattern_ops);