A way of creating computed columns with better performance

[the-sun-will-rise-tomorrow]

Summary

I implemented a number of computed columns as described on https://postgraphile.org/postgraphile/next/computed-columns - functions which accept an individual row, and return a value.

This works, however, as documented, it's slow. I think the problem is not so much because it's a function, but because if the function needs to fetch additional data from other tables using a sub-query, then PostgreSQL cannot optimize this sub-query into something more efficient such as a hoisted JOIN.

I tried a different approach as well. Instead of using functions, I declared a view which used CTEs to return all data and all computed columns as a single view. Unfortunately, this seems to cause PostgreSQL to eagerly compute all such computed columns, whether they are required from the Postgraphile's GraphQL selection set or not, so while it did help significantly in some situations, it introduced significant overhead in others.

I am wondering if it would be possible to have a more efficient general purpose way of defining computed columns, where the building block is not a function, but a view which returns two columns: id and value. This would then be joined by PostGraphile with the main table as needed.

CREATE TABLE people (id integer, name text, birth_date date);

CREATE VIEW people_age AS
SELECT
  id,
  DATE_PART('year', AGE(birth_date, NOW())) AS value
FROM 
  people;
COMMENT ON VIEW people_age IS $$
@computed_column_single  # returns exactly one value per data row ID; could also be:
# @computed_column_maybe_single  # returns zero or one value per data row, visible in GraphQL as a nullable value
# @computed_column_many  # returns any number of values per data row, visible in GraphQL as an array of values
@joined_on id
$$;

What do you think? Does this already exist somewhere?

[the-sun-will-rise-tomorrow]

Unfortunately, this seems to cause PostgreSQL to eagerly compute all such computed columns, whether they are required from the Postgraphile's GraphQL selection set or not

By the way, here is why this happened for me, exactly:

• When converting computed columns from functions to view columns, we can only do this for those functions which can be represented as columns, i.e. functions which return a single value or record. We can't do this for functions returning SETOF.
• As such, I left the functions returning SETOF as they were - functions.
• As described on https://postgraphile.org/postgraphile/next/computed-columns , computed column functions need to accept, as the first argument, the entire type that they are extending.
• When previously this was just a table, the type that these functions are extending is now the view, which now also includes the other computed columns, which were converted from functions to view columns.
• When passing the view row to the function, PostgreSQL computes all view columns in order to be able to pass it to the function parameter.
• Thus, querying any field which is backed by a SETOF-function has the overhead of also computing all non-SETOF fields as part of the query, regardless of whether they're actually used.

[benjie]

This is an interesting idea; I might hack out a quick implementation when I get a chance. Personally I side-step this issue via makeExtendSchemaPlugin but I understand the desire to be an SQL purist. I have wondered previously if we can expose this in another way via e.g. templated SQL files, but I never got around to implementing anything there.

[benjie]

Here's a rudimentary plugin to try this:

declare global {
  namespace GraphileBuild {
    interface Build {
      computedDigest: Map<
        PgCodec,
        {
          codecColumns: string[];
          viewColumns: string[];
          view: PgResource;
          sourceColumn: string;
          singular: boolean;
          notNull: boolean;
        }[]
      >;
    }
  }
}

