Difference work using index condition 'OR' databases Mysql and PostgeSQL

I have MySQL not using indexes for the two indexed columns condition OR OR. If more, the table has several columns bear on them by the indexes and then done the sample for these columns using the "OR". Codes do not work. I decided to investigate this point in comparison with PostgreSQL, as this point in time I set for myself a goal to get acquainted in PostgreSQL.

To illustrate, run the following SQL queries to two different databases. To start, repeat the situation with the condition "OR" in MySQL.

1. Create a test table.

the

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

2. Insert multiple values.

the

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. Create indexes on the two columns.

the

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

4. Do request a sample on two columns using a condition "OR".

the

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)

In this case clearly shows that a MySQL database in the sample does not use any of the two indices. The standard solution in this situation is to use a union to fix the created indexes.

the

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. Do a similar table with data in the database PostgeSQL and try to make a similar case with the condition "OR".

the

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

Indexes do not work, try the previously used approach union.

the

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)

Indexes are not used.

Having heard that PostgeSQL is running more efficiently with indexes than MySQL suspect that
apparently PostgeSQL little data in the table and therefore generate more data.

the

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

With such volumes the indexes are used and, indeed, PostgreSQL is able to work with "OR" condition.

the

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)

Article based on information from habrahabr.ru