Unconference at db tech showcase 2012の資料公開 :)

db tech showcase 2012
Unconference at db tech showcase 2012

db tech showcaseの一角をJPOUGが占拠してUnconferenceを開催しました。 db tech showcase関係者の皆様、このような機会を与えて頂き大変感謝します。
そして、お疲れさまでした。

---

Index Only Access ３部作の最終回？! として 「Index Only Accessが実装されるたった一つの理由」と題したセッションを行いました。
実行計画を取得するために操作したデータベースの中には人生二度目のデータベース複数もあり、かなりの時間を裂いて調べた割にはセッション時間が少々短めになってしまいました。m(_ _)m

なぜ、このテーマを選んだか.

PostgreSQLがリリースされてから9.1まで実装されなかったIndex Only Accessでしたが、9.2でついに実装されました。

そして..db tech showcase 2012は...

SQL> select dbms_name from all_dbms where dbms_category like "%";

DBMS_NAME
------------------
Oracle
DB2
MySQL
PostgreSQL
SQL Server
Vecterwise
MongoDB
Symfoware
Clustrix
InfiniDB
.
.
.
.

的な雰囲気となっていることもあり、Index Only Accessの魚拓をあつめて比較、Index Only Accessが実装される理由について今一度、考えてみたいな..と。
タイトル見ただけで理由が想像できた方は、資料見なくても大丈夫だと思いますよ。:)　
　

H/Wの性能が急速に伸びてきている影響もあるように感じますが、無駄に広範囲な検索や、無駄にビッグなデータとなっていること気にしていないのではないか？　というケースが多くなっていると感じています。
DBMSはアクセスするデータをより少なくするための工夫をしているのに...エンジニアがそれをうまく使っていない、使えていない、設計できてない...そんな"感じ"がするんです。

セッション資料を公開しました。

じゃ、like "%てない" 状況をどうすればいいか....答えは、小田さんのセッションの中にあった。。。。:)

#不慣れなDBMSもあり、こんなメトリックみたほうが分かりやすいよ〜、などのツッコミ歓迎します.

Index Only Accessネタのおまけ

