1

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
  • 2
    if id is really defined as text, I'm guessing the query engine finds substr(id,1,1) (a char) is easier to process than a text; a general comment about your queries ... both are using non-ANSI group by ... ANSI (in a nutshell) says all non-aggregate columns should be in both the select list and the group by clause ... so I'm guessing you're also suffering some performance degradation from the non-ANSI group by; take a closer look at Evan's queries ... ANSI-compliant group by AND his worst run time is still 1/4 of your best run time – markp Sep 9 '17 at 21:00
  • I can't recreate your results. They conflict with my results which I demonstrate here. Perhaps you could record a screen capture and show us what you're doing. Or, identify what you think I am doing differently. – Evan Carroll Sep 12 '17 at 16:32
  • @EvanCarroll Our results are conflicting because in your environment table's been read only once while in the website where I tried these queries, they recreate table for each and every query in order to find actual performance of query. To recreate scenario, I've created Students table each time and fire both queries and you can see the difference in their runtime here: 1) sqlfiddle.com/#!9/26e55d/2/0 (Qyery 2) (2) sqlfiddle.com/#!9/26e55d/3/0 (Query 1) – Harshil Sep 12 '17 at 16:54
  • So you're basing your benchmarks on sqlfiddle? that's "the website"? That's wildly inaccurate and not made for this. Moreover, you're not narrowing down your problem. You're just insisting one exists, and for me your sql-fiddles aren't demonstrating anything the one wo/ the substr is faster. Put it in the same query and use profiles -- as I did in my answer. Stop obfuscating the problem and insisting it exists: you're basing this whole conjecture on the cold-run times of two discrete queries in different sqlfiddle sessions that are highly variable, by a factor of 50x. – Evan Carroll Sep 12 '17 at 19:21
  • 1
    @EvanCarroll Ok. It'll take some time though. – Harshil Sep 12 '17 at 19:35
2

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                                                                      |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
0

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.

  • I found this problem in an contest on a website where to get actual runtime of query they are creating new table every time the query is executed by any user so that they can assign rank to users according to result and runtime of query. So i don't think that reason behind the lower runtime of query with substr() is caching of table. – Harshil Sep 10 '17 at 16:21
  • @Harshil I showed you explicit that this is the problem. In the profile query 2 & 4 use substr. As you can see, 4 is slower than 3. – Evan Carroll Sep 11 '17 at 18:30
  • 1
    FYI - See latest question edit - multiple runs of OP's queries, alternating, with faster one first show this is not a cold vs hot data issue. – RDFozz Sep 12 '17 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.