Could PostGraphile use (more) CTEs/JOINs instead of subqueries?

Author: the-sun-will-rise-tomorrowCreated Jun 10, 2025Updated Aug 28, 2026

For a GraphQL query such as:

graphql

{
  objects {
    id
    states {
      someField
      # ... more fields here ...
    }
  }
}

PostGraphile generates the following SQL:

sql

select
  __objects__."id"::text as "0",
  array(
    select array[
      __object_states__."some_field"::text,
      -- ... more fields here ...
    ]::text[]
    from "postgraphile_schema"."object_states" as __object_states__
    where (
      __object_states__."object_id" = __objects__."id"
    )
    order by __object_states__."id" asc
  )::text as "1"
from "postgraphile_schema"."objects" as __objects__
order by __objects__."id" asc;

This is slow, because "postgraphile_schema"."object_states" is an expensive-to-compute view, and because it is in a subquery, it is recomputed for every row in __objects__.

The following equivalent query is much faster:

sql

with __object_states__ as (
  select *
  from "postgraphile_schema"."object_states"
)
select
  __objects__.id::text as "0",
  array_agg(
    array[
      __object_states__."some_field"::text,
      -- ... more fields here ...
    ]::text[]
    order by __object_states__.id asc
  )::text as "1"
from "postgraphile_schema"."objects" as __objects__
left join __object_states__ on __objects__.id = __object_states__.object_id
group by __objects__.id
order by __objects__.id asc;

Could PostGraphile generate SQL code closer to the second variant?

Source: graphile/crystal

View original on GitHub View discussion on GitHub