const PgComputedColumnViewsPlugin: GraphileConfig.Plugin = {
  name: "PgComputedColumnViewsPlugin",
  gather: {},
  schema: {
    entityBehavior: {
      pgCodec: {
        inferred: {
          after: ["inferred"],
          provides: ["postInferred"],
          callback(behavior, entity) {
            if (entity.extensions?.tags?.computed) {
              return [behavior, "-*"];
            }
            return behavior;
          },
        },
      },
    },
    hooks: {
      build(build) {
        build.computedDigest = new Map();
        for (const pgResource of Object.values(
          build.input.pgRegistry.pgResources,
        )) {
          const tag = pgResource.extensions?.tags?.computed;
          if (!tag) {
            continue;
          }
          const parts = String(tag).split(" ");
          let codec: PgCodec | undefined;
          let codecColumns: string[] | undefined;
          let viewColumns: string[] | undefined;
          let singular = false;
          let notNull = false;

          for (const part of parts) {
            if (part.startsWith("on:")) {
              const match = part.match(/^on:([^(]+)\(([^)]+)\)=\(([^)]+)\)$/);
              if (!match) {
                throw new Error(
                  `Invalid '@computed' smart tag: ${JSON.stringify(tag)}`,
                );
              }
              const [_, tableName, localColumnsString, remoteColumnsString] =
                match;
              codec = Object.values(build.input.pgRegistry.pgCodecs).find(
                (c) =>
                  // TODO: match schemaName too
                  c.extensions?.pg?.name === tableName,
              );
              if (!codec || !codec.attributes) {
                throw new Error(`Failed to find codec for '${tableName}'`);
              }
              codecColumns = localColumnsString.split(",");
              viewColumns = remoteColumnsString.split(",");
            } else if (part === "singular") {
              singular = true;
            } else if (part === "notNull") {
              notNull = true;
            }
          }

          if (!codec || !codecColumns || !viewColumns) {
            throw new Error(
              `Invalid '@computed' smart tag; no 'on:' specifier: ${JSON.stringify(tag)}`,
            );
          }

          const sourceColumn = Object.keys(pgResource.codec.attributes).find(
            (n) => !viewColumns.includes(n),
          );
          if (!sourceColumn) {
            throw new Error(`Couldn't find column to source value from`);
          }
          if (!build.computedDigest!.get(codec)) {
            build.computedDigest!.set(codec, []);
          }
          build.computedDigest!.get(codec)!.push({
            codecColumns,
            viewColumns,
            view: pgResource,
            sourceColumn,
            singular,
            notNull,
          });
        }
        return build;
      },
      GraphQLObjectType_fields(fields, build, context) {
        const {
          grafast: { each },
        } = build;
        const {
          scope: { isPgClassType, pgCodec },
        } = context;
        if (!isPgClassType) return fields;
        if (!pgCodec || !pgCodec.attributes) return fields;
        const cols = build.computedDigest.get(pgCodec);
        if (!cols) return fields;
        for (const col of cols) {
          const {
            notNull,
            singular,
            sourceColumn,
            view,
            viewColumns,
            codecColumns,
          } = col;
          const attr = view.codec.attributes?.[sourceColumn];
          if (!attr) continue;
          const type = build.getGraphQLTypeByPgCodec(
            attr.codec,
            "output",
          ) as GraphQLOutputType;
          if (!type) continue;
          fields[
            build.inflection.camelCase(
              build.inflection.coerceToGraphQLName(col.sourceColumn),
            )
          ] = {
            type: build.nullableIf(notNull, type),
            plan: EXPORTABLE(
              (codecColumns, constant, each, singular, sourceColumn, view, viewColumns) => function plan($pgSelectSingle: PgSelectSingleStep) {
                  const $rows = view.find(
                    Object.fromEntries(
                      viewColumns.map((vc, i) => [
                        vc,
                        $pgSelectSingle.get(codecColumns[i]),
                      ]),
                    ),
                  );
                  if (singular) {
                    $rows.setFirst(constant(1));
                    const $row = $rows.single();
                    return ($row as PgSelectSingleStep).get(sourceColumn);
                  } else {
                    return each($rows, ($row) =>
                      ($row as PgSelectSingleStep).get(sourceColumn),
                    );
                  }
                },
              [codecColumns, constant, each, singular, sourceColumn, view, viewColumns],
            ),
          };
        }
        return fields;
      },
    },
  },
};

DB:

drop view if exists people_age;
drop table if exists people;

CREATE TABLE people (
  id serial primary key,
  name text,
  birth_date date
);

CREATE VIEW people_age AS
SELECT
  people.id,
  DATE_PART('year', AGE(birth_date, NOW())) AS value
FROM 
  people;