2012/10/13追記
MySQL(InnoDB)の主キーはClustered Indexなので主キーアクセスである場合はCovering indexは不要ですね。(^^;;　
PostgreSQL9.2のIndex-only scanですが、vacuumさえしっかりやっていればcovering indexを利用するようになることを確認。

前述の２点を後日追加予定です。

---

随分間があいてしまいました、m(_ _)m

Oracle以外でも多数の商用/OSS RDBMSでIndex Only Accessできるんですよね。
ということで、MySQL/PostgreSQLの実行計画ではどのように表示されるのか、Oracleのように索引だけ作れば勝ってにIndex Only Accessやってくれるのか？、などなど簡単に確かめてみました。(備忘録）

OracleのIndex Only Accessは以下のエントリを参考にしてください。
・いん！、イン！、Index どっぷり Inde Only Access生活w - Oracle OpenWorld Unconference presented by JPOUG
・JPOUG SET EVENTS 20120721 - 「(続)いん！、イン！、Index 大人の事情縛りのSQLチューニング」資料公開

今回使ったのは、MySQL 5.5.27/PostgreSQL 9.1.4そしてBeta阪ですが9.2 Beta4です。
環境はOracleが乗ってる環境と同じです。

MacBook Air late 2010 13inch 2GB (MacOS X Lion)
VirtualBox4.1.18 for MacOS X
GuestOS：CentOS5.8 x86

ちなみに、データ作りに時間を割けなかったので、クラスタリングファクターはどちらのも低めとなっています。実行計画上どのように見えるのか知りたかっただけなので. (^^;;;

MySQL 5.5.27

Oracleのデモでも使っていたスカラー副問合せで試してみました。最初は、Index Rancge Scanをグルグル。
この場合は索引、表をそれぞれアクセスします。

mysql> explain 
    -> select
    ->     t1.unique_id,
    ->     t1.item_code,
    ->     (
    ->        select
    ->            max(t3.unique_id)
    ->        from
    ->            tab31 t2 join tab311 t3
    ->            on
    ->                t2.sub_item_code = t3.sub_item_code
    ->                and t3.is_delete = 0
    ->        where
    ->            t2.item_code = t1.item_code
    ->            and t2.is_delete = 0
    ->     ) current_sub_item
    -> from
    ->     tab3 t1
    -> where
    ->     t1.unique_id between 1 and 10000
    ->     and t1.is_delete = 0
    ->     and t1.status_code = '00'
    -> ;
+----+--------------------+-------+--------+---------------+-----------+---------+------------------------+-------+-------------+
| id | select_type        | table | type   | possible_keys | key       | key_len | ref                    | rows  | Extra       |
+----+--------------------+-------+--------+---------------+-----------+---------+------------------------+-------+-------------+
|  1 | PRIMARY            | t1    | range  | PRIMARY       | PRIMARY   | 5       | NULL                   | 20108 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | eq_ref | PRIMARY       | PRIMARY   | 45      | scott.t1.item_code     |     1 | Using where |
|  2 | DEPENDENT SUBQUERY | t3    | ref    | tab311_ix     | tab311_ix | 31      | scott.t2.sub_item_code |     1 | Using where |
+----+--------------------+-------+--------+---------------+-----------+---------+------------------------+-------+-------------+
3 rows in set (0.00 sec)

mysql> 

Index Only Accessさせた場合の実行計画です。
Extra列に Using indexと出ていればIndex Only Accessになっています。
ちなみに、Oracleとちがって勝ってにCovering Indexを使ってくれなかったのでSQLヒントを使っています。Oracleのヒントと随分書き方違うので戸惑うよ(^^;;;

mysql> explain
    -> select
    ->     t1.unique_id,
    ->     t1.item_code,
    ->     (
    ->        select
    ->            max(t3.unique_id)
    ->        from
    ->            tab31 t2 ignore index(primary) join tab311 t3 ignore index(tab311_ix)
    ->            on
    ->                t2.sub_item_code = t3.sub_item_code
    ->                and t3.is_delete = 0
    ->        where
    ->            t2.item_code = t1.item_code
    ->            and t2.is_delete = 0
    ->     ) current_sub_item
    -> from
    ->     tab3 t1
    -> where
    ->     t1.unique_id between 1 and 10000
    ->     and t1.is_delete = 0
    ->     and t1.status_code = '00'
    -> ;
+----+--------------------+-------+-------+----------------+----------------+---------+------------------------------+-------+--------------------------+
| id | select_type        | table | type  | possible_keys  | key            | key_len | ref                          | rows  | Extra                    |
+----+--------------------+-------+-------+----------------+----------------+---------+------------------------------+-------+--------------------------+
|  1 | PRIMARY            | t1    | range | PRIMARY        | PRIMARY        | 5       | NULL                         | 20108 | Using where              |
|  2 | DEPENDENT SUBQUERY | t2    | ref   | tab31_demo_ix  | tab31_demo_ix  | 47      | scott.t1.item_code,const     | 10000 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | t3    | ref   | tab311_demo_ix | tab311_demo_ix | 33      | scott.t2.sub_item_code,const | 10000 | Using where; Using index |
+----+--------------------+-------+-------+----------------+----------------+---------+------------------------------+-------+--------------------------+
3 rows in set (0.00 sec)

mysql> 

PostgreSQL 9.1

PostgreSQL9.1まではIndex Only Accessが実装されていないとのこと。（実は今年になってはじめて知ったことなのですが、その時は、「え？！　そうなの？」って感じでした）

※実行時間のバラツキがあるため実行統計情報から表や索引ブロックアクセス状況を確認しています。

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
 relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 16401 | public     | tab311  |              0 |             0 |             0 |            0 |               0 |              0 |              0 |             0
 16395 | public     | tab31   |              0 |             0 |             0 |            0 |               0 |              0 |              0 |             0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
 relid | indexrelid | schemaname | relname |  indexrelname  | idx_blks_read | idx_blks_hit 
-------+------------+------------+---------+----------------+---------------+--------------
 16401 |      16411 | public     | tab311  | tab311_pk      |             0 |            0
 16395 |      16454 | public     | tab31   | tab31_demo_ix  |             0 |            0
 16401 |      16455 | public     | tab311  | tab311_demo_ix |             0 |            0

scott=> explain analyze verbose
scott-> select
scott->     t1.unique_id,
scott->     t1.item_code,
scott->     (
scott(>        select
scott(>            max(t3.unique_id)
scott(>        from
scott(>            tab31 t2 join tab311 t3
scott(>            on
scott(>                t2.sub_item_code = t3.sub_item_code
scott(>                and t3.is_delete = 0
scott(>        where
scott(>            t2.item_code = t1.item_code
scott(>            and t2.is_delete = 0
scott(>     ) current_sub_item
scott-> from
scott->     tab3 t1
scott-> where
scott->     t1.unique_id between 1 and 10000
scott->     and t1.is_delete = 0
scott->     and t1.status_code = '00'
scott-> ;
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tab3_pk on public.tab3 t1  (cost=0.00..2050286.39 rows=10326 width=22) (actual time=45.613..6560.751 rows=10000 loops=1)
   Output: t1.unique_id, t1.item_code, (SubPlan 1)
   Index Cond: ((t1.unique_id >= 1::numeric) AND (t1.unique_id <= 10000::numeric))
   Filter: ((t1.is_delete = 0::numeric) AND (t1.status_code = '00'::bpchar))
   SubPlan 1
     ->  Aggregate  (cost=198.47..198.48 rows=1 width=6) (actual time=0.650..0.651 rows=1 loops=10000)
           Output: max(t3.unique_id)
           ->  Nested Loop  (cost=0.00..198.46 rows=4 width=6) (actual time=0.143..0.621 rows=40 loops=10000)
                 Output: t3.unique_id
                 ->  Index Scan using tab31_pk on public.tab31 t2  (cost=0.00..8.58 rows=1 width=11) (actual time=0.010..0.011 rows=1 loops=10000)
                       Output: t2.item_code, t2.sub_item_code, t2.data, t2.is_delete
                       Index Cond: (t2.item_code = t1.item_code)
                       Filter: (t2.is_delete = 0::numeric)
                 ->  Index Scan using tab311_ix on public.tab311 t3  (cost=0.00..189.27 rows=49 width=17) (actual time=0.130..0.565 rows=40 loops=10000)
                       Output: t3.unique_id, t3.sub_item_code, t3.data, t3.is_delete
                       Index Cond: (t3.sub_item_code = t2.sub_item_code)
                       Filter: (t3.is_delete = 0::numeric)
 Total runtime: 6566.636 ms
(18 行)

時間: 6618.795 ms
scott=> 

適切な索引を利用しているのでIndex Unique/Range Scanとテーブルブロックへのアクセスが確認できます。（当然といえば当然ですよね）

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
 relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 16401 | public     | tab311  |          15389 |         36920 |          1983 |        30033 |               0 |              0 |              0 |             0
 16395 | public     | tab31   |            401 |          9599 |            64 |        30057 |               0 |              0 |              0 |             0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
 relid | indexrelid | schemaname | relname |  indexrelname  | idx_blks_read | idx_blks_hit 
-------+------------+------------+---------+----------------+---------------+--------------
 16401 |      16411 | public     | tab311  | tab311_pk      |             0 |            0
 16395 |      16454 | public     | tab31   | tab31_demo_ix  |            64 |        30057
 16401 |      16455 | public     | tab311  | tab311_demo_ix |          1983 |        30033

Index Only AccessさせるためのCovering Index(FAT index)を作ってみましたが、Index Range/Unique Scanのままですね。
ちなみに、PostgreSQLってOracleやMySQLのようなSQLヒントがありません。（あったらごめんなさい。調べきれてないだけです。m(_ _)m
Covering Indexだけを残して邪魔な索引を削除することでCovering indexをアクセスさせています。

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
 relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 16401 | public     | tab311  |              0 |             0 |             0 |            0 |               0 |              0 |              0 |             0
 16395 | public     | tab31   |              0 |             0 |             0 |            0 |               0 |              0 |              0 |             0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
 relid | indexrelid | schemaname | relname |  indexrelname  | idx_blks_read | idx_blks_hit 
-------+------------+------------+---------+----------------+---------------+--------------
 16401 |      16411 | public     | tab311  | tab311_pk      |             0 |            0
 16395 |      16454 | public     | tab31   | tab31_demo_ix  |             0 |            0
 16401 |      16455 | public     | tab311  | tab311_demo_ix |             0 |            0

scott=> explain analyze verbose
scott-> select
scott->     t1.unique_id,
scott->     t1.item_code,
scott->     (
scott(>        select
scott(>            max(t3.unique_id)
scott(>        from
scott(>            tab31 t2 join tab311 t3
scott(>            on
scott(>                t2.sub_item_code = t3.sub_item_code
scott(>                and t3.is_delete = 0
scott(>        where
scott(>            t2.item_code = t1.item_code
scott(>            and t2.is_delete = 0
scott(>     ) current_sub_item
scott-> from
scott->     tab3 t1
scott-> where
scott->     t1.unique_id between 1 and 10000
scott->     and t1.is_delete = 0
scott->     and t1.status_code = '00'
scott-> ;
                                                                          QUERY PLAN                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tab3_pk on public.tab3 t1  (cost=0.00..2109597.62 rows=10326 width=22) (actual time=0.677..1808.056 rows=10000 loops=1)
   Output: t1.unique_id, t1.item_code, (SubPlan 1)
   Index Cond: ((t1.unique_id >= 1::numeric) AND (t1.unique_id <= 10000::numeric))
   Filter: ((t1.is_delete = 0::numeric) AND (t1.status_code = '00'::bpchar))
   SubPlan 1
     ->  Aggregate  (cost=204.21..204.22 rows=1 width=6) (actual time=0.176..0.176 rows=1 loops=10000)
           Output: max(t3.unique_id)
           ->  Nested Loop  (cost=0.00..204.20 rows=4 width=6) (actual time=0.024..0.137 rows=40 loops=10000)
                 Output: t3.unique_id
                 ->  Index Scan using tab31_demo_ix on public.tab31 t2  (cost=0.00..8.76 rows=1 width=11) (actual time=0.007..0.009 rows=1 loops=10000)
                       Output: t2.item_code, t2.sub_item_code, t2.data, t2.is_delete
                       Index Cond: ((t2.item_code = t1.item_code) AND (t2.is_delete = 0::numeric))
                 ->  Index Scan using tab311_demo_ix on public.tab311 t3  (cost=0.00..194.84 rows=49 width=17) (actual time=0.013..0.068 rows=40 loops=10000)
                       Output: t3.unique_id, t3.sub_item_code, t3.data, t3.is_delete
                       Index Cond: ((t3.sub_item_code = t2.sub_item_code) AND (t3.is_delete = 0::numeric))
 Total runtime: 4762.819 ms
(16 行)

時間: 4855.100 ms
scott=> 

実行計画上、Filterはなくなりましたが、表ブロックもアクセスしているのでIndex Only Accessにはなっていません。(ほんとにIndex Only AccessというかIndex-only Scanは9.1までは実装されていないんですね。）

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
 relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 16401 | public     | tab311  |          15390 |         36919 |          1982 |        30034 |               0 |              0 |              0 |             0
 16395 | public     | tab31   |            401 |          9599 |            62 |        30059 |               0 |              0 |              0 |             0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
 relid | indexrelid | schemaname | relname |  indexrelname  | idx_blks_read | idx_blks_hit 
-------+------------+------------+---------+----------------+---------------+--------------
 16401 |      16411 | public     | tab311  | tab311_pk      |             0 |            0
 16395 |      16454 | public     | tab31   | tab31_demo_ix  |            62 |        30059
 16401 |      16455 | public     | tab311  | tab311_demo_ix |          1982 |        30034

PostgreSQL 9.2 Beta4

最後に、PostgreSQL 9.2 Beta4 です。このリリースではPostgreSQLでは初めて、Index Only Access（マニュアルでは　Index-only Scanと記載されています）
PostgreSQL方面の方がIndex-only Scanと書くかたが多いのもこの影響でしょうね。日本人からするとIndex Only AccessよりIndex Only Scanの方が発音しやすい？（私だけか？）気がしますw

scott=> explain analyze verbose
scott-> select
scott->     t1.unique_id,
scott->     t1.item_code,
scott->     (
scott(>        select
scott(>            max(t3.unique_id)
scott(>        from
scott(>            tab31 t2 join tab311 t3
scott(>            on
scott(>                t2.sub_item_code = t3.sub_item_code
scott(>                and t3.is_delete = 0
scott(>        where
scott(>            t2.item_code = t1.item_code
scott(>            and t2.is_delete = 0
scott(>     ) current_sub_item
scott-> from
scott->     tab3 t1
scott-> where
scott->     t1.unique_id between 1 and 10000
scott->     and t1.is_delete = 0
scott->     and t1.status_code = '00'
scott-> ;
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tab3_pk on public.tab3 t1  (cost=0.00..1925706.85 rows=9421 width=22) (actual time=41.205..6202.096 rows=10000 loops=1)
   Output: t1.unique_id, t1.item_code, (SubPlan 1)
   Index Cond: ((t1.unique_id >= 1::numeric) AND (t1.unique_id <= 10000::numeric))
   Filter: ((t1.is_delete = 0::numeric) AND (t1.status_code = '00'::bpchar))
   SubPlan 1
     ->  Aggregate  (cost=204.32..204.33 rows=1 width=6) (actual time=0.613..0.614 rows=1 loops=10000)
           Output: max(t3.unique_id)
           ->  Nested Loop  (cost=0.00..204.31 rows=4 width=6) (actual time=0.110..0.584 rows=40 loops=10000)
                 Output: t3.unique_id
                 ->  Index Scan using tab31_pk on public.tab31 t2  (cost=0.00..11.35 rows=1 width=11) (actual time=0.012..0.013 rows=1 loops=10000)
                       Output: t2.item_code, t2.sub_item_code, t2.data, t2.is_delete
                       Index Cond: (t2.item_code = t1.item_code)
                       Filter: (t2.is_delete = 0::numeric)
                 ->  Index Scan using tab311_ix on public.tab311 t3  (cost=0.00..192.47 rows=49 width=17) (actual time=0.095..0.525 rows=40 loops=10000)
                       Output: t3.unique_id, t3.sub_item_code, t3.data, t3.is_delete
                       Index Cond: (t3.sub_item_code = t2.sub_item_code)
                       Filter: (t3.is_delete = 0::numeric)
 Total runtime: 6207.935 ms
(18 行)

時間: 6313.789 ms
scott=>

9.2でもIndex Only Accessでなければ表ブロックもアクセスしますよね〜。そりゃそうだ。:)

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
 relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 16404 | public     | tab311  |          15390 |         36919 |          1649 |        29996 |               0 |              0 |              0 |             0
 16398 | public     | tab31   |            401 |          9599 |            39 |        30037 |               0 |              0 |              0 |             0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
 relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit 
-------+------------+------------+---------+--------------+---------------+--------------
 16404 |      16414 | public     | tab311  | tab311_pk    |             0 |            0
 16398 |      16432 | public     | tab31   | tab31_pk     |            39 |        30037
 16404 |      16434 | public     | tab311  | tab311_ix    |          1649 |        29996

いよいよ、PostgreSQL9.2 Beta4のIndex-only Scanの番です。:)
お〜〜〜っ、 Index Scan using xxxxという部分が、Index Only Scan using xxxxとなっています！ が、 Heap Fetches 400000とある？　どゆこと？

scott=> explain analyze verbose
scott-> select
scott->     t1.unique_id,
scott->     t1.item_code,
scott->     (
scott(>        select
scott(>            max(t3.unique_id)
scott(>        from
scott(>            tab31 t2 join tab311 t3
scott(>            on
scott(>                t2.sub_item_code = t3.sub_item_code
scott(>                and t3.is_delete = 0
scott(>        where
scott(>            t2.item_code = t1.item_code
scott(>            and t2.is_delete = 0
scott(>     ) current_sub_item
scott-> from
scott->     tab3 t1
scott-> where
scott->     t1.unique_id between 1 and 10000
scott->     and t1.is_delete = 0
scott->     and t1.status_code = '00'
scott-> ;

                                                                            QUERY PLAN                                                                             
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tab3_pk on public.tab3 t1  (cost=0.00..2000896.05 rows=9421 width=22) (actual time=0.410..1728.699 rows=10000 loops=1)
   Output: t1.unique_id, t1.item_code, (SubPlan 1)
   Index Cond: ((t1.unique_id >= 1::numeric) AND (t1.unique_id <= 10000::numeric))
   Filter: ((t1.is_delete = 0::numeric) AND (t1.status_code = '00'::bpchar))
   SubPlan 1
     ->  Aggregate  (cost=212.30..212.31 rows=1 width=6) (actual time=0.168..0.168 rows=1 loops=10000)
           Output: max(t3.unique_id)
           ->  Nested Loop  (cost=0.00..212.29 rows=4 width=6) (actual time=0.023..0.131 rows=40 loops=10000)
                 Output: t3.unique_id
                 ->  Index Only Scan using tab31_demo_ix on public.tab31 t2  (cost=0.00..13.13 rows=1 width=11) (actual time=0.006..0.007 rows=1 loops=10000)
                       Output: t2.item_code, t2.is_delete, t2.sub_item_code
                       Index Cond: ((t2.item_code = t1.item_code) AND (t2.is_delete = 0::numeric))
                       Heap Fetches: 10000
                 ->  Index Only Scan using tab311_demo_ix on public.tab311 t3  (cost=0.00..198.67 rows=49 width=17) (actual time=0.013..0.066 rows=40 loops=10000)
                       Output: t3.sub_item_code, t3.is_delete, t3.unique_id
                       Index Cond: ((t3.sub_item_code = t2.sub_item_code) AND (t3.is_delete = 0::numeric))
                       Heap Fetches: 400000
 Total runtime: 5412.612 ms
(18 行)

時間: 5465.400 ms
scott=> 

やはり！　Heap Fetchesとあるのでおかしいと思っていたら...orz. なんで表ブロックアクセスしてんの〜〜〜っ。実行計画は、Index Only Scan。謎。

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
 relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 16404 | public     | tab311  |          15389 |         36920 |          1983 |        30033 |               0 |              0 |              0 |             0
 16398 | public     | tab31   |            401 |          9599 |            64 |        30057 |               0 |              0 |              0 |             0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
 relid | indexrelid | schemaname | relname |  indexrelname  | idx_blks_read | idx_blks_hit 
-------+------------+------------+---------+----------------+---------------+--------------
 16404 |      16414 | public     | tab311  | tab311_pk      |             0 |            0
 16398 |      16435 | public     | tab31   | tab31_demo_ix  |            64 |        30057
 16404 |      16436 | public     | tab311  | tab311_demo_ix |          1983 |        30033

何故、Inde Only Scanなのに表ブロックをアクセスしてしまうのか、わかった〜〜〜っ、と思う。（vacuum analyze が必要らしい。ちなみに前述の結果はデータをINSERTし、analyzeコマンドだけを実施した状態だった）

気持ちを落ち着けて〜〜〜！

[oracle@leaffish ˜]$ psql -U oracle scott
タイミングは on です。
psql (9.2beta4)
"help" でヘルプを表示します.

scott=# vacuum analyze tab311;
scott=# vacuum analyze tab31;

こんどこそ！　できた〜〜〜〜っ！と思う。
Heap Fetchesも０だし、表ブロックへのアクセスもほぼない。ほぼない。大切なので２度書きました。表ブロックへのアクセスは０にはならないようです。今のところ。こまけーことはいいんだよ的で、ワイルド。 :)　数ブロックアクセスしているのはどのようなブロックなのでしょうね

scott=> explain analyze verbose
scott-> select
scott->     t1.unique_id,
scott->     t1.item_code,
scott->     (
scott(>        select
scott(>            max(t3.unique_id)
scott(>        from
scott(>            tab31 t2 join tab311 t3
scott(>            on
scott(>                t2.sub_item_code = t3.sub_item_code
scott(>                and t3.is_delete = 0
scott(>        where
scott(>            t2.item_code = t1.item_code
scott(>            and t2.is_delete = 0
scott(>     ) current_sub_item
scott-> from
scott->     tab3 t1
scott-> where
scott->     t1.unique_id between 1 and 10000
scott->     and t1.is_delete = 0
scott->     and t1.status_code = '00'
scott-> ;
                                                                           QUERY PLAN                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tab3_pk on public.tab3 t1  (cost=0.00..188801.90 rows=9921 width=22) (actual time=0.255..1116.525 rows=10000 loops=1)
   Output: t1.unique_id, t1.item_code, (SubPlan 1)
   Index Cond: ((t1.unique_id >= 1::numeric) AND (t1.unique_id <= 10000::numeric))
   Filter: ((t1.is_delete = 0::numeric) AND (t1.status_code = '00'::bpchar))
   SubPlan 1
     ->  Aggregate  (cost=18.95..18.96 rows=1 width=6) (actual time=0.108..0.109 rows=1 loops=10000)
           Output: max(t3.unique_id)
           ->  Nested Loop  (cost=0.00..18.94 rows=4 width=6) (actual time=0.019..0.081 rows=40 loops=10000)
                 Output: t3.unique_id
                 ->  Index Only Scan using tab31_demo_ix on public.tab31 t2  (cost=0.00..9.13 rows=1 width=11) (actual time=0.005..0.005 rows=1 loops=10000)
                       Output: t2.item_code, t2.is_delete, t2.sub_item_code
                       Index Cond: ((t2.item_code = t1.item_code) AND (t2.is_delete = 0::numeric))
                       Heap Fetches: 0
                 ->  Index Only Scan using tab311_demo_ix on public.tab311 t3  (cost=0.00..9.32 rows=49 width=17) (actual time=0.012..0.036 rows=40 loops=10000)
                       Output: t3.sub_item_code, t3.is_delete, t3.unique_id
                       Index Cond: ((t3.sub_item_code = t2.sub_item_code) AND (t3.is_delete = 0::numeric))
                       Heap Fetches: 0
 Total runtime: 1121.140 ms
(18 行)

時間: 1123.194 ms
 

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
 relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 16404 | public     | tab311  |              0 |             3 |             0 |        32015 |               0 |              0 |              0 |             0
 16398 | public     | tab31   |              0 |             1 |             0 |        30120 |               0 |              0 |              0 |             0

scott=>  select * from pg_statio_user_indexes where relname in ('tab31','tab311');
 relid | indexrelid | schemaname | relname |  indexrelname  | idx_blks_read | idx_blks_hit 
-------+------------+------------+---------+----------------+---------------+--------------
 16404 |      16414 | public     | tab311  | tab311_pk      |             0 |            0
 16398 |      16421 | public     | tab31   | tab31_demo_ix  |             0 |        30120
 16404 |      16422 | public     | tab311  | tab311_demo_ix |             0 |        32015

---

参考：
・INDEX FULL SCANを狙う - MySQL Casual Advent Calendar 2011
・Covering Index と self-join と MySQL
・How are index-only scans implemented in InnoDB
・PostgreSQL 9.2 highlight: Index-only scans
・PostgreSQLアーキテクチャ入門（INSIGHT OUT 2011）

オープンソースカンファレンス2011.DB に参加した

OSC.DBって随分ご無沙汰してたと思ったら、前回参加したOSC.DBは、2007年でしたか。息子ちゃんが生まれた年です：）

OSC.DBも2008年以来、久々に開催でしたし、久々にお会いできた方々もいて :) でした。

全てのセッションには参加できなかったので、午前中だけ。

参加したセッションは

• PostgreSQL 9.1 and more - 日本PostgreSQLユーザ会/永安 悟史
• OSSDB MySQL - 日本MySQLユーザ会/とみた まさひろ・須藤 功平
• Windowsで使う! Firebird !! - Firebird日本ユーザー会/木村 明治と 愉快な仲間

とOSC.DBではおなじみのユーザ（ー）会のセッションでした。

PostgreSQL 9.1 は Insight outで聞けなかったところ？を駆け足で聴いた感じ。
(9.1からサポートされたIndes only scanはOracleでもよく使うチューニング方法ですね。あとは、カスケードレプリケーションとか、pg_basebackup関連とか）

MySQLは、MySQL5.6の話とストレージエンジンである groongaストレージエンジン関連をこれまた駆け足で。
(InnoDBオプティマイザ統計情報の永続化、デッドロックをエラーログに出力とか、groongaストレージエンジンには、Spiderエンジンの斯波さんも関わっているとか、MariaDBにバンドルされることになったとか、http://labs.mysql.com/ とか）

３０分ぐらいだとどうしても駆け足になってしまいますよね。皆さん早口ですよね。
（いままでで一番高速な語りは、大規模Web サイトでのMySQL導入方法および事例紹介」セミナーの松信さんだったように思います。速すぎて日本語聞き取れねーと思ったのは人生初。おおげさかw）

お腹が減ってきたランチ前のセッションは、Firebird日本ユーザー会。ユーザーなんですよユーザじゃなくて。

木村さんと、林さんの軽快なトークが炸裂（アドリブだったらしいw) したFirefoxじゃなくてFirebirdの話。
（ブラジルではFirebird関連イベントで800人ぐらい軽く集まるらしい。すげー）

そして、ほぼパーフェクトな記録発見。 Thx （やっぱ、iPhoneだけだとメモれないというかつぶやききれないw のだ)
http://emasaka.blog65.fc2.com/blog-entry-952.html

鬼熱かった！ :: Insight out 2011- DB tech showcase

書くのおそくなっちゃいました　m(_ _)m

１０月１９日〜２１日に開催されたInsight out 2011- DB tech showcase
に、つまみ食いながらなんとか参加し、インサイトテクノロジーさんの鬼熱い魂を感じてきた :)

無理矢理空き時間作って参加したセッションは以下の通り。

• Deep Dive into Oracle Database Patch (Oracle) - 諸橋渉
• Why Why is probably the right answer (Oracle) - Tom Kyte
• Rac Buffer Sharingの仕組み (Oracle) - 山下正
• Effective Indexing (Oracle) - Tom Kyte
• New challenges Information security technologies are facing (others) - David Maman
• Developer and Indexes (Oracle) - Anjo Kolk

MySQLとかPostgreSQLとかOSSなのはOSCとかでも聴けるかなーと思いきづいたらOracle中心だったw

Effective Indexing/Developer and Indexes というセッションは予想以上だった、Indexを理解してるのって重要だよなーと改めて感じたセッションだった。

Tom Kyteさんが紹介していた書籍、「Relational Database Index Design and the Optimizers」

鬼熱い語りの山下さんのセッション、前回のOOWのUnconferenceの続編か？と思わせるような諸橋さんのセッション、つい引き込まれちゃいましたよ：）

参加者やスピーカが鬼熱いエンジニアであることは間違いないが、何と言っても、世界中からデータベースに関わる凄い方々を集めてしまうインサイトテクノロジーさんが一番、鬼熱いんじゃないかと感じた３日間だった。

来年も開催して頂きたいイベントだ。