Многим известна проблема 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=1G;
*************************** 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=1G;
*************************** 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)
Автор: bizzonaru
