Comparing Optimizer Results

Fri, 2022-11-18 10:59 — Shinguz

A while ago I read a paper about Optimizer Benchmarks: How Good Are Query Optimizers, Really? by Viktor Leis from Fakultät für Informatik of the Technische Universität München.

Because we have from time to time problems with slow queries of customers especially after upgrading to new database releases it is interesting for me, how different optimizers cope with a query. Sometimes it is not clear to me why the query is slow or how I can make it faster. So the mentioned paper inspired me to compare the same query with the same dataset among different optimizers or optimizer versions. The most recent query from our customer we were testing against MariaDB 10.6, 10.9, 10.10, MySQL 8.0 and PostgreSQL 15.1.

The test data

The table we were using is our generic test table which many people already know from our MariaDB and MySQL trainings:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(128) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
);

Loading the data from MySQL

How to dump the data from MySQL to load them into PostgreSQL I have already described earlier. This is about how to create the table and load the data into PostgreSQL:

# sudo su - postgres
# psql
# postgres=# SELECT VERSION();
# postgres=# \l
# postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# \c test
postgres=# CREATE TABLE test (
  id SERIAL NOT NULL PRIMARY KEY,
  data VARCHAR(128) DEFAULT NULL,
  ts TIMESTAMP NOT NULL
);
test=# \dt+
                                     List of relations
 Schema |   Name   | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+----------+-------+----------+-------------+---------------+---------+-------------
 public | test     | table | postgres | permanent   | heap          | 0 bytes |

# psql test < /tmp/test_dump.sql

The Query

The query we had problems with looked something like this:

WITH t AS (
SELECT SUM(id)+0.0 AS c, data AS pn
  FROM test
 WHERE ts BETWEEN '2022-11-22 00:00:00' AND '2022-11-22 23:59:59'
   AND data LIKE 'ab%'
 GROUP BY data
 ORDER BY data
-- MariaDB 10.6: 0.66s
-- MariaDB 10.9: 0.37s
-- Competing product: 0.70s
)
SELECT 'AAA' AS label, ((SELECT c FROM t WHERE pn = 'ab06d9ca0e5eb27e5bb2e5c0f345b782')+(SELECT c FROM t WHERE pn = 'ab0f8e861348995d1cbf9ec21d4bf52d'))/((SELECT c FROM t WHERE pn = 'ab134910f6808802cf44b587fb825ab5')+(SELECT c FROM t WHERE pn = 'ab1d6682feb41a97054e8c634ecd5552'))*100 AS value
-- MariaDB 10.6: 2.04s
-- MariaDB 10.9: 0.79s
-- Competing product: 0.70s
UNION
SELECT 'BBB' AS label, (SELECT c FROM t WHERE pn = 'ab2516b576d7463f106b5671bdac6d32')/((SELECT c FROM t WHERE pn = 'ab28e05f4fa736f4738a2ff6cf494f04')+(SELECT c FROM t WHERE pn = 'ab31ab6a7d52fee49990d63dee58371b'))*100 AS value
-- MariaDB 10.6: 3.54s
-- MariaDB 10.9: 1.38s
-- Competing product: 0.70s
UNION
SELECT 'CCC' AS label, (SELECT c FROM t WHERE pn = 'ab390b211d7bb3cc19fc1f808fb1bc3a')/(SELECT c FROM t WHERE pn = 'ab45af9e4ca10d3a618d40ec3f230349')*100 AS value
-- MariaDB 10.6: 4.58s
-- MariaDB 10.9: 1.76s
-- Competing product: 0.70s
UNION
SELECT 'DDD' AS label, ((SELECT c FROM t WHERE pn = 'ab531756624d8287d38eb5fe5809bdb4')+(SELECT c FROM t WHERE pn = 'ab5aaa230cc64c15a61f908aea1c3f9c'))/((SELECT c FROM t WHERE pn = 'ab60f7712cd176b6ac26040d69be0e11')+(SELECT c FROM t WHERE pn = 'ab674e464997f4916e7ed23942c53d23'))*100 AS value
UNION
SELECT 'EEE' AS label, (SELECT c FROM t WHERE pn = 'ab6cc3781441a08d67eb8039521c63bb')/((SELECT c FROM t WHERE pn = 'ab7612bca85db4f6c4638a658fbee646')+(SELECT c FROM t WHERE pn = 'ab7811882f520c2051c6761edf4c306f'))*100 AS value
UNION
SELECT 'FFF' AS label, (SELECT c FROM t WHERE pn = 'ab7f0bd3758fbdac0ff6abb24db166ad')/(SELECT c FROM t WHERE pn = 'ab87d7d598ad47ca5db3bf9e6cbdee48')*100 AS value
-- MariaDB 10.6: 9.15s
-- MariaDB 10.9: 3.51s
-- Competing product: 0.71s
;

