https://mariadb.com/kb/en/optimizer-switch/にあるように、MariaDBのオプティマイザはかなり改良されている。
では、MariaDBのオプティマイザ/エクゼキュータはどの程度優秀か、４つのSELECT文の実行を通してMySQLと(ついでにPostgreSQLと)比較してみる。
(2014.12.3追記：オプティマイザについては省略してますが、こんな本がでます。)
結論を先にいえば「MySQLは検索が速い」というのは都市伝説。MariaDBはがんばってるけどPostgreSQLにはまだまだ及ばず。
＊念のため。これはベンチマークじゃないよ、オプティマイザ/エクゼキュータの機能比較です。
自分で再確認したい場合はこちらにスクリプト群と実験のやり方を簡単に書いたので参照のこと。
調査環境
同一マシンにMySQL5.6.14、MariaDB10.0.4、PostgreSQL9.3.1をインストールし、同一テーブルとデータで比較。
テーブル定義
国名country、市町村の人口city、市町村の病院数hospital、市町村の学校数schoolをテーブル定義する。
インデックスは”素直に”張ったつもり。
CREATE TABLE `country` ( `code` int(11) NOT NULL, `name` text, `continent` text, PRIMARY KEY (`code`), KEY `continent` (`continent`(10)), KEY `name` (`name`(10)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `city` ( `code` int(11) NOT NULL, `country` int(11) DEFAULT NULL, `population` int(11) DEFAULT NULL, PRIMARY KEY (`code`), KEY `population` (`population`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `hospital` ( `code` int(11) NOT NULL, `num` int(11) DEFAULT NULL, PRIMARY KEY (`code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `school` ( `code` int(11) NOT NULL, `num` int(11) DEFAULT NULL, PRIMARY KEY (`code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
データは乱数で発生させた。
mysql> SELECT count(*) FROM country; +----------+ | count(*) | +----------+ | 180 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM city; +----------+ | count(*) | +----------+ | 202133 | +----------+ 1 row in set (0.13 sec) mysql> SELECT count(*) FROM hospital; +----------+ | count(*) | +----------+ | 202133 | +----------+ 1 row in set (0.15 sec) mysql> SELECT count(*) FROM school; +----------+ | count(*) | +----------+ | 202133 | +----------+ 1 row in set (0.15 sec)
実行SQL
SQL１
この例をアレンジして実行する：
https://mariadb.com/kb/en/derived-table-merge-optimization/
SELECT count(*) FROM (SELECT * FROM city WHERE population > 3000000) AS big_city WHERE big_city.country = '85';
ついでに等価なSQL1'も実験してみる。
SELECT count(*) FROM city WHERE population > 3000000 AND city.country = '85';
SQL2
この例をアレンジして実行する：
https://mariadb.com/kb/en/firstmatch-strategy/
SELECT count(*) FROM country WHERE country.code IN
(SELECT city.country FROM city WHERE city.population > 5*1000*1000)
AND country.continent='Europe';
SQL３
SQL1に一つテーブルをJOINする。
SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital WHERE big_city.country = '85' AND big_city.code = hospital.code AND hospital.num = 10;
SQL４
さらに一つテーブルをJOINする。
SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital, school, country WHERE big_city.country = country.code AND country.name = 'Japan' AND big_city.code = hospital.code AND hospital.num = 100 AND hospital.code = school.code AND school.num < 100;
ついでにサブクエリを書き換えたSQL4'も試す。
SELECT count(*) FROM city, hospital, school, country WHERE population > 100*1000 AND city.country = country.code AND country.name = 'Japan' AND city.code = hospital.code AND hospital.num = 100 AND hospital.code = school.code AND school.num < 100;
測定結果と結論
実行結果は２度目のSQLの時間を計測している。よってデータはバッファ上にあるのでI/Oアクセスは生じていない。
| 測定結果 | MySQL5.6 | MariaDB10.0 | PostgreSQL9.3 |
|---|---|---|---|
| SQL１ | 0.55 sec | 0.15 sec | 0.07 sec |
| SQL１' | 0.16 sec | 0.15 sec | 0.07 sec |
| SQL２ | 0.24 sec | 0.21 sec | 0.10 sec |
| SQL３ | 0.30 sec | 0.15 sec | 0.13 sec |
| SQL４ | 0.68 sec | 0.21 sec | 0.09 sec |
| SQL４' | 0.20 sec | 0.20 sec | 0.09 sec |
測定結果とEXPLAINのスナップショット？は付録として下にある。
- SQL1 & SQL1'
- ここで”タブー”というだけあって、MySQLのサブクエリ処理のダメな点がはっきりした例。
- PostgreSQLはSQLを書き換えて、ただの単一テーブルのシーケンシャルスキャンになっている。MariaDBも同じような感じ。SQL1とSQL1'のEXPLAINを比較参照のこと。
- SQL2
- SQL3
- MySQLも(SQL1にひとつテーブルJOINしたにも関らず)SQL1より高速化している。多分WHERE条件が良かったのだろう(hospital.num=10固定なので、絞り込みできたようだ。)。しかしMariaDBとPostgreSQLには遠く及ばない。
- SQL4 & SQL4'
- サブクエリ＋テーブル３つのJOINとなるとMySQLは絶望的。
- MariaDBはサブクエリをうまくクリアして、MySQLよりも高速に処理を行っている。
- PostgreSQLはこういう複雑なものほど速い。
＊再度、念のため。これはベンチマークじゃないよ、オプティマイザ/エクゼキュータの機能比較です。
MySQLは評判通り、サブクエリもJOINも弱い。ただしSQL3のようにWHERE句の条件がよければサブクエリの弱点を隠蔽できる場合もある模様。
MariaDBはかなり頑張っている（特にサブクエリの処理）ように見える。
PostgreSQLはこの程度のSQLならなんの問題もなく高速に実行できる。サブクエリの書き換えも優秀だし、JOINもネステッドループ以外にHASHとMergeSortの計３種類ある。
個人的には全般的な性能と頑健性からPostgreSQLを薦めるけれども、せいぜい２、３個のテーブルJOINをスレッドプールで高速に捌くのであれば、MariaDBでもいいんじゃないかと思う*2。
付録：実行結果とEXPLAINのまとめ
SQL１ & SQL1'
- MySQL5.6
mysql> SELECT count(*) FROM (SELECT * FROM city WHERE population > 3000000)
-> AS big_city WHERE big_city.country = '85';
+----------+
| count(*) |
+----------+
| 1007 |
+----------+
1 row in set (0.55 sec)
mysql> EXPLAIN SELECT count(*) FROM (SELECT * FROM city WHERE population > 3000000)
-> AS big_city WHERE big_city.country = '85';
+----+-------------+------------+------+---------------+-------------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+-------------+---------+-------+--------+-------------+
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 5 | const | 10 | NULL |
| 2 | DERIVED | city | ALL | population | NULL | NULL | NULL | 192815 | Using where |
+----+-------------+------------+------+---------------+-------------+---------+-------+--------+-------------+
2 rows in set (0.00 sec)
mysql> SELECT count(*) FROM city WHERE population > 3000000 AND city.country = '85';
+----------+
| count(*) |
+----------+
| 1007 |
+----------+
1 row in set (0.16 sec)
mysql> EXPLAIN SELECT count(*) FROM city WHERE population > 3000000 AND city.country = '85';
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | city | ALL | population | NULL | NULL | NULL | 192815 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
- MariaDB10.0
MariaDB [big]> SELECT count(*) FROM (SELECT * FROM city WHERE population > 3000000)
-> AS big_city WHERE big_city.country = '85';
+----------+
| count(*) |
+----------+
| 1007 |
+----------+
1 row in set (0.15 sec)
MariaDB [big]> EXPLAIN SELECT count(*) FROM (SELECT * FROM city WHERE population > 3000000)
-> AS big_city WHERE big_city.country = '85';
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | city | ALL | population | NULL | NULL | NULL | 202407 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
MariaDB [big]> SELECT count(*) FROM city WHERE population > 3000000 AND city.country = '85';
+----------+
| count(*) |
+----------+
| 1007 |
+----------+
1 row in set (0.15 sec)
MariaDB [big]> EXPLAIN SELECT count(*) FROM city WHERE population > 3000000 AND city.country = '85';
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | city | ALL | population | NULL | NULL | NULL | 186544 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)
- PostgreSQL9.3
big=# SELECT count(*) FROM (SELECT * FROM city WHERE population > 3000000)
big-# AS big_city WHERE big_city.country = '85';
count
-------
1007
(1 row)
Time: 71.316 ms
big=# EXPLAIN SELECT count(*) FROM (SELECT * FROM city WHERE population > 3000000) AS big_city WHERE big_city.country = '85';
QUERY PLAN
---------------------------------------------------------------
Aggregate (cost=4025.42..4025.43 rows=1 width=0)
-> Seq Scan on city (cost=0.00..4022.98 rows=976 width=0)
Filter: ((population > 3000000) AND (country = 85))
(3 rows)
Time: 71.672 ms
big=# SELECT count(*) FROM city WHERE population > 3000000 AND city.country = '85';
count
-------
1007
(1 row)
Time: 71.765 ms
big=# EXPLAIN SELECT count(*) FROM city WHERE population > 3000000 AND city.country = '85';
QUERY PLAN
---------------------------------------------------------------
Aggregate (cost=4025.42..4025.43 rows=1 width=0)
-> Seq Scan on city (cost=0.00..4022.98 rows=976 width=0)
Filter: ((population > 3000000) AND (country = 85))
(3 rows)
SQL2
- MySQL5.6
mysql> SELECT count(*) FROM country WHERE country.code IN
-> (SELECT city.country FROM city WHERE city.population > 5*1000*1000) AND country.continent='Europe';
+----------+
| count(*) |
+----------+
| 39 |
+----------+
1 row in set (0.24 sec)
mysql> EXPLAIN SELECT count(*) FROM country WHERE country.code IN
-> (SELECT city.country FROM city WHERE city.population > 5*1000*1000)
-> AND country.continent='Europe';
+----+--------------+-------------+--------+-------------------+------------+---------+------------------+--------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------------+--------+-------------------+------------+---------+------------------+--------+------------------------------------+
| 1 | SIMPLE | country | ref | PRIMARY,continent | continent | 33 | const | 39 | Using index condition; Using where |
| 1 | SIMPLE | <subquery2> | eq_ref | <auto_key> | <auto_key> | 5 | big.country.code | 1 | NULL |
| 2 | MATERIALIZED | city | ALL | population | NULL | NULL | NULL | 192815 | Using where |
+----+--------------+-------------+--------+-------------------+------------+---------+------------------+--------+------------------------------------+
3 rows in set (0.00 sec)
- MariaDB10.0
MariaDB [big]> SELECT count(*) FROM country WHERE country.code
-> IN (SELECT city.country FROM city WHERE city.population > 5*1000*1000) AND country.continent='Europe';
+----------+
| count(*) |
+----------+
| 39 |
+----------+
1 row in set (0.21 sec)
MariaDB [big]> EXPLAIN SELECT count(*) FROM country WHERE country.code IN
-> (SELECT city.country FROM city WHERE city.population > 5*1000*1000)
-> AND country.continent='Europe';
+------+--------------+-------------+--------+-------------------+--------------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------------+--------+-------------------+--------------+---------+-------+--------+-------------+
| 1 | PRIMARY | country | ref | PRIMARY,continent | continent | 33 | const | 39 | Using where |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | |
| 2 | MATERIALIZED | city | ALL | population | NULL | NULL | NULL | 202407 | Using where |
+------+--------------+-------------+--------+-------------------+--------------+---------+-------+--------+-------------+
3 rows in set (0.00 sec)
- PostgreSQL9.3
big=# SELECT count(*) FROM country WHERE country.code IN
(SELECT city.country FROM city WHERE city.population > 5*1000*1000)
AND country.continent='Europe';
count
-------
39
(1 row)
Time: 95.607 ms
big=# EXPLAIN SELECT count(*) FROM country WHERE country.code IN
(SELECT city.country FROM city WHERE city.population > 5*1000*1000)
AND country.continent='Europe';
QUERY PLAN
------------------------------------------------------------------------------
Aggregate (cost=3816.62..3816.63 rows=1 width=0)
-> Hash Join (cost=3814.85..3816.52 rows=39 width=0)
Hash Cond: (city.country = country.code)
-> HashAggregate (cost=3810.11..3811.16 rows=105 width=4)
-> Seq Scan on city (cost=0.00..3517.65 rows=116985 width=4)
Filter: (population > 5000000)
-> Hash (cost=4.25..4.25 rows=39 width=4)
-> Seq Scan on country (cost=0.00..4.25 rows=39 width=4)
Filter: (continent = 'Europe'::text)
(9 rows)
SQL3
- MySQL5.6
mysql> SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000)
-> AS big_city, hospital WHERE big_city.country = '85' AND big_city.code = hospital.code AND hospital.num = 10;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.30 sec)
mysql> EXPLAIN
-> SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital
-> WHERE big_city.country = '85' AND big_city.code = hospital.code AND hospital.num = 10;
+----+-------------+------------+--------+---------------+---------+---------+---------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+---------------+--------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 96407 | Using where |
| 1 | PRIMARY | hospital | eq_ref | PRIMARY | PRIMARY | 4 | big_city.code | 1 | Using where |
| 2 | DERIVED | city | ALL | population | NULL | NULL | NULL | 192815 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+---------------+--------+-------------+
3 rows in set (0.00 sec)
- MariaDB10.0
MariaDB [big]> SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital
-> WHERE big_city.country = '85' AND big_city.code = hospital.code AND hospital.num = 10;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.15 sec)
MariaDB [big]> EXPLAIN SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital
-> WHERE big_city.country = '85' AND big_city.code = hospital.code AND hospital.num = 10;
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------+
| 1 | SIMPLE | city | ALL | PRIMARY,population | NULL | NULL | NULL | 202407 | Using where |
| 1 | SIMPLE | hospital | eq_ref | PRIMARY | PRIMARY | 4 | big.city.code | 1 | Using where |
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------+
2 rows in set (0.00 sec)
- PostgreSQL9.3
big=# SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital
WHERE big_city.country = '85' AND big_city.code = hospital.code AND hospital.num = 10;
count
-------
12
(1 row)
Time: 129.348 ms
big=# EXPLAIN SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital
big-# WHERE big_city.country = '85' AND big_city.code = hospital.code AND hospital.num = 10;
QUERY PLAN
--------------------------------------------------------------------------------
Aggregate (cost=7487.46..7487.47 rows=1 width=0)
-> Hash Join (cost=3455.58..7487.41 rows=19 width=0)
Hash Cond: (city.code = hospital.code)
-> Seq Scan on city (cost=0.00..4022.98 rows=1387 width=4)
Filter: ((population > 100000) AND (country = 85))
-> Hash (cost=3421.64..3421.64 rows=2715 width=4)
-> Seq Scan on hospital (cost=0.00..3421.64 rows=2715 width=4)
Filter: (num = 10)
(8 rows)
SQL４ & SQL4'
- MySQL5.6
mysql> SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital, school,
-> country WHERE big_city.country = country.code AND country.name = 'Japan' AND big_city.code = hospital.code
-> AND hospital.num = 100 AND hospital.code = school.code AND school.num < 100;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.68 sec)
mysql> EXPLAIN SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital, school, country
-> WHERE big_city.country = country.code AND country.name = 'Japan'
-> AND big_city.code = hospital.code AND hospital.num = 100
-> AND hospital.code = school.code AND school.num < 100;
+----+-------------+------------+--------+---------------+-------------+---------+------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+-------------+---------+------------------+--------+-------------+
| 1 | PRIMARY | country | ref | PRIMARY,name | name | 33 | const | 1 | Using where |
| 1 | PRIMARY | <derived2> | ref | <auto_key3> | <auto_key3> | 5 | big.country.code | 773 | NULL |
| 1 | PRIMARY | hospital | eq_ref | PRIMARY | PRIMARY | 4 | big_city.code | 1 | Using where |
| 1 | PRIMARY | school | eq_ref | PRIMARY | PRIMARY | 4 | big_city.code | 1 | Using where |
| 2 | DERIVED | city | ALL | population | NULL | NULL | NULL | 192815 | Using where |
+----+-------------+------------+--------+---------------+-------------+---------+------------------+--------+-------------+
5 rows in set (0.00 sec)
mysql> SELECT count(*) FROM city, hospital, school, country
-> WHERE population > 100*1000
-> AND city.country = country.code AND country.name = 'Japan'
-> AND city.code = hospital.code AND hospital.num = 100
-> AND hospital.code = school.code AND school.num < 100;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.20 sec)
mysql> EXPLAIN SELECT count(*) FROM city, hospital, school, country
-> WHERE population > 100*1000
-> AND city.country = country.code AND country.name = 'Japan'
-> AND city.code = hospital.code AND hospital.num = 100
-> AND hospital.code = school.code AND school.num < 100;
+----+-------------+----------+--------+--------------------+---------+---------+---------------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+--------------------+---------+---------+---------------+--------+----------------------------------------------------+
| 1 | SIMPLE | country | ref | PRIMARY,name | name | 33 | const | 1 | Using where |
| 1 | SIMPLE | city | ALL | PRIMARY,population | NULL | NULL | NULL | 192815 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | hospital | eq_ref | PRIMARY | PRIMARY | 4 | big.city.code | 1 | Using where |
| 1 | SIMPLE | school | eq_ref | PRIMARY | PRIMARY | 4 | big.city.code | 1 | Using where |
+----+-------------+----------+--------+--------------------+---------+---------+---------------+--------+----------------------------------------------------+
4 rows in set (0.00 sec)
- MariaDB10.0
MariaDB [big]> SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital, school, country
-> WHERE big_city.country = country.code AND country.name = 'Japan' AND big_city.code = hospital.code
-> AND hospital.num = 100 AND hospital.code = school.code AND school.num < 100;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.21 sec)
MariaDB [big]> EXPLAIN SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital, school, country
-> WHERE big_city.country = country.code AND country.name = 'Japan'
-> AND big_city.code = hospital.code AND hospital.num = 100
-> AND hospital.code = school.code AND school.num < 100;
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------------------------------------------+
| 1 | SIMPLE | country | ref | PRIMARY,name | name | 33 | const | 1 | Using where |
| 1 | SIMPLE | city | ALL | PRIMARY,population | NULL | NULL | NULL | 202407 | Using where; Using join buffer (flat, BNL join) |
| 1 | SIMPLE | hospital | eq_ref | PRIMARY | PRIMARY | 4 | big.city.code | 1 | Using where |
| 1 | SIMPLE | school | eq_ref | PRIMARY | PRIMARY | 4 | big.city.code | 1 | Using where |
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------------------------------------------+
4 rows in set (0.00 sec)
MariaDB [big]> SELECT count(*) FROM city, hospital, school, country
-> WHERE population > 100*1000
-> AND city.country = country.code AND country.name = 'Japan'
-> AND city.code = hospital.code AND hospital.num = 100
-> AND hospital.code = school.code AND school.num < 100;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.20 sec)
MariaDB [big]> EXPLAIN SELECT count(*) FROM city, hospital, school, country
-> WHERE population > 100*1000
-> AND city.country = country.code AND country.name = 'Japan'
-> AND city.code = hospital.code AND hospital.num = 100
-> AND hospital.code = school.code AND school.num < 100;
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------------------------------------------+
| 1 | SIMPLE | country | ref | PRIMARY,name | name | 33 | const | 1 | Using where |
| 1 | SIMPLE | city | ALL | PRIMARY,population | NULL | NULL | NULL | 186544 | Using where; Using join buffer (flat, BNL join) |
| 1 | SIMPLE | hospital | eq_ref | PRIMARY | PRIMARY | 4 | big.city.code | 1 | Using where |
| 1 | SIMPLE | school | eq_ref | PRIMARY | PRIMARY | 4 | big.city.code | 1 | Using where |
+------+-------------+----------+--------+--------------------+---------+---------+---------------+--------+-------------------------------------------------+
4 rows in set (0.00 sec)
- PostgreSQL9.3
big=# SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital, school, country
WHERE big_city.country = country.code AND country.name = 'Japan'
AND big_city.code = hospital.code AND hospital.num = 100
AND hospital.code = school.code AND school.num < 100;
count
-------
1
(1 row)
Time: 89.733 ms
big=# EXPLAIN SELECT count(*) FROM (SELECT * FROM city WHERE population > 100*1000) AS big_city, hospital, school, country
big-# WHERE big_city.country = country.code AND country.name = 'Japan'
big-# AND big_city.code = hospital.code AND hospital.num = 100
big-# AND hospital.code = school.code AND school.num < 100;
QUERY PLAN
-----------------------------------------------------------------------------------------
-------
Aggregate (cost=4793.19..4793.20 rows=1 width=0)
-> Nested Loop (cost=5.10..4793.19 rows=2 width=0)
-> Nested Loop (cost=4.68..4791.75 rows=3 width=8)
-> Hash Join (cost=4.26..4268.71 rows=1090 width=4)
Hash Cond: (city.country = country.code)
-> Seq Scan on city (cost=0.00..3517.65 rows=196239 width=8)
Filter: (population > 100000)
-> Hash (cost=4.25..4.25 rows=1 width=4)
-> Seq Scan on country (cost=0.00..4.25 rows=1 width=4)
Filter: (name = 'Japan'::text)
-> Index Scan using hospital_pkey on hospital (cost=0.42..0.47 rows=1 wi
dth=4)
Index Cond: (code = city.code)
Filter: (num = 100)
-> Index Scan using school_pkey on school (cost=0.42..0.47 rows=1 width=4)
Index Cond: (code = city.code)
Filter: (num < 100)
(16 rows)
big=# SELECT count(*) FROM city, hospital, school, country
WHERE population > 100*1000
AND city.country = country.code AND country.name = 'Japan'
AND city.code = hospital.code AND hospital.num = 100
AND hospital.code = school.code AND school.num < 100;
count
-------
1
(1 row)
Time: 91.021 ms
big=# EXPLAIN SELECT count(*) FROM city, hospital, school, country
WHERE population > 100*1000
AND city.country = country.code AND country.name = 'Japan'
AND city.code = hospital.code AND hospital.num = 100
AND hospital.code = school.code AND school.num < 100;
QUERY PLAN
----------------------------------------------------------------------------------------------
--
Aggregate (cost=4793.19..4793.20 rows=1 width=0)
-> Nested Loop (cost=5.10..4793.19 rows=2 width=0)
-> Nested Loop (cost=4.68..4791.75 rows=3 width=8)
-> Hash Join (cost=4.26..4268.71 rows=1090 width=4)
Hash Cond: (city.country = country.code)
-> Seq Scan on city (cost=0.00..3517.65 rows=196239 width=8)
Filter: (population > 100000)
-> Hash (cost=4.25..4.25 rows=1 width=4)
-> Seq Scan on country (cost=0.00..4.25 rows=1 width=4)
Filter: (name = 'Japan'::text)
-> Index Scan using hospital_pkey on hospital (cost=0.42..0.47 rows=1 width=4
)
Index Cond: (code = city.code)
Filter: (num = 100)
-> Index Scan using school_pkey on school (cost=0.42..0.47 rows=1 width=4)
Index Cond: (code = city.code)
Filter: (num < 100)
(16 rows)
