The difference in the use of indexes in the condition 'OR' databases Mysql and PostgeSQL

Many people know the problem of MySQL in not using indexes for two indexable columns in the condition "OR". If in more detail, there are several columns in the table with indices placed on them and then a selection is made of these columns using the “OR” condition. Indexes do not work. I decided to explore this point in comparison with PostgreSQL, since at the present moment I set a goal for myself to learn a little about PostgreSQL.

To illustrate, we will execute the following SQL queries for two different databases. To begin, repeat the situation with the condition "OR" in MySQL.

1. Create a test pattern.

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

2. Insert several values.
')

 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 in two columns.

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

4. Make a request with a sample of two columns through the condition "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) 

In this case, it is clearly seen that the MySQL database does not use any of the two indices when sampling. The standard solution in this situation is to use union to fix the use of the created indexes.

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

 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 union approach.

 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) 

Indices are not used.

Hearing that PostgeSQL works more efficiently with indexes than MySQL suspected that
apparently PostgeSQL has little data in the table and therefore generates more data.

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

With such volumes, indexes are used and indeed PostgreSQL can work with the “OR” condition.

 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)