Optimizing Slick generated SQL query

Question

I have a very simple query which in SQL can be represented as follows:

SELECT
  c.id,
  count(cp.product_id)
FROM cart c LEFT OUTER JOIN cart_product cp ON c.id = cp.cart_id
WHERE c.id = 3
GROUP BY c.id;

I was very surprised when using Slick DSL to represent above query, the query generated from following DSL:

Cart.joinLeft(CartProduct)
  .on { case (c, cp) => c.id === cp.cartId }
  .filter { case (c, cp) => c.id === 3 }
  .groupBy { case (c, cp) => c.id }
  .map { case (c, pr) => (c, pr.length)
}

Looked as follows:

SELECT
  x2.x3,
  count(1)
FROM (SELECT
        x4.x5  AS x3,
        x4.x6  AS x7,
        x8.x9  AS x10,
        x8.x11 AS x12,
        x8.x13 AS x14,
        x8.x15 AS x16
      FROM (SELECT
              x17."id"      AS x5,
              x17."user_id" AS x6
            FROM "cart" x17) x4 LEFT OUTER JOIN (SELECT
                                                   1                AS x9,
                                                   x18."id"         AS x11,
                                                   x18."cart_id"    AS x13,
                                                   x18."product_id" AS x15
                                                 FROM "cart_product" x18) x8 ON x4.x5 = x8.x13) x2
WHERE x2.x3 = 3
GROUP BY x2.x3;

What am I doing wrong? Is it normal to see such nested queries? What is the point of using Slick DSL if the complexity of query grows so quickly? I could probably write native SQL however I really liked Slick DSL. What are the techniques of optimizing Slick queries?

Answer 1

Since you have written you are using PostgreSQL, then I would not worry, since PostgreSQL is known for a really good query optimizer. Such a simple transformation is effortless, it takes virtually no additional time. The only thing is you wait, the problem is eventually going to be fixed upstream (somewhere around Slick version 3.1) and you don't have to do anything.

p.s.: Why are you not simply using this query? It should return exactly the same result, if you have a foreign constraint on the tables:

SELECT id, COUNT(*) FROM cart_product WHERE id=3