The results

Timing is enabled in PostgreSQL as follows:

postgres=# \timing

Then we were running the different queries against the different databases and versions:

	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8
MariaDB 10.6.11	0.14 s	0.32 s	0.79 s	1.39 s	1.78 s	3.56 s	3.32 s	0.46 s
MariaDB 10.9.4	0.14 s	0.33 s	0.80 s	1.40 s	1.80 s	3.60 s	3.40 s	0.46 s
MariaDB 10.10.2	0.14 s	0.33 s	0.80 s	1.39 s	1.78 s	3.55 s	3.40 s	0.45 s
MySQL 8.0.31	0.03 s	0.77 s	0.77 s	0.77 s	0.77 s	0.77 s	1.31 s	1.39 s/0.44 s
PostgreSQL 15.1	0.03 s	0.04 s	0.04 s	0.04 s	0.04 s	0.04 s	0.65 s	0.35 s

Other sources:

Hacker News: What I found strange about MariaDB is that it is ~10 times slower than MySQL on OLAP queries

The Queries

Query 1:

SELECT COUNT(*) FROM test;

Query 2

SELECT SUM(id)+0.0 AS c, data AS pn
  FROM test
 WHERE ts BETWEEN '2022-11-22 00:00:00' AND '2022-11-22 23:59:59'
   AND data LIKE 'ab%'
 GROUP BY data
 ORDER BY data;

Query 3

WITH t AS (
SELECT SUM(id)+0.0 AS c, data AS pn
  FROM test
 WHERE ts BETWEEN '2022-11-22 00:00:00' AND '2022-11-22 23:59:59'
   AND data LIKE 'ab%'
 GROUP BY data
 ORDER BY data
)
SELECT 'AAA' AS label, ((SELECT c FROM t WHERE pn = 'ab06d9ca0e5eb27e5bb2e5c0f345b782')+(SELECT c FROM t WHERE pn = 'ab0f8e861348995d1cbf9ec21d4bf52d'))/((SELECT c FROM t WHERE pn = 'ab134910f6808802cf44b587fb825ab5')+(SELECT c FROM t WHERE pn = 'ab1d6682feb41a97054e8c634ecd5552'))*100 AS value;

Query 4

WITH t AS (
SELECT SUM(id)+0.0 AS c, data AS pn
  FROM test
 WHERE ts BETWEEN '2022-11-22 00:00:00' AND '2022-11-22 23:59:59'
   AND data LIKE 'ab%'
 GROUP BY data
 ORDER BY data
)
SELECT 'AAA' AS label, ((SELECT c FROM t WHERE pn = 'ab06d9ca0e5eb27e5bb2e5c0f345b782')+(SELECT c FROM t WHERE pn = 'ab0f8e861348995d1cbf9ec21d4bf52d'))/((SELECT c FROM t WHERE pn = 'ab134910f6808802cf44b587fb825ab5')+(SELECT c FROM t WHERE pn = 'ab1d6682feb41a97054e8c634ecd5552'))*100 AS value
UNION
SELECT 'BBB' AS label, (SELECT c FROM t WHERE pn = 'ab2516b576d7463f106b5671bdac6d32')/((SELECT c FROM t WHERE pn = 'ab28e05f4fa736f4738a2ff6cf494f04')+(SELECT c FROM t WHERE pn = 'ab31ab6a7d52fee49990d63dee58371b'))*100 AS value;

Query 5

