memo-space

大文字小文字同一視
/\chello (Hello, HELLOにもマッチ)

カーソル位置指定
/hello/e (helloにマッチして、カーソルを"o"の位置に)
/hello/e1 (helloにマッチして、カーソルを"o"の次の位置に)

コマンドラインに最後に検索したパターンを入力する
:/

pg_statsのcorrelationとは、データが物理的に格納されている順番をあらわすような値らしい。これがIndexScanのコスト計算に影響する。 次のコードは、テーブルtbl1のa,bフィールドに1〜100000の値を格納する。aには昇順に、bにはランダムな順番に格納する。で、a,bそれぞれにインデックスを作成する。

<?php

$conn = pg_connect("") or die;

pg_query($conn, "drop table tbl1");
pg_query($conn, "create table tbl1 (a serial, b int)");
pg_query($conn, "create temp table tbl2 (a int)");
$scale = 100000;
pg_query($conn, "copy tbl2 from stdin");
for ($i = 0; $i < $scale; $i++)
{
  pg_put_line("$i\n");
}
pg_put_line("\\.\n");
pg_end_copy($conn);

pg_query("insert into tbl1 (b) select a from tbl2 order by random()");
pg_query("create index tbl1_a_idx on tbl1 (a)");
pg_query("create index tbl1_b_idx on tbl1 (b)");
pg_query("analyze tbl1");
pg_close($conn);
?>

この時のcorrelationは以下のようになっている。

ishida=# SELECT tablename, attname, correlation from pg_stats where tablename
= 'tbl1';
 tablename | attname | correlation
-----------+---------+-------------
 tbl1      | a       |           1
 tbl1      | b       | 0.000599762
(2 rows)

で、explain してみると、

ishida=# EXPLAIN ANALYZE select * from tbl1 where a < 100;
                                                    QUERY PLAN                  
-------------------------------------------------------------------------------------------------------------------
 Index Scan using tbl1_a_idx on tbl1  (cost=0.00..4.17 rows=88 width=8)
(actual time=0.143..1.823 rows=99 loops=1)
   Index Cond: (a < 100)
 Total runtime: 3.349 ms
(3 rows)

ishida=# EXPLAIN ANALYZE select * from tbl1 where b < 100;
                                                      QUERY PLAN                
----------------------------------------------------------------------------------------------------------------------
 Index Scan using tbl1_b_idx on tbl1  (cost=0.00..187.01 rows=49 width=8)
(actual time=0.224..2.911 rows=100 loops=1)
   Index Cond: (b < 100)
 Total runtime: 5.614 ms
(3 rows)

同じ行数を返すクエリなのに、aのコストが4.17に対して、bのコストは187.01になっている。（しかもbはrowsの見積りが49と本来の半分なのに、、、）。 bのクエリの返す行数を増やしていくと、900あたりでSeq Scanが選択される。

ishida=# EXPLAIN ANALYZE select * from tbl1 where b < 900;
                                              QUERY PLAN                        
-------------------------------------------------------------------------------------------------------
 Seq Scan on tbl1  (cost=0.00..1743.05 rows=838 width=8) (actual
time=1.564..250.410 rows=900 loops=1)
   Filter: (b < 900)
 Total runtime: 257.991 ms
(3 rows)

ishida=# EXPLAIN ANALYZE select * from tbl1 where b < 800;
                                                       QUERY PLAN               
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using tbl1_b_idx on tbl1  (cost=0.00..1699.95 rows=740 width=8)
(actual time=0.209..17.035 rows=800 loops=1)
   Index Cond: (b < 800)
 Total runtime: 26.627 ms
(3 rows)

けど、800の時に26.627msなのに、900で257.991msでは、明かに900の時もIndex Scanを使った方が速そうだ。つまり、Index Scanのコストの見積りが大きすぎる。 randome_page_costを1にしてみると、80000あたりでSeq Scanを選択するようになった。

ishida=# set random_page_cost to 1;
SET

ishida=# EXPLAIN ANALYZE select * from tbl1 where b < 70000;
                                                          QUERY PLAN            
------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tbl1_b_idx on tbl1  (cost=0.00..1595.15 rows=70234 width=8)
(actual time=0.142..864.855 rows=70000 loops=1)
   Index Cond: (b < 70000)
 Total runtime: 1313.035 ms
(3 rows)

ishida=# EXPLAIN ANALYZE select * from tbl1 where b < 80000;
                                                QUERY PLAN                      
-----------------------------------------------------------------------------------------------------------
 Seq Scan on tbl1  (cost=0.00..1743.05 rows=80459 width=8) (actual
time=0.076..828.856 rows=80000 loops=1)
   Filter: (b < 80000)
 Total runtime: 1340.610 ms
(3 rows)

ちなみにその物理的な並び順は、clusterコマンドで変更することができる。

ishida=# CLUSTER tbl1_b_idx ON tbl1;
CLUSTER
ishida=# ANALYZE tbl1;
ANALYZE
ishida=# SELECT tablename, attname, correlation from pg_stats where tablename
= 'tbl1';
 tablename | attname | correlation
-----------+---------+-------------
 tbl1      | a       |  -0.0377632
 tbl1      | b       |           1
(2 rows)