Различие работы в использовании индексов в условии 'OR' баз данных Mysql и PostgeSQL

Многим известна проблема MySQL в не использовании индексов для двух индексируемых колонок в условии «OR». Если подробнее, в таблице есть несколько колонок с проставленными по ним индексами и затем делается выборка по этим колонкам с использованием условия «OR». Индексы не работают. Я решил исследовать этот момент в сравнении с PostgreSQL, так как в настоящий момент времени поставил для себя цель немного познакомиться в PostgreSQL.

Для иллюстрации выполним следующие SQL запросы для двух разных баз данных. Для начала повторим ситуацию с условием «OR» в MySQL.

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. Создаем индексы по двум колонкам.

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


4. Делаем запрос с выборкой по двум колонкам через условие «OR».

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 при выборке не использует ни какой из двух индексов. Стандартное решение в этой ситуации это использовать union, чтобы зафиксировать использование созданных индексов.

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))

Индексы не срабатывают, пробуем ранее используемых подход union.

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)


Индексы не используются.

Наслышавшись о том, что PostgeSQL работает более эффективно с индексами нежели MySQL заподозрил о том, что
видимо 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)
Поделиться публикацией
Похожие публикации
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Реклама
Комментарии 5
  • +15
    Справедливости ради, в mysql тоже данных допишите побольше.
    mysql> explain select * from test where a=4756 or b=45 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: test
       partitions: NULL
             type: index_merge
    possible_keys: aidx,bidx
              key: aidx,bidx
          key_len: 5,5
              ref: NULL
             rows: 15
         filtered: 100.00
            Extra: Using union(aidx,bidx); Using where
    1 row in set, 1 warning (0,00 sec)
    

    index merge в mysql 5.5 завезли: http://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html
    • +2

      Если нужно потестить индексы в Postgres — используйте


      set local enable_seqscan=off;

      который поднимет стоимость seqscan до 100млн, а планировщик будет использовать его, как last resort.

      • 0
        А покажите, пожалуйста, как оно будет работать на объемах 5 и 10 млн записей?
        • –1
          Оптимизатор запросов штука довольно умная и оперирует в т.ч. данными о количестве строк в таблице. Когда их там 10, то ему проще сделать table scan и не заморачиваться с индексами. Смотри ещё force index.

          Так что дело не в том умеет что-то MySQL или нет.
          • 0
            В PostgeSQL дело не только в количестве данных. Планировщик пытается предугадать как эффективнее делать выборку — с индексами или без. Если, согласно прогнозу, в выборку попадет достаточно большое количество записей, то из-за того, что данные читаются не по записям а блоками, может получиться, что независимо от использования индексов, придется читать всю таблиц. В таком случае, чтение индексов — излишняя трата.

            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.