Why correlated scalar is 10x times slower in MySQL comparing to PG

Question

Let's create a table with 3000 rows

create table tt(id int, txt text);

insert into tt
with recursive r(id) as
(select 1 union all select id + 1 from r where id < 3e3)
select id, concat('name', id)
from r;

The same query in both databases results in very different performance:

select sum(id), 
       sum((select count(*) 
            from tt t1 
            where t1.id = t2.id)) cnt
from tt t2

MYSQL

mysql> explain analyze
    -> select sum(id), sum((select count(*) from tt t1 where t1.id = t2.id)) cnt
    -> from tt t2\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: sum(t2.id), sum((select #2))  (cost=602 rows=1) (actual time=7542..7542 rows=1 loops=1)
    -> Table scan on t2  (cost=302 rows=3000) (actual time=0.025..2.75 rows=3000 loops=1)
-> Select #2 (subquery in projection; dependent)
    -> Aggregate: count(0)  (cost=62.5 rows=1) (actual time=2.51..2.51 rows=1 loops=3000)
        -> Filter: (t1.id = t2.id)  (cost=32.5 rows=300) (actual time=1.25..2.51 rows=1 loops=3000)
            -> Table scan on t1  (cost=32.5 rows=3000) (actual time=0.00256..2.31 rows=3000 loops=3000)

1 row in set, 1 warning (7.54 sec)

PG

postgres=# explain analyze
postgres-# select sum(id), sum((select count(*) from tt t1 where t1.id = t2.id)) cnt
postgres-# from tt t2;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=163599.50..163599.51 rows=1 width=40) (actual time=684.339..684.340 rows=1 loops=1)
   ->  Seq Scan on tt t2  (cost=0.00..47.00 rows=3000 width=4) (actual time=0.013..0.223 rows=3000 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=54.50..54.51 rows=1 width=8) (actual time=0.227..0.227 rows=1 loops=3000)
           ->  Seq Scan on tt t1  (cost=0.00..54.50 rows=1 width=0) (actual time=0.113..0.223 rows=1 loops=3000)
                 Filter: (id = t2.id)
                 Rows Removed by Filter: 2999
 Planning Time: 0.663 ms
 Execution Time: 684.512 ms
(9 rows)

As you can see the difference is ~7.0 seconds vs ~0.7 seconds.

So it both cases it does not unnest subquery and executes it 3000 times.

But in MySQL one execution takes 2+ ms while in PG it takes 0.2 ms.

Question asked to understand this difference and not to make query faster.

Obviously we can create an index or rewrite to explicit join.

select sum(t1.id), sum(cnt) cnt
from tt t2
join (select id, sum(1) cnt from tt group by id) t1 on t1.id = t2.id;

PS. Both RDBMS have default settings.

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.43    |
+-----------+
1 row in set (0.00 sec)

postgres=# select version();
                          version
------------------------------------------------------------
 PostgreSQL 15.1, compiled by Visual C++ build 1914, 64-bit
(1 row)

UPDATE

As requested in the comments - adding comparison on Ubuntu.

Still 10x difference.

MYSQL

mysql> explain analyze
    -> select sum(id), sum((select count(*) from tt t1 where t1.id = t2.id)) cnt
    -> from tt t2\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: sum(t2.id), sum((select #2))  (cost=600 rows=1) (actual time=5416..5416 rows=1 loops=1)
    -> Table scan on t2  (cost=300 rows=3000) (actual time=0.0244..5.38 rows=3000 loops=1)
-> Select #2 (subquery in projection; dependent)
    -> Aggregate: count(0)  (cost=60.3 rows=1) (actual time=1.79..1.79 rows=1 loops=3000)
        -> Filter: (t1.id = t2.id)  (cost=30.3 rows=300) (actual time=0.906..1.79 rows=1 loops=3000)
            -> Table scan on t1  (cost=30.3 rows=3000) (actual time=0.00668..1.56 rows=3000 loops=3000)

1 row in set, 1 warning (5.41 sec)

PG

postgres=# explain analyze
select sum(id), sum((select count(*) from tt t1 where t1.id = t2.id)) cnt
from tt t2;
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=163599.50..163599.51 rows=1 width=40) (actual time=526.720..526.721 rows=1 loops=1)
   ->  Seq Scan on tt t2  (cost=0.00..47.00 rows=3000 width=4) (actual time=0.012..0.301 rows=3000 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=54.50..54.51 rows=1 width=8) (actual time=0.175..0.175 rows=1 loops=3000)
           ->  Seq Scan on tt t1  (cost=0.00..54.50 rows=1 width=0) (actual time=0.087..0.173 rows=1 loops=3000)
                 Filter: (id = t2.id)
                 Rows Removed by Filter: 2999
 Planning Time: 0.078 ms
 Execution Time: 526.766 ms
(9 rows)

@RichardHuxton 10x factor stays constant. On 6000 rows it becomes 3 sec vs 30 secs and on 9000 rows it becomes 7 sec vs 70 secs. I believe it all comes down to 2ms vs 0.2ms difference I pointed to in the original post. What kind of profiling are you referring to? — Slimboy Fat
– Slimboy Fat, Commented Oct 2 at 13:06
@BillKarwin explain ANALYZE executes query in both RDBMS. explain ANALYZE shows actual exectuion time. You can run query without explain analyze to check. — Slimboy Fat
– Slimboy Fat, Commented Oct 2 at 13:12
@BillKarwin , SlimBoy Fat used explain analyze which in fact (from the doc) "The ANALYZE option causes the statement to be actually executed, not only planned". So in this case this is not the culprit. — Guillaume Outters
– Guillaume Outters, Commented Oct 2 at 13:13
These are two (very) different database systems, both accepting (some form of) SQL as input. The Why? will be in the implementation details of each engine. Put some other databases, Oracle/SQL Server/DB2, in your test, and you get different results as well. I think this question is way too big for SO — Frank Heikens
– Frank Heikens, Commented Oct 2 at 15:23
The "not suitable for SO" flag comes with a warning: Do not use this close reason for questions that are on-topic for Stack Overflow, even if they would also be on-topic on another Stack Exchange site. - I'd argue this thread is fine for both SO and DBA. It requires a deep dive into the two RDBMS' implementations which makes it hard, not broad, but I don't think there's a rule or a guideline against asking difficult questions. I disagree the scope is too wide: the question is about behaviour of a very specific mechanism in a well defined scenario. Plus, I see a nice and portable MRE. — Zegarek
– Zegarek, Commented Oct 2 at 16:44

Slimboy Fat · Accepted Answer · 2025-10-03 11:51:35Z

1

Well, answering my own question.

TL;DR: InnoDB is super slow for full table scans.

To demonstrate let's just create bigger table and run some trivial queries.

create table ttt(id int, txt text);

insert into ttt
with recursive r(id) as
(select 1 union all select id + 1 from r where id < 6e6)
select id, concat('name', id)
from r;

PG

postgres=# select count(*) from ttt;
  count
---------
 6000000
(1 row)


Time: 454.881 ms
postgres=# select sum(id) from ttt;
      sum
----------------
 18000003000000
(1 row)


Time: 544.896 ms
postgres=# select sum(id), max(txt) from ttt;
      sum       |    max
----------------+------------
 18000003000000 | name999999
(1 row)


Time: 2541.881 ms (00:02.542)

MYSQL

mysql> select count(*) from ttt;
+----------+
| count(*) |
+----------+
|  6000000 |
+----------+
1 row in set (0.38 sec)

mysql> select sum(id) from ttt;
+----------------+
| sum(id)        |
+----------------+
| 18000003000000 |
+----------------+
1 row in set (6.04 sec)

mysql> select sum(id), max(txt) from ttt;
+----------------+------------+
| sum(id)        | max(txt)   |
+----------------+------------+
| 18000003000000 | name999999 |
+----------------+------------+
1 row in set (8.13 sec)

Let's compare size on disk

PG

postgres=# select
postgres-#   c.relname table_name,
postgres-#   pg_relation_filepath(c.oid) file,
postgres-#   pg_size_pretty(pg_total_relation_size(c.oid)) total_size
postgres-# from pg_class c
postgres-# where c.relname = 'ttt';
 table_name |     file     | total_size
------------+--------------+------------
 ttt        | base/5/98593 | 253 MB
(1 row)

MYSQL

mysql> select
    ->   t.table_name,
    ->   f.file_name file,
    ->   round((t.data_length + t.index_length)/1024/1024, 2) total_mb
    -> from information_schema.tables as t
    -> join information_schema.files as f
    ->   on f.tablespace_name = concat(t.table_schema, '/', t.table_name)
    -> where t.table_schema = 'mydb'
    ->   and t.table_name   = 'ttt'
    ->   and t.engine = 'InnoDB'
    ->   and f.file_type = 'TABLESPACE';
+------------+----------------+----------+
| TABLE_NAME | file           | total_mb |
+------------+----------------+----------+
| ttt        | ./mydb/ttt.ibd |   255.80 |
+------------+----------------+----------+

So this is almost the same but queries take 10x times longer (except count(*)).

It would be understandable if full scan was 10% slower or 20% slower… or even 100% slower.

But 1000+% slower that is a bit too much! ((6.04-0.54)/(0.54)*1000 = 1018)

PS.

postgres=# show max_parallel_workers_per_gather;
 max_parallel_workers_per_gather
---------------------------------
 0
(1 row)

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

edited Oct 3 at 11:51

answered Oct 3 at 11:16

Slimboy Fat

6631 gold badge4 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Zegarek Oct 3 at 13:51

I don't think "it's slower" is a good answer to "why is it slower". This post somewhat simplifies the problem or takes a step in the right direction by peeling away a redundant layer, focusing on the scan that seems to be the leading factor in the overall query cost. However, it doesn't answer the question you posed, only reiterates it. As an edit to expand the question (or rather clean it up and simplify), it could be more helpful. Also, posting a (non-)answer hides this thread from people's feeds, so you're losing possibly valuable traffic.

Slimboy Fat Oct 3 at 19:57

Absolutely agree with you. Most likely, in order to find the root cause, I need to profile C code. However, I had a hope that some MySQL experts can explain architectural specifics. I suspected that slowness might be due to MVCC implementation in MySQL. However it is somewhat similar to Oracle (with UNDO to reconstruct specific versions and LRU to get data from buffer) and query execution time in Oracle is even better than the one in PostgreSQL. Also I suspected it might be something related to prefetch.

Slimboy Fat Oct 3 at 19:57

Another possible answer to “why” is because historically MySQL was targeted to OLTP workloads while PG evolved in both OLTP and OLAP areas. Practically speaking, MySQL can compete with PG only for strict OLTP workloads. Anyways, I raised more specific question on DBA site - dba.stackexchange.com/questions/347894/…

Slimboy Fat Oct 3 at 19:58

Finally, I wanted to thank you for answers and comments in my threads. I really appreciate the value you add. Thanks, man.

Frank Heikens Oct 3 at 19:40

Did you check your configuration? MySQL might be more sensitive to a suboptimal configuration. See also dba.stackexchange.com/questions/27328/…

Slimboy Fat Oct 3 at 20:00

Let's put PG out of the picture. If you can make it run several times faster in MySQL - please add your solution to dba.stackexchange.com/questions/347894/…

Frank Heikens Oct 3 at 21:29

Sorry, can't help you with this. I try to stay away from MySQL...

Rick James · Accepted Answer · 2025-10-04 06:04:32Z

0

It seems that the table in question does not have an explicit PRIMARY KEY; is this correct?

When MySQL has no PK, nor a suitable UNIQUE index that could serve as a PK, it generates a hidden, 6-byte, PK. Not having id as the PK could be leading to inefficiencies in your test case.

MySQL will do table scans for where t1.id = t2.id, slowing down the query dramatically.

(I don't know what happens in PG.)

Suggest you add PRIMARY KEY(id) to both tests.

This may be the main clue: "rows=3000 loops=3000".

answered Oct 4 at 6:04

Rick James

144k15 gold badges144 silver badges254 bronze badges

2 Comments

Slimboy Fat Oct 4 at 9:02

Perhaps I did not manage to make the right accent. Specific query was provided as the reproducible example which clearly show the difference (by scanning table thousand times). The key question is how to speed up full table scans - dba.stackexchange.com/questions/347894/…

Rick James Oct 5 at 4:42

"not to make query faster" != "speed up". PG seems to avoid the scans that MySQL is doing.

Collectives™ on Stack Overflow

Why correlated scalar is 10x times slower in MySQL comparing to PG

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related