「索引列を演算するとインデックスが使えなくなる」これはなぜなのか
例えばテーブルt1の列col1にインデックスがあり、以下のクエリでそのインデックスが参照される場合、
select * from t1 where col1 >= 20 --(1)
クエリを以下のように書き換えると
select * from t1 where col1*5 >= 100 --(2)
インデックスが使えなくなってしまう、だから索引列は極力演算したり関数に渡したりしない方がよい、という話がある。
実際、PostgreSQL9.1.2やTeradata12では(1)はインデックスシーク、(2)はテーブルスキャンになってしまう。
しかし、B-treeインデックスには列値そのものが載っているのだから、(2)でインデックスを使えない理由はないような気がする。
インデックス読み出して「col1の値が20以上か」をチェックする代わりに、「col1の値を5倍して100以上になるか」をチェックすればよいだけではないのか。
SQL Serverならインデックスを使ってくれるが...
実際、SQL Server 2008は(2)のクエリについてもインデックスを使ってくれる。
サンプルDB AdventureWorks の SalesOrderDetail テーブルのOrderQtyにインデックスを張り、まずは
select * from Sales.SalesOrderDetail where OrderQty >= 20
でインデックスが使用されることを確認する(実行計画右上の Index Seek)。
次にOrderQtyを×5してみると、
select * from Sales.SalesOrderDetail where OrderQty*5 >= 100
ただし、掛け算することによって実行計画右上の Index Seek (Nonclustered) が Index Scan (Nonclustered) に変わってしまっている。
つまりルートノードを出発点としてB-treeインデックスを「L字型」に読むのではなく、リーフノードを端から全部読んでいるのだ。ここがわからない。
なぜシークできずスキャンするのか。リーフノードに対して可能な演算と比較が、ルートノード/ブランチノードに対してはできない事情があるようだが、それは何か?