Allow conditions on computed properties (optimize computed property query)

[EyMaddis]

Hi, currently the queries for computed property functions are evaluated after the data was fully loaded, leading to N+2 and weird error behaviour (#375) :

postgraphql:postgres select set_config($1, $2, true), set_config($3, $4, true), set_config($5, $6, true), set_config($7, $8, true), set_config($9, $10, true), set_config($11, $12, true), set_config($13, $14, true) +1ms
postgraphql:postgres select to_json(__local_0__) as value from graphql."user" as __local_0__ where "id" is not null and true and true and true order by "id" using < limit all +3ms
postgraphql:postgres select to_json(graphql."user_custom_column"(($1, $2, $3, $4, $5, $6, $7, $8, $9)::graphql."user")) as value +1ms
postgraphql:postgres select to_json(graphql."user_custom_column"(($1, $2, $3, $4, $5, $6, $7, $8, $9)::graphql."user")) as value +1ms
postgraphql:postgres select to_json(graphql."user_custom_column"(($1, $2, $3, $4, $5, $6, $7, $8, $9)::graphql."user")) as value +1ms
postgraphql:postgres select to_json(graphql."user_custom_column"(($1, $2, $3, $4, $5, $6, $7, $8, $9)::graphql."user")) as value +0ms
postgraphql:postgres select to_json(graphql."user_custom_column"(($1, $2, $3, $4, $5, $6, $7, $8, $9)::graphql."user")) as value +1ms
postgraphql:postgres select to_json(graphql."user_custom_column"(($1, $2, $3, $4, $5, $6, $7, $8, $9)::graphql."user")) as value +0ms
postgraphql:postgres select to_json(graphql."user_custom_column"(($1, $2, $3, $4, $5, $6, $7, $8, $9)::graphql."user")) as value +1ms
postgraphql:postgres select to_json(graphql."user_custom_column"(($1, $2, $3, $4, $5, null, $6, $7, $8)::graphql."user")) as value +1ms
postgraphql:postgres select to_json(graphql."user_custom_column"(($1, $2, $3, $4, $5, null, $6, $7, $8)::graphql."user")) as value +1ms
postgraphql:postgres select to_json(graphql."user_custom_column"(($1, $2, $3, $4, $5, null, $6, $7, $8)::graphql."user")) as value +1ms

This can be achieved with a single request, based on a StackOverflow answer:

SELECT *, u.user_custom_column 
  FROM graphql.user as u

The same approach can be used in WHERE clauses, so the condition types in PostGraphQL can also be amended. Currently conditions on allMyType(condition: {...}) can only include non-computed properties. Example for using a computed property in a condition:

SELECT *, u.user_custom_column 
  FROM graphql.user as u
  WHERE u.user_custom_column > 30

This might be more tricky when it comes to allowing parameters for the computed properties, like the summary property in the forum example.

PS: I also ran it against #342, which puts this into one CTE (🎉) , but it still split up into two SELECT statements.

[danscan]

Hmm– I am planning to look more at this tomorrow, but it seems like using expression indexes might make some use cases like this possible?

[EyMaddis]

@danscan expression indexes are nice, but only possible when it is not relying on any other data than the row. Basically for this to work, there could also be a trigger that just sets the value on Update/Insert. But the power is when it happens at query time, as this could even access other tables.

[benjie]

This could be implemented as a plugin in v4.

[benjie]

Relates also to https://github.com/graphile/postgraphile/issues/386

I think we can use indexes to detect the computed columns that are suitable for use in both conditions and order by.

[benjie]

4.3.1 added support for filtering and ordering by single-argument computed columns:

https://github.com/graphile/postgraphile/releases/tag/v4.3.1