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
– Vikas Pandya, Commented Aug 15, 2015 at 13:37
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
– mohit, Commented Aug 15, 2015 at 18:33
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
– Maciej Los, Commented Aug 24, 2015 at 10:34

Rok Kralj · Accepted Answer · 2015-08-27 18:32:41Z

4

+50

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

edited Aug 27, 2015 at 18:32

answered Aug 27, 2015 at 15:44

Rok Kralj

49k10 gold badges75 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mon Calamari Over a year ago

Let's hope 3.1 will fix query nesting.

Collectives™ on Stack Overflow

Optimizing Slick generated SQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related