WITH t AS (
SELECT SUM(id)+0.0 AS c, data AS pn
  FROM test
 WHERE ts BETWEEN '2022-11-22 00:00:00' AND '2022-11-22 23:59:59'
   AND data LIKE 'ab%'
 GROUP BY data
 ORDER BY data
)
SELECT 'AAA' AS label, ((SELECT c FROM t WHERE pn = 'ab06d9ca0e5eb27e5bb2e5c0f345b782')+(SELECT c FROM t WHERE pn = 'ab0f8e861348995d1cbf9ec21d4bf52d'))/((SELECT c FROM t WHERE pn = 'ab134910f6808802cf44b587fb825ab5')+(SELECT c FROM t WHERE pn = 'ab1d6682feb41a97054e8c634ecd5552'))*100 AS value
UNION
SELECT 'BBB' AS label, (SELECT c FROM t WHERE pn = 'ab2516b576d7463f106b5671bdac6d32')/((SELECT c FROM t WHERE pn = 'ab28e05f4fa736f4738a2ff6cf494f04')+(SELECT c FROM t WHERE pn = 'ab31ab6a7d52fee49990d63dee58371b'))*100 AS value
UNION
SELECT 'CCC' AS label, (SELECT c FROM t WHERE pn = 'ab390b211d7bb3cc19fc1f808fb1bc3a')/(SELECT c FROM t WHERE pn = 'ab45af9e4ca10d3a618d40ec3f230349')*100 AS value;

Query 6

WITH t AS (
SELECT SUM(id)+0.0 AS c, data AS pn
  FROM test
 WHERE ts BETWEEN '2022-11-22 00:00:00' AND '2022-11-22 23:59:59'
   AND data LIKE 'ab%'
 GROUP BY data
 ORDER BY data
)
SELECT 'AAA' AS label, ((SELECT c FROM t WHERE pn = 'ab06d9ca0e5eb27e5bb2e5c0f345b782')+(SELECT c FROM t WHERE pn = 'ab0f8e861348995d1cbf9ec21d4bf52d'))/((SELECT c FROM t WHERE pn = 'ab134910f6808802cf44b587fb825ab5')+(SELECT c FROM t WHERE pn = 'ab1d6682feb41a97054e8c634ecd5552'))*100 AS value
UNION
SELECT 'BBB' AS label, (SELECT c FROM t WHERE pn = 'ab2516b576d7463f106b5671bdac6d32')/((SELECT c FROM t WHERE pn = 'ab28e05f4fa736f4738a2ff6cf494f04')+(SELECT c FROM t WHERE pn = 'ab31ab6a7d52fee49990d63dee58371b'))*100 AS value
UNION
SELECT 'CCC' AS label, (SELECT c FROM t WHERE pn = 'ab390b211d7bb3cc19fc1f808fb1bc3a')/(SELECT c FROM t WHERE pn = 'ab45af9e4ca10d3a618d40ec3f230349')*100 AS value
UNION
SELECT 'DDD' AS label, ((SELECT c FROM t WHERE pn = 'ab531756624d8287d38eb5fe5809bdb4')+(SELECT c FROM t WHERE pn = 'ab5aaa230cc64c15a61f908aea1c3f9c'))/((SELECT c FROM t WHERE pn = 'ab60f7712cd176b6ac26040d69be0e11')+(SELECT c FROM t WHERE pn = 'ab674e464997f4916e7ed23942c53d23'))*100 AS value
UNION
SELECT 'EEE' AS label, (SELECT c FROM t WHERE pn = 'ab6cc3781441a08d67eb8039521c63bb')/((SELECT c FROM t WHERE pn = 'ab7612bca85db4f6c4638a658fbee646')+(SELECT c FROM t WHERE pn = 'ab7811882f520c2051c6761edf4c306f'))*100 AS value
UNION
SELECT 'FFF' AS label, (SELECT c FROM t WHERE pn = 'ab7f0bd3758fbdac0ff6abb24db166ad')/(SELECT c FROM t WHERE pn = 'ab87d7d598ad47ca5db3bf9e6cbdee48')*100 AS value;

Query 7

The table:

