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?

I haven't tried this particular version but may be 3.1.0-M2 be helpful in avoiding subqueries. slick.typesafe.com/news/2015/08/13/slick-3.1.0-M2-released.html. main highlight of milestone release "New query compiler back-end. The main goal is to avoid subqueries wherever possible. The current version is feature-complete." — Vikas Pandya
If you are using MySQL, then its a known issue. github.com/slick/slick/issues/489. Slick-3.1.0 should generate better queries. — mohit
What happens when you remove join part and execute query like this: SELECT cp.cart_id, count(cp.product_id) FROM cart_product cp WHERE cp.cart_id = 3 GROUP BY cp.cart_id; Does it change execution plan? — Maciej Los

Rok Kralj Rok Kralj · Accepted Answer · 2015-08-27T15:44:15

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

Optimizing Slick generated SQL query

1 Answers