COMMENT ON VIEW people_age IS $$
@computed on:people(id)=(id) singular notNull
$$;

insert into people (name, birth_date) values
   ('Alice', '2000-01-01T00:00:00Z')
  ,('Bob', '1970-01-01T00:00:00Z')
  ,('Carl', '1985-07-07T07:07:07Z')
  ;

Query:

{
  allPeople {
    nodes {
      id
      name
      birthDate
      value
    }
  }
}

SQL:

select
  __people__."id"::text as "0",
  __people__."name" as "1",
  to_char(__people__."birth_date", 'YYYY-MM-DD'::text) as "2",
  array(
    select array[
      __people_age__."value"::text,
      (not (__people_age__ is null))::text
    ]::text[]
    from "public"."people_age" as __people_age__
    where (
      __people_age__."id" = __people__."id"
    )
    limit 1
  )::text as "3"
from "public"."people" as __people__
order by __people__."id" asc;

This could be made more efficient by adding a primary key to the view, then a left-join can be used.

[benjie]

V2:

declare global {
  namespace GraphileBuild {
    interface Build {
      computedDigest: Map<
        PgCodec,
        {
          codecColumns: string[];
          viewColumns: string[];
          view: PgResource;
          sourceColumn: string;
          singular: boolean;
          notNull: boolean;
        }[]
      >;
    }
  }
}