MariaDB/MySQL	PostgreSQL
CREATE TABLE `queue_destinations` ( `id` int(11) NOT NULL AUTO_INCREMENT, `queue` int(11) NOT NULL, `dtype` varchar(100) NOT NULL, `dnumber` varchar(255) NOT NULL, `available` smallint(6) NOT NULL DEFAULT 1, `priority` smallint(6) NOT NULL DEFAULT 1, `lasttime` bigint(20) NOT NULL DEFAULT 0, `nexttime` bigint(20) NOT NULL DEFAULT 0, `active_call` varchar(100) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `queue` (`queue`,`dtype`,`dnumber`), KEY `dtype` (`dtype`,`dnumber`), KEY `available` (`available`), KEY `priority` (`priority`), KEY `lasttime` (`lasttime`), KEY `nexttime` (`nexttime`), KEY `active_call` (`active_call`) );	CREATE TABLE queue_destinations ( id SERIAL NOT NULL PRIMARY KEY, queue int NOT NULL, dtype varchar(100) NOT NULL, dnumber varchar(255) NOT NULL, available smallint NOT NULL DEFAULT 1, priority smallint NOT NULL DEFAULT 1, lasttime bigint NOT NULL DEFAULT 0, nexttime bigint NOT NULL DEFAULT 0, active_call varchar(100) NOT NULL, CONSTRAINT queue UNIQUE (queue, dtype, dnumber) ); CREATE INDEX dtype_idx ON queue_destinations (dtype,dnumber); CREATE INDEX available ON queue_destinations (available); CREATE INDEX priority ON queue_destinations (priority); CREATE INDEX lasttime ON queue_destinations (lasttime); CREATE INDEX nexttime ON queue_destinations (nexttime); CREATE INDEX active_call ON queue_destinations (active_call);

MariaDB/MySQL

PostgreSQL

