MysqlおよびPostgeSQLデータベースの「OR」句でのインデックスの使用の違い

多くの人は、「OR」条件で2つのインデックス付きカラムにインデックスを使用しないというMySQLの問題を知っています。 より詳細には、テーブルにインデックスが付加された複数の列があり、「OR」条件を使用してこれらの列のサンプルが作成されます。 インデックスは機能しません。 現時点でPostgreSQLについて少し知りたいという目標を設定したため、PostgreSQLと比較してこの点を調査することにしました。



説明のために、2つの異なるデータベースに対して次のSQLクエリを実行します。 まず、MySQLで「OR」条件を使用して状況を繰り返しましょう。



1.テストパターンを作成します。

MariaDB [metemplate]> create table example (a int, b int);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

2.いくつかの値を挿入します。

 MariaDB [metemplate]> select * from example; +------+------+ | a | b | +------+------+ | 1 | 2 | | 4 | 1 | | 2 | 7 | | 9 | 9 | | 19 | 9 | | 1 | 19 | | 11 | 12 | | 16 | 10 | +------+------+ 8 rows in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

3. 2つの列にインデックスを作成します。

 MariaDB [metemplate]> create index a_idx on example(a); MariaDB [metemplate]> create index b_idx on example(b);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

4.「OR」条件を介して2列のサンプリングでクエリを作成します。

 MariaDB [metemplate]> explain select * from example where a=1 or b=1\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: example type: ALL possible_keys: a_idx,b_idx key: NULL key_len: NULL ref: NULL rows: 8 Extra: Using where 1 row in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この場合、MySQLデータベースはフェッチ時に2つのインデックスのどちらも使用しないことが明確にわかります。 この状況での標準的な解決策は、ユニオンを使用して、作成されたインデックスの使用をキャプチャすることです。

 MariaDB [metemplate]> explain select * from example where a=1 union select * from example where b=1\G; *************************** 1. row *************************** id: 1 select_type: PRIMARY table: example type: ref possible_keys: a_idx key: a_idx key_len: 5 ref: const rows: 2 Extra: *************************** 2. row *************************** id: 2 select_type: UNION table: example type: ref possible_keys: b_idx key: b_idx key_len: 5 ref: const rows: 1 Extra: *************************** 3. row *************************** id: NULL select_type: UNION RESULT table: <union1,2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: 3 rows in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

5. PostgeSQLデータベースのデータを使用して同様のテーブルを作成し、「OR」条件で同様のケースを作成しようとします。

 metemplate=# explain select * from example where a=1 or b=1; Seq Scan on example (cost=0.00..42.10 rows=21 width=8) Filter: ((a = 1) OR (b = 1))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

インデックスは機能しません。以前に使用されたユニオンアプローチを試してください。

 metemplate=# explain select * from example where a=1 union select * from example where b=1; HashAggregate (cost=73.83..74.05 rows=22 width=8) -> Append (cost=0.00..73.72 rows=22 width=8) -> Seq Scan on example (cost=0.00..36.75 rows=11 width=8) Filter: (a = 1) -> Seq Scan on example (cost=0.00..36.75 rows=11 width=8) Filter: (b = 1)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

インデックスは使用されません。



MySQLが疑っていたよりもPostgeSQLがインデックスでより効率的に動作することを聞いた

どうやらPostgeSQLのテーブルにはほとんどデータがないため、より多くのデータが生成されます。

 metemplate=# insert into example values (generate_series(1,10000), generate_series(1,100000));
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このようなボリュームでは、インデックスが使用され、実際にPostgreSQLは「OR」条件で動作できます。

 metemplate=# explain select * from example where a=1; Bitmap Heap Scan on example (cost=4.34..39.96 rows=10 width=8) Recheck Cond: (a = 1) -> Bitmap Index Scan on a_idx (cost=0.00..4.34 rows=10 width=0) Index Cond: (a = 1) metemplate=# explain select * from example where a=1 or b=1; Bitmap Heap Scan on example (cost=8.61..47.58 rows=11 width=8) Recheck Cond: ((a = 1) OR (b = 1)) -> BitmapOr (cost=8.61..8.61 rows=11 width=0) -> Bitmap Index Scan on a_idx (cost=0.00..4.34 rows=10 width=0) Index Cond: (a = 1) -> Bitmap Index Scan on b_idx (cost=0.00..4.27 rows=1 width=0) Index Cond: (b = 1)