const PgComputedColumnViewsPlugin: GraphileConfig.Plugin = {
  name: "PgComputedColumnViewsPlugin",
  gather: {},
  schema: {
    entityBehavior: {
      pgCodec: {
        inferred: {
          after: ["inferred"],
          provides: ["postInferred"],
          callback(behavior, entity) {
            if (entity.extensions?.tags?.computed) {
              return [behavior, "-*"];
            }
            return behavior;
          },
        },
      },
    },
    hooks: {
      build(build) {
        build.computedDigest = new Map();
        for (const pgResource of Object.values(
          build.input.pgRegistry.pgResources,
        )) {
          const tag = pgResource.extensions?.tags?.computed;
          if (!tag) {
            continue;
          }
          const parts = String(tag).split(" ");
          let codec: PgCodec | undefined;
          let codecColumns: string[] | undefined;
          let viewColumns: string[] | undefined;
          let singular = false;
          let notNull = false;

          for (const part of parts) {
            if (part.startsWith("on:")) {
              const match = part.match(/^on:([^(]+)\(([^)]+)\)=\(([^)]+)\)$/);
              if (!match) {
                throw new Error(
                  `Invalid '@computed' smart tag: ${JSON.stringify(tag)}`,
                );
              }
              const [_, tableName, localColumnsString, remoteColumnsString] =
                match;
              codec = Object.values(build.input.pgRegistry.pgCodecs).find(
                (c) =>
                  // TODO: match schemaName too
                  c.extensions?.pg?.name === tableName,
              );
              if (!codec || !codec.attributes) {
                throw new Error(`Failed to find codec for '${tableName}'`);
              }
              codecColumns = localColumnsString.split(",");
              viewColumns = remoteColumnsString.split(",");
            } else if (part === "singular") {
              singular = true;
            } else if (part === "notNull") {
              notNull = true;
            }
          }

          if (!codec || !codecColumns || !viewColumns) {
            throw new Error(
              `Invalid '@computed' smart tag; no 'on:' specifier: ${JSON.stringify(tag)}`,
            );
          }

          const sourceColumn = Object.keys(pgResource.codec.attributes).find(
            (n) => !viewColumns.includes(n),
          );
          if (!sourceColumn) {
            throw new Error(`Couldn't find column to source value from`);
          }
          if (!build.computedDigest!.get(codec)) {
            build.computedDigest!.set(codec, []);
          }
          build.computedDigest!.get(codec)!.push({
            codecColumns,
            viewColumns,
            view: pgResource,
            sourceColumn,
            singular,
            notNull,
          });
        }
        return build;
      },
      GraphQLObjectType_fields(fields, build, context) {
        const {
          grafast: { each, arraysMatch },
        } = build;
        const {
          scope: { isPgClassType, pgCodec },
        } = context;
        if (!isPgClassType) return fields;
        if (!pgCodec || !pgCodec.attributes) return fields;
        const cols = build.computedDigest.get(pgCodec);
        if (!cols) return fields;
        for (const col of cols) {
          const {
            notNull,
            singular,
            sourceColumn,
            view,
            viewColumns,
            codecColumns,
          } = col;
          const attr = view.codec.attributes?.[sourceColumn];
          if (!attr) continue;
          const type = build.getGraphQLTypeByPgCodec(
            attr.codec,
            "output",
          ) as GraphQLOutputType;
          if (!type) continue;
          const uniq = view.uniques.some((u) =>
            arraysMatch(u.attributes, viewColumns),
          );
          fields[
            build.inflection.camelCase(
              build.inflection.coerceToGraphQLName(col.sourceColumn),
            )
          ] = {
            type: build.nullableIf(notNull, type),
            plan:
              singular && uniq
                ? EXPORTABLE(
                    (codecColumns, sourceColumn, view, viewColumns) =>
                      function plan($pgSelectSingle: PgSelectSingleStep) {
                        const $row = view.get(
                          Object.fromEntries(
                            viewColumns.map((vc, i) => [
                              vc,
                              $pgSelectSingle.get(codecColumns[i]),
                            ]),
                          ),
                        );
                        return ($row as PgSelectSingleStep).get(sourceColumn);
                      },
                    [codecColumns, sourceColumn, view, viewColumns],
                  )
                : EXPORTABLE(
                    (
                      codecColumns,
                      constant,
                      each,
                      singular,
                      sourceColumn,
                      view,
                      viewColumns,
                    ) =>
                      function plan($pgSelectSingle: PgSelectSingleStep) {
                        const $rows = view.find(
                          Object.fromEntries(
                            viewColumns.map((vc, i) => [
                              vc,
                              $pgSelectSingle.get(codecColumns[i]),
                            ]),
                          ),
                        );
                        if (singular) {
                          $rows.setFirst(constant(1));
                          const $row = $rows.single();
                          return ($row as PgSelectSingleStep).get(sourceColumn);
                        } else {
                          return each($rows, ($row) =>
                            ($row as PgSelectSingleStep).get(sourceColumn),
                          );
                        }
                      },
                    [
                      codecColumns,
                      constant,
                      each,
                      singular,
                      sourceColumn,
                      view,
                      viewColumns,
                    ],
                  ),
          };
        }
        return fields;
      },
    },
  },
};

schema:

drop view if exists people_age;
drop table if exists people;

CREATE TABLE people (
  id serial primary key,
  name text,
  birth_date date
);

CREATE VIEW people_age AS
SELECT
  people.id,
  DATE_PART('year', AGE(birth_date, NOW())) AS value
FROM 
  people;
COMMENT ON VIEW people_age IS $$
@computed on:people(id)=(id) singular notNull
@primaryKey id
$$;

insert into people (name, birth_date) values
   ('Alice', '2000-01-01T00:00:00Z')
  ,('Bob', '1970-01-01T00:00:00Z')
  ,('Carl', '1985-07-07T07:07:07Z')
  ;

sql:

select
  __people__."id"::text as "0",
  __people__."name" as "1",
  to_char(__people__."birth_date", 'YYYY-MM-DD'::text) as "2",
  __people_age__."value"::text as "3",
  __people_age__."id"::text as "4"
from "public"."people" as __people__
left outer join "public"."people_age" as __people_age__
on (
/* WHERE becoming ON */ (
  __people_age__."id" = __people__."id"
))
order by __people__."id" asc;

[the-sun-will-rise-tomorrow]

Thank you very much! I've actually moved all my computed columns' implementations out of SQL following your mention of makeExtendSchemaPlugin, but I will surely try this again on the next opportunity 👍