実行計画(EXPLAIN)・シーケンススキャンとインデックススキャン

自己都合によりマニュアルへのリンクは9.2としています。ご了承下さい。


━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ 実行計画(EXPLAIN）・シーケンススキャンとインデックススキャン
■■■■
■■■■
■■■■ 2013/02/18
■■■■ 使用環境：PostgreSQL9.1.7 (CentOS6.2)
                                                                   (C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

EXPLAINを使った実行計画の確認。シーケンススキャン／インデックススキャン編。
ごく簡単な内容のみです。


【マニュアル】

EXPLAIN→●[マニュアル]


【その他】

Lets's postgres・スロークエリの改善→●[記事]
Lets's postgres・スロークエリの分析→●[記事]


■１■ 概要
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

シーケンススキャン（シーケンシャルスキャン）　
インデックススキャン　



■２■ シーケンススキャンとインデックススキャン　簡単な確認
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━


テーブルt1　１千万件　PKなし
テーブルt2　１千万件　PKあり

シーケンススキャンとインデックススキャンを発生させそれぞれ実行計画の表示のされ方を確認する。


１．準備
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

【□】 テーブルを作成

DROP TABLE if exists t1,t2;
CREATE TABLE t1 (c1 int,c2 timestamp,c3 int);
CREATE TABLE t2 (c1 int PRIMARY KEY,c2 timestamp,c3 int);


【□】 t1テーブルへCTEを使い1000万件インサート

\timing on
WITH RECURSIVE tmp(c1,c2,c3) AS (
    SELECT 1, current_timestamp,floor(random()*10000000) 
    UNION ALL 
    SELECT c1+1, c2 + '1 hours' ,floor(random()*10000000) 
      FROM tmp WHERE c1 < 10000000
)
INSERT INTO t1 SELECT * FROM tmp;


【□】 t2テーブルへCTEを使い1000万件インサート

\timing on
WITH RECURSIVE tmp(c1,c2,c3) AS (
    SELECT 1, current_timestamp,floor(random()*10000000) 
    UNION ALL 
    SELECT c1+1, c2 + '1 hours' ,floor(random()*10000000) 
      FROM tmp WHERE c1 < 10000000
)
INSERT INTO t2 SELECT * FROM tmp;



【参考】上のテーブルのINSERT処理時間

  テーブルt1 INSERT 0 10000000   時間:  61595.054 ms・・約61秒
  テーブルt2 INSERT 0 10000000   時間: 117722.924 ms・・約117秒



２．実行計画確認
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * FROM t1 where c1=5000000;
EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * FROM t2 where c1=5000000;




【□】 EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * FROM t1 where c1=5000000;

---------------------------------------------------------------------------------------------------------------
★Seq Scan on public.t1  (cost=0.00..188696.44 rows=1 width=16) (actual time=2997.280..4370.887 rows=1 loops=1)
   Output: c1, c2, c3
   Filter: (t1.c1 = 5000000)
   Buffers: shared hit=32 read=63663 ★readが大量に発生
 Total runtime: 4370.949 ms

時間: 4371.341 ms





【□】 EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * FROM t2 where c1=5000000;

--------------------------------------------------------------------------------------------------------------------
★Index Scan using t2_pkey on public.t2  (cost=0.00..9.36 rows=1 width=16) (actual time=0.011..0.012 rows=1 loops=1)
   Output: c1, c2, c3
   Index Cond: (t2.c1 = 5000000)
   Buffers: shared hit=5  ★readは発生していない。すべてhit
 Total runtime: 0.042 ms

時間: 0.659 ms


※「時間：x.xxx ms」はpsqlの\timingを指定したための時間表示。
    EXPLAIN ANALYZEでの時間は「Total runtime: 」欄を参照。






以上