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

[the-sun-will-rise-tomorrow]

For a GraphQL query such as:

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

PostGraphile generates the following 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:

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?

[benjie]

No, for a number of reasons:

1. limits - you'd need to order and limit in the CTE and then re-order in the aggregate again
2. duplication - in the left join version you'd be repeating the data from __objects__ for every row returned from __object_states__
3. multiplication - if you did this for 2+ tables, you'd get a very large number of rows

Instead, consider using makeWrapPlansPlugin to forbid inlining of your expensive to calculate view. (We should probably add a smart tag for that too.)

export default makeWrapPlansPlugin({
  Object: {
    states(plan) {
      const $pgSelect = plan();
      if (!($pgSelect instanceof PgSelectStep)) {
        throw new Error(`Broken assumption... connection? ${$pgSelect}`);
      }
      $pgSelect.setInliningForbidden();
      return $pgSelect;
    }
  }
})

[the-sun-will-rise-tomorrow]

Thank you, I will look into that. Though, on these two points:

• duplication - in the left join version you'd be repeating the data from __objects__ for every row returned from __object_states__

• multiplication - if you did this for 2+ tables, you'd get a very large number of rows

The second query uses an aggregate function and GROUP BY, so I don't think this would be the case?

[benjie]

Without the group by the duplication issue hits, with the group by you get more issues (not allowed to select columns that you're not grouping on, for example, which would make computed columns challenging) and I think it would not work well for 2+ joined to-many relationship tables.

[the-sun-will-rise-tomorrow]

Instead, consider using makeWrapPlansPlugin to forbid inlining of your expensive to calculate view.

I tried this, but unfortunately this had the same effect.

PostGraphile generates the following SQL for object_states:

select __object_states_result__.*
from (
  select
    ids.ordinality - 1 as idx,
    (
      ids.value->>0
    )::"int4" as "id0"
  from
    json_array_elements(
      '[[1], [2], [3], [4], ... all object IDs here...]'::json
    ) with ordinality as ids
) as __object_states_identifiers__,
lateral (
  select
    __object_states__."some_field"::text as "0",
    __object_states_identifiers__.idx as "1"
  from "postgraphile_schema"."object_states" as __object_states__
  where (
    __object_states__."object_id" = __object_states_identifiers__."id0"
  )
  order by __object_states__."id" asc
) as __object_states_result__;

It's slow, but if I replace the lateral select with a regular join, it's fast again:

with __object_states__ as (
  select * from "postgraphile_schema"."object_states"
)
select
  __object_states__."some_field"::text as "0",
  __object_states_identifiers__.idx as "1"
from (
  select
    ids.ordinality - 1 as idx,
    (
      ids.value->>0
    )::"int4" as "id0"
  from
    json_array_elements(
      '[[1], [2], [3], [4], ... all object IDs here...]'::json
    ) with ordinality as ids
) as __object_states_identifiers__
left join __object_states__
  on __object_states__."object_id" = __object_states_identifiers__."id0"
order by __object_states__."id" asc

[the-sun-will-rise-tomorrow]

with the group by you get more issues (not allowed to select columns that you're not grouping on, for example, which would make computed columns challenging) and I think it would not work well for 2+ joined to-many relationship tables.

Obviously I don't have the same perspective as you, but these issues seem solvable to me by doing one CTE per relation, and then JOIN-ing them together with the main query (similar to what we discussed in #2551), which can then freely select any subset of regular columns.

[benjie]

If you're able to run the SQL above yourself, what would happen if you were to replace

  from "postgraphile_schema"."object_states" as __object_states__

with

  from (
    select *
    from "postgraphile_schema"."object_states"
  ) as __object_states__

I'm wondering if all you need here is a cacheable subquery rather than a common table expression.

[the-sun-will-rise-tomorrow]

If you're able to run the SQL above yourself, what would happen if you were to replace

No observable change in performance.

[benjie]

drop schema if exists a cascade;

create schema a;

create table a.users (
  id serial primary key,
  username text not null
);

create table a.emails (
  id serial primary key,
  user_id int not null references a.users,
  email_address text not null,
  last_emailed timestamptz
);

create table a.phone_numbers (
  id serial primary key,
  user_id int not null references a.users,
  phone_number text not null,
  last_called timestamptz
);

insert into a.users (username) values
  ('Alice'),   -- id 1
  ('Bob'),     -- id 2
  ('Charlie'), -- id 3
  ('Dana'),    -- id 4
  ('Eve');     -- id 5

insert into a.emails (user_id, email_address, last_emailed) values
  -- Alice
  (1, 'alice@example.com',       '2024-01-01T10:00:00Z'),
  (1, 'alice.work@example.com', '2024-01-02T11:00:00Z'),
  (1, 'a.smith@example.org',    '2024-01-03T12:00:00Z'),
  -- Bob
  (2, 'bob@example.com',         '2024-01-04T13:00:00Z'),
  (2, 'bob.ross@example.com',    '2024-01-05T14:00:00Z'),
  (2, 'bobby808@example.org',    '2024-01-06T15:00:00Z'),
  (2, 'robert@example.net',      '2024-01-07T16:00:00Z'),
  -- Charlie
  (3, 'charlie@example.com',     '2024-01-08T17:00:00Z'),
  (3, 'c.charlie@example.org',   '2024-01-09T18:00:00Z'),
  -- Dana
  (4, 'dana@example.com',        '2024-01-10T19:00:00Z'),
  (4, 'dana.dev@example.org',    '2024-01-11T20:00:00Z'),
  (4, 'danaw@example.net',       '2024-01-12T21:00:00Z'),
  (4, 'd.w@example.com',         '2024-01-13T22:00:00Z'),
  (4, 'dana411@example.com',     '2024-01-14T23:00:00Z'),
  -- Eve
  (5, 'eve@example.com',         '2024-01-15T00:00:00Z'),
  (5, 'eve.e@example.org',       '2024-01-16T01:00:00Z');

insert into a.phone_numbers (user_id, phone_number, last_called) values
  -- Alice (411)
  (1, '+14115550001', '2024-02-01T10:00:00Z'),
  (1, '+14115550002', '2024-02-02T11:00:00Z'),
  -- Bob (808)
  (2, '+18085550001', '2024-02-03T12:00:00Z'),
  -- Charlie (no phone)
  -- Dana (411)
  (4, '+14115550003', '2024-02-04T13:00:00Z'),
  (4, '+14115550004', '2024-02-05T14:00:00Z'),
  -- Eve (999)
  (5, '+19995550001', '2024-02-06T15:00:00Z');

with __emails__ as (
  select *
  from a.emails
),
__phone_numbers__ as (
  select *
  from a.phone_numbers
)
select
  __users__.id::text as "0",
  array_agg(
    array[
      __emails__.id::text,
      __emails__.email_address::text,
      __emails__.last_emailed::text
    ]::text[]
    order by __emails__.id asc
  )::text as "1",
  array_agg(
    array[
      __phone_numbers__.id::text,
      __phone_numbers__.phone_number::text,
      __phone_numbers__.last_called::text
    ]::text[]
    order by __phone_numbers__.id asc
  )::text as "2"
from a.users as __users__
left join __emails__ on __users__.id = __emails__.user_id
left join __phone_numbers__ on __users__.id = __phone_numbers__.user_id
group by __users__.id
order by __users__.id asc;

Here's a simple schema that shows the issue with multiple relations like this. If you run this and look at the output from columns 1 and 2 you'll see they include duplicated/multiplied content due to the two left joins. I'm not sure what you had in mind for this?

[the-sun-will-rise-tomorrow]

Happy to explain. I would rewrite the final query as:

with __emails__ as (
  select *
  from a.emails
),
__phone_numbers__ as (
  select *
  from a.phone_numbers
),
__user_emails__ as (
  select
    user_id as id,
    array_agg(
      array[
        __emails__.id::text,
        __emails__.email_address::text,
        __emails__.last_emailed::text
      ]::text[]
      order by __emails__.id asc
    )::text as value
  from __emails__
  group by user_id
),
__user_phone_numbers__ as (
  select
    user_id as id,
    array_agg(
      array[
        __phone_numbers__.id::text,
        __phone_numbers__.phone_number::text,
        __phone_numbers__.last_called::text
      ]::text[]
      order by __phone_numbers__.id asc
    )::text as value
  from __phone_numbers__
  group by user_id
)
select
  __users__.id::text as "0",
  __user_emails__.value as "1",
  __user_phone_numbers__.value as "2"
from a.users as __users__
left join __user_emails__ on __users__.id = __user_emails__.id
left join __user_phone_numbers__ on __users__.id = __user_phone_numbers__.id
order by __users__.id asc;

I think this should work as expected and have good performance.

[benjie]

Ah interesting; this is actually aligned with something I tried a couple years ago, based on what I do for pg-introspection. It is something I plan to revisit because the current approach still duplicates data and I want more of a DataLoader-style deduplication; it's why "identifiers" are part of pgSelect: so I can tie data back together from sibling queries. IIRC the reason it didn't work for us was because it didn't integrate well with the batching that we had at the time particularly due to issues over identifiers: for example you might find a username by username = 'benjie' (citext) but when we read the data back into JS it might have Benjie (read from the CITEXT column) which wasn't === to the input data, so it didn't get paired back up. This is of course solvable by comparing in citext inside the database rather than simple string equality in the JS layer, but I think I timed out on it (and the performance differences I saw in my experiments didn't justify more time being spent there at the time).

[benjie]

(But I was testing perf against tables, not views, so the story might be different there. I don't remember the last time I used a non-materialized view for something.)

[the-sun-will-rise-tomorrow]

Thanks for looking into it!

(But I was testing perf against tables, not views, so the story might be different there. I don't remember the last time I used a non-materialized view for something.)

Going a little off topic, but I may need to reconsider using views here - I went with this approach following our discussion in #2389. Wrapping the view's query in a materialized CTE helped a little, but clearly that can only go so far. I think the alternative would be a materialized view or explicitly-materialized cache table, however, it needs to reflect parameters specified in HTTP headers, so we're back to the original question of that issue. The flow could then be something like this:

1. Request comes in, with a specific configuration specified in the HTTP headers
2. This is translated to GUC parameters via context / pgSettings
3. Views are re-materialized according to these settings - how to do this?
4. Queries and mutations continue as normal, using the materialized views.

Maybe a plan hook as you've suggested above? I could then rematerialize the views on demand per table instead of doing it eagerly for all tables.

[benjie]

You can't safely re-materialize the views like this because they'd conflict with other parallel transactions.