[Question] Simple joins from request

[Eddydpyl]

I have the following HTTP call in my application:

/rest/v1/simulations
?select=*,event_id!inner(*),matrix_id!inner(*)
&matrix_id.centre_id=eq.XXX
&offset=0
&limit=10

The query I wanted to make was the following:

SELECT s.*, row_to_json(e.*) AS event_id, row_to_json(m.*) AS matrix_id FROM simulations s INNER JOIN events e ON s.event_id = e.id INNER JOIN matrices m ON s.matrix_id = m.id WHERE m.centre_id = 'XXX' OFFSET 0 LIMIT 10;

But instead I get this:

SELECT "public"."simulations".*, row_to_json("simulations_event_id".*) AS "event_id", row_to_json("simulations_matrix_id".*) AS "matrix_id" FROM "public"."simulations"  INNER JOIN LATERAL( SELECT "public"."events".* FROM "public"."events" WHERE "public"."events"."centre_id" = 'XXX' AND "public"."simulations"."event_id" = "public"."events"."id"  LIMIT 10 OFFSET 0 ) AS "simulations_event_id" ON TRUE INNER JOIN LATERAL( SELECT "public"."matrices".* FROM "public"."matrices"  WHERE "public"."simulations"."matrix_id" = "public"."matrices"."id"  LIMIT 10 OFFSET 0 ) AS "simulations_matrix_id" ON TRUE LIMIT 10 OFFSET 0;

The results are identical, but the optimizer has a much easier time with the shorter query, which is a big deal for me. Is there a way to make a request that triggers a query more like the shorter one? Thanks.

[steve-chavez]

The results are identical, but the optimizer has a much easier time with the shorter query, which is a big deal for me

Can you share the EXPLAIN ANALYZE output for both queries?

Is there a way to make a request that triggers a query more like the shorter one?

A view or function would do.

[Eddydpyl]

Ok so no way to do it directly through the API call. The issue is with my RLS policies, but as modifying those will take quite a bit of work, I thought I could tackle the problem from this end, at least temporarily. Thanks!