Substr() with Group by clause

Question

I've been working on solving the problem shown here, and a question arose

Here's the schema I created, my statement of the problem, and my test data:

Schema: Students

CREATE TABLE Students (group_id text, sql_quotient float);
INSERT INTO Students(group_id, sql_quotient)
VALUES 
    ( 'A', 25 ),
    ( 'B', 30 ),
    ( 'C', 40 ),
    ( 'A', 35 ),
    ( 'B', 20 );

Task: Display max average sql_quotient among all the groups.

• group_id is guaranteed to be a Single character of range from A-Z.
• Here for group A, avg is 30; for B, avg is 25; and for C, avg is 40; hence, 40 should be displayed.

I tried the following 2 queries; both give me the right answer.

Query 1

select max(round(b.avg_quotient,2)) as answer 
         from 
          (SELECT AVG(sql_quotient) as avg_quotient FROM Students GROUP BY group_id) as b;

Runtime = 0.002378 sec

Query 2

select max(round(b.avg_quotient,2)) as answer 
     from 
      (SELECT AVG(sql_quotient) as avg_quotient FROM Students GROUP BY substr(group_id,1,1) )as b;  

Runtime = 0.000459 sec

The difference - the first query groups the data by group_id; the second by `substr(group_id,1,1).

As the second query applies an additional function, I would expect it to take longer. However, as you can see above, runtime of query no.2 is remarkably less than query no. 1.

My question: why does query 2 have a lower runtime than query 1, even though query 2 has one extra function (substr()).

Notes:

• The schema is already defined. I don't know why the datatype of Id is text instead of char(1), and for the purposes of my question it's irrelevant.
• I'm looking for the reason for the difference of runtime between these 2 queries, not the another query for same problem.
• The Students table is created anew for each run, so this isn't a case of the data having to be read from disk on the first run, and being in memory on the second. To prove this, I ran the queries four more times, running Query 2, then Query 1, then 2 again, then 1 again. The run times were:
  • Q2, 1st: 0.000493
  • Q1, 1st: 0.002779
  • Q2, 2nd: 0.000499
  • Q1, 2nd: 0.002787

Answer 1

No substring needed. No need to calculate the max()

Something like this,

SELECT id, avg(price)
FROM Students
GROUP BY id
ORDER BY avg(price) DESC
LIMIT 1;

+------+------------+
| id   | avg(price) |
+------+------------+
| C    |    40.0000 |
+------+------------+

That's likely simpler than having to calculate the max.

SELECT id, avg(price)
FROM Students
GROUP BY id
HAVING avg(price) = (
    SELECT max(avg)
    FROM (
        SELECT id, avg(price) AS avg
        FROM Students
        GROUP BY id
    ) AS t
);

timings

+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                                           |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.00008716 | SELECT id, avg(price) FROM Students GROUP BY id HAVING avg(price) = ( SELECT max(avg) FROM ( SELECT id, avg(price) AS avg FROM Students GROUP BY id) AS t ) |
|        2 | 0.00008053 | SELECT id, avg(price) FROM Students GROUP BY id ORDER BY avg(price) DESC LIMIT 1                                                                      |
|        3 | 0.00011303 | SELECT id, avg(price) FROM Students GROUP BY id HAVING avg(price) = ( SELECT max(avg) FROM ( SELECT id, avg(price) AS avg FROM Students GROUP BY id) AS t ) |
|        4 | 0.00006121 | SELECT id, avg(price) FROM Students GROUP BY id ORDER BY avg(price) DESC LIMIT 1                                                                      |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------+

Answer 2

I don't think you understand cold and hot times. Run the same queries again.

I think the whole foundation for the question is wrong. It's not any faster. The first table lookup is slower, then it gets cached.

MariaDB [test]> show profiles;
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                                                 |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.00366737 | select max(round(b.avg_price,2)) as answer 
             from 
              (SELECT AVG(price) as avg_price FROM Students GROUP BY id )as b          |
|        2 | 0.00080117 | select max(round(b.avg_price,2)) as answer 
         from 
          (SELECT AVG(price) as avg_price FROM 
    Students GROUP BY substr(id,1,1) )as b |
|        3 | 0.00010088 | select max(round(b.avg_price,2)) as answer 
             from 
              (SELECT AVG(price) as avg_price FROM Students GROUP BY id )as b          |
|        4 | 0.00015381 | select max(round(b.avg_price,2)) as answer 
         from 
          (SELECT AVG(price) as avg_price FROM 
    Students GROUP BY substr(id,1,1) )as b |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+

You can't see heap hits vs hd hits because MySQL stinks. In a real database though,

explain (ANALYZE, VERBOSE, BUFFERS) SELECT id, avg(price)
FROM Students
GROUP BY id
ORDER BY avg(price) DESC
LIMIT 1;;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=31.50..31.50 rows=1 width=40) (actual time=0.091..0.091 rows=1 loops=1)
   Output: id, (avg(price))
   Buffers: shared hit=3 read=1
   ->  Sort  (cost=31.50..32.00 rows=200 width=40) (actual time=0.090..0.090 rows=1 loops=1)
         Output: id, (avg(price))
         Sort Key: (avg(students.price)) DESC
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=3 read=1
         ->  HashAggregate  (cost=28.00..30.50 rows=200 width=40) (actual time=0.045..0.046 rows=3 loops=1)
               Output: id, avg(price)
               Group Key: students.id
               Buffers: shared read=1
               ->  Seq Scan on public.students  (cost=0.00..22.00 rows=1200 width=40) (actual time=0.027..0.028 rows=5 loops=1)
                     Output: id, price
                     Buffers: shared read=1
 Planning time: 0.541 ms
 Execution time: 0.332 ms
(17 rows)

test=# explain (ANALYZE, VERBOSE, BUFFERS) SELECT id, avg(price)
FROM Students
GROUP BY id
ORDER BY avg(price) DESC
LIMIT 1;;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=31.50..31.50 rows=1 width=40) (actual time=0.059..0.059 rows=1 loops=1)
   Output: id, (avg(price))
   Buffers: shared hit=1
   ->  Sort  (cost=31.50..32.00 rows=200 width=40) (actual time=0.057..0.057 rows=1 loops=1)
         Output: id, (avg(price))
         Sort Key: (avg(students.price)) DESC
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=1
         ->  HashAggregate  (cost=28.00..30.50 rows=200 width=40) (actual time=0.037..0.039 rows=3 loops=1)
               Output: id, avg(price)
               Group Key: students.id
               Buffers: shared hit=1
               ->  Seq Scan on public.students  (cost=0.00..22.00 rows=1200 width=40) (actual time=0.013..0.015 rows=5 loops=1)
                     Output: id, price
                     Buffers: shared hit=1
 Planning time: 0.114 ms
 Execution time: 0.152 ms
(17 rows)

Observe, with PostgreSQL,

   Buffers: shared hit=3 read=1

vs

   Buffers: shared hit=1

The first execution has to read the table, and then it has three shared hits to ram. The second time, it's already in ram and it's one hit away for all of it.