8 replies

Aggregate first() generates seemingly inefficient subquery

The following code

calculate :cover_image_path,
              :string,
              expr(
                cover_image.path ||
                  first(photos, field: :path, query: [sort: [inserted_at: :asc]])
              )

calculate :cover_image_path,
              :string,
              expr(
                cover_image.path ||
                  first(photos, field: :path, query: [sort: [inserted_at: :asc]])
              )

generates an query for the first() using an arr_aggr, selecting all photos and selecting the first value in the array. Is there a way to optimize this to not use a arr_agg and just a order by X limit 1 in the subquery?

Example SQL query generated:

SELECT a0."id", a0."name", a0."description", a0."cover_image_id", a0."created_by_id", a0."end_date", a0."inserted_at", a0."start_date", a0."updated_at", coalesce(p1."path"::text, s2."first_photo_path"::text)::text FROM "albums" AS a0 LEFT OUTER JOIN "public"."photos" AS p1 ON a0."cover_image_id" = p1."id" LEFT OUTER JOIN LATERAL (SELECT sp0."album_id" AS "album_id", (array_agg(sp0."path"::text ORDER BY  sp0."inserted_at" ASC ) FILTER (WHERE sp0."path"::text IS NOT NULL))[1]::text AS "first_photo_path" FROM "public"."photos" AS sp0 WHERE (a0."id" = sp0."album_id") GROUP BY sp0."album_id") AS s2 ON TRUE WHERE (a0."id"::uuid = $1::uuid) ["01988ff7-e02b-7f68-a1bb-4c3e2e6b803d"]

SELECT a0."id", a0."name", a0."description", a0."cover_image_id", a0."created_by_id", a0."end_date", a0."inserted_at", a0."start_date", a0."updated_at", coalesce(p1."path"::text, s2."first_photo_path"::text)::text FROM "albums" AS a0 LEFT OUTER JOIN "public"."photos" AS p1 ON a0."cover_image_id" = p1."id" LEFT OUTER JOIN LATERAL (SELECT sp0."album_id" AS "album_id", (array_agg(sp0."path"::text ORDER BY  sp0."inserted_at" ASC ) FILTER (WHERE sp0."path"::text IS NOT NULL))[1]::text AS "first_photo_path" FROM "public"."photos" AS sp0 WHERE (a0."id" = sp0."album_id") GROUP BY sp0."album_id") AS s2 ON TRUE WHERE (a0."id"::uuid = $1::uuid) ["01988ff7-e02b-7f68-a1bb-4c3e2e6b803d"]

Ash Framework•8mo ago•

8 replies

Phoop

Aggregate first() generates seemingly inefficient subquery

The following code

calculate :cover_image_path,
              :string,
              expr(
                cover_image.path ||
                  first(photos, field: :path, query: [sort: [inserted_at: :asc]])
              )

calculate :cover_image_path,
              :string,
              expr(
                cover_image.path ||
                  first(photos, field: :path, query: [sort: [inserted_at: :asc]])
              )

SELECT a0."id", a0."name", a0."description", a0."cover_image_id", a0."created_by_id", a0."end_date", a0."inserted_at", a0."start_date", a0."updated_at", coalesce(p1."path"::text, s2."first_photo_path"::text)::text FROM "albums" AS a0 LEFT OUTER JOIN "public"."photos" AS p1 ON a0."cover_image_id" = p1."id" LEFT OUTER JOIN LATERAL (SELECT sp0."album_id" AS "album_id", (array_agg(sp0."path"::text ORDER BY  sp0."inserted_at" ASC ) FILTER (WHERE sp0."path"::text IS NOT NULL))[1]::text AS "first_photo_path" FROM "public"."photos" AS sp0 WHERE (a0."id" = sp0."album_id") GROUP BY sp0."album_id") AS s2 ON TRUE WHERE (a0."id"::uuid = $1::uuid) ["01988ff7-e02b-7f68-a1bb-4c3e2e6b803d"]

SELECT a0."id", a0."name", a0."description", a0."cover_image_id", a0."created_by_id", a0."end_date", a0."inserted_at", a0."start_date", a0."updated_at", coalesce(p1."path"::text, s2."first_photo_path"::text)::text FROM "albums" AS a0 LEFT OUTER JOIN "public"."photos" AS p1 ON a0."cover_image_id" = p1."id" LEFT OUTER JOIN LATERAL (SELECT sp0."album_id" AS "album_id", (array_agg(sp0."path"::text ORDER BY  sp0."inserted_at" ASC ) FILTER (WHERE sp0."path"::text IS NOT NULL))[1]::text AS "first_photo_path" FROM "public"."photos" AS sp0 WHERE (a0."id" = sp0."album_id") GROUP BY sp0."album_id") AS s2 ON TRUE WHERE (a0."id"::uuid = $1::uuid) ["01988ff7-e02b-7f68-a1bb-4c3e2e6b803d"]

Aggregate first() generates seemingly inefficient subquery

Similar Threads

Aggregate first() generates seemingly inefficient subquery

Similar Threads

Similar Threads

Similar Threads