CREATE TABLE `queue_destinations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `queue` int(11) NOT NULL,
  `dtype` varchar(100) NOT NULL,
  `dnumber` varchar(255) NOT NULL,
  `available` smallint(6) NOT NULL DEFAULT 1,
  `priority` smallint(6) NOT NULL DEFAULT 1,
  `lasttime` bigint(20) NOT NULL DEFAULT 0,
  `nexttime` bigint(20) NOT NULL DEFAULT 0,
  `active_call` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `queue` (`queue`,`dtype`,`dnumber`),
  KEY `dtype` (`dtype`,`dnumber`),
  KEY `available` (`available`),
  KEY `priority` (`priority`),
  KEY `lasttime` (`lasttime`),
  KEY `nexttime` (`nexttime`),
  KEY `active_call` (`active_call`)
);

CREATE TABLE queue_destinations (
  id SERIAL NOT NULL PRIMARY KEY,
  queue int NOT NULL,
  dtype varchar(100) NOT NULL,
  dnumber varchar(255) NOT NULL,
  available smallint NOT NULL DEFAULT 1,
  priority smallint NOT NULL DEFAULT 1,
  lasttime bigint NOT NULL DEFAULT 0,
  nexttime bigint NOT NULL DEFAULT 0,
  active_call varchar(100) NOT NULL,
  CONSTRAINT queue UNIQUE (queue, dtype, dnumber)
);

CREATE INDEX dtype_idx ON queue_destinations (dtype,dnumber);
CREATE INDEX available ON queue_destinations (available);
CREATE INDEX priority ON queue_destinations (priority);
CREATE INDEX lasttime ON queue_destinations (lasttime);
CREATE INDEX nexttime ON queue_destinations (nexttime);
CREATE INDEX active_call ON queue_destinations (active_call);

The Query:

SELECT *
  FROM queue_destinations a
 WHERE queue = 45393
   AND available = 1
   AND nexttime <= 1669284432
   AND active_call = ''
   AND (
        SELECT COUNT(*)
         FROM queue_destinations b
        WHERE b.dnumber = a.dnumber
          AND active_call != ''
       ) = 0
ORDER BY priority DESC, lasttime FOR UPDATE
;

The Query Execution Plan:

+------+--------------------+-------+------+--------------------------+-------+---------+-------+--------+-----------------------------+
| id   | select_type        | table | type | possible_keys            | key   | key_len | ref   | rows   | Extra                       |
+------+--------------------+-------+------+--------------------------+-------+---------+-------+--------+-----------------------------+
|    1 | PRIMARY            | a     | ref  | queue,available,nexttime | queue | 4       | const | 5      | Using where; Using filesort |
|    2 | DEPENDENT SUBQUERY | b     | ALL  | NULL                     | NULL  | NULL    | NULL  | 955596 | Using where                 |
+------+--------------------+-------+------+--------------------------+-------+---------+-------+--------+-----------------------------+

-> Sort: a.priority DESC, a.lasttime  (cost=1.26 rows=5)
  -> Filter: ((a.active_call = '') and (a.available = 1) and (a.nexttime <= 1669284432) and ((select #2) = 0))
      -> Index lookup on a using queue (queue=45393)
      -> Select #2 (subquery in condition; dependent)
          -> Aggregate: count(0)  (cost=30637.14 rows=1)
              -> Filter: ((b.dnumber = a.dnumber) and (b.active_call <> ''))  (cost=21409.85 rows=92273)
                  -> Table scan on b  (cost=21409.85 rows=1025255)

LockRows  (cost=395076.59..395076.61 rows=1 width=101)                                                                                                                                                                                                                   
   ->  Sort  (cost=395076.59..395076.60 rows=1 width=101)                                                                                                                                                                                                                 
         Sort Key: a.priority DESC, a.lasttime                                                                                                                                                                                                                            
         ->  Index Scan using queue on queue_destinations a  (cost=0.42..395076.58 rows=1 width=101)                                                                                                                                                                      
               Index Cond: (queue = 45393)                                                                                                                                                                                                                                
               Filter: ((nexttime <= 1669284432) AND (available = 1) AND ((active_call)::text = ''::text) AND ((SubPlan 1) = 0))                                                                                                                                          
               SubPlan 1                                                                                                                                                                                                                                                  
                 ->  Aggregate  (cost=32918.64..32918.65 rows=1 width=8)                                                                                                                                                                                                  
                       ->  Seq Scan on queue_destinations b  (cost=0.00..32918.64 rows=1 width=0)                                                                                                                                                                         
                             Filter: (((active_call)::text <> ''::text) AND ((dnumber)::text = (a.dnumber)::text))                                                                                                                                                        
 JIT:                                                                                                                                                                                                                                                                     
   Functions: 12
   Options: Inlining false, Optimization false, Expressions true, Deforming true

Query 8

SELECT * FROM test WHERE data IS NULL;

MariaDB 10.x:
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | test  | ALL  | data          | NULL | NULL    | NULL | 1047013 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+

MySQL 8 (QEP is wrong!):
+----+-------------+-------+------------+------+---------------+------+---------+-------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows   | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+------+---------+-------+--------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | ref  | data          | data | 515     | const | 523506 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+------+---------+-------+--------+----------+-----------------------+

PostgreSQL 15:
 Seq Scan on test  (cost=0.00..25952.76 rows=1047632 width=44)
   Filter: (data IS NULL)

99.9% of rows are NULL in column data. This test is coming from: MariaDB indexing of NULL values

Conclusion

This little tests showed some results I have not expected:

MariaDB Optimizer does some bad things on this query. OK, this was expected. Bug is reported: MDEV-30017.
The originally very bad execution times from MariaDB 10.6 (see timing notes in the first query) were not reproducible any more the next day. I should consider more to run ANALYZE TABLE. I should have know this because it is not the first time I experienced this.
We have a lot of variations between different measurements. This I should investigate a bit more later...
In the MariaDB Server Fest 2022 yesterday I heard with MariaDB 11 Optimizer everything will become much better... Wait! there is something more I do not understand and which I did not expect:
PostgreSQL response time is constantly dramatic better for these queries. I checked the docu but did not find anything beside their normal cache which would explain this (something similar to the MariaDB Query Cache for example). If somebody has a clue why they manage to respond so fast or why we respond so slow I would be happy for a hint...

Taxonomy upgrade extras:

Shinguz's blog
Log in or register to post comments

Languages

Login

You are here

Comparing Optimizer Results

The test data

Loading the data from MySQL

The Query

The results

Other sources:

The Queries

Query 1:

Query 2

Query 3

Query 4

Query 5

Query 6

Query 7

Query 8

Conclusion

Navigation

Trademarks

Languages

Login

Search form

You are here

Comparing Optimizer Results

The test data

Loading the data from MySQL

The Query

The results

Other sources:

The Queries

Query 1:

Query 2

Query 3

Query 4

Query 5

Query 6

Query 7

Query 8

Conclusion

Navigation

Trademarks