You are here
Comparing Optimizer Results
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); |
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...
- Shinguz's blog
- Log in or register to post comments