I have typical Users and Orders tables:

CREATE TABLE Users 
(
    id INT, 
    name TEXT 
);

CREATE TABLE Orders 
(
    id INT, 
    name TEXT, 
    userId REFERENCES Users(id) 
);

I want a query to get a count of all orders belonging to a specific user’s name (“bob”). e.g.,

I have 2 possible queries:

SELECT * 
FROM Users
JOIN 
    (SELECT Bob.id, COUNT(*)
     FROM Orders
     JOIN  
         (SELECT id FROM Users WHERE name = 'bob') Bob ON Orders.userId = Bob.id
     GROUP BY Bob.id) BobOrders ON BobOrders.id = Users.id;

Using EXPLAIN ANALYZE returns: Planning Time: 0.301 ms / Execution Time: 0.170 ms

Another shorter but unoptimized query is:

SELECT * 
FROM Users
JOIN 
    (SELECT userId, COUNT(*) 
     FROM Orders 
     GROUP BY userId) MyData ON MyData.userId = Users.id 
                             AND Users.name = 'bob';

Using EXPLAIN ANALYZE returns: Planning Time: 0.172 ms / Execution Time: 0.106 ms

Why does the query analyzer say the 2nd query is faster when it needs to GROUP BY the whole orders list before filtering by Users.id?