You need to sign in or sign up before continuing.
Optimize field dependency SQL
Background
- Baserow keeps track of all per field dependencies (this lookup field depends on that target field via this link row field etc)
- When doing inserts/updates/deletes we need to cascade changes over this dependency graph
- The SQL involved in doing this is complicated and slow
Optimization target 1
It is possible given a list of field_ids, to lookup all other dependant fields and the edges on that graph using a recursive CTE. We had this code originally in get_dependant_fields_with_type
which would fetch the entire graph in one go. We should investigate if this is still possible.
Optimization target2:
We are seeing this SQL statement be very slow:
SELECT "database_fielddependency"."id", "database_fielddependency"."dependant_id", "database_fielddependency"."dependency_id", "database_fielddependency"."via_id", "database_fielddependency"."broken_reference_field_name", T5."id", T5."created_on", T5."updated_on", T5."trashed", T5."table_id", T5."order", T5."name", T5."primary", T5."content_type_id", "database_field"."id", "database_field"."created_on", "database_field"."updated_on", "database_field"."trashed", "database_field"."table_id", "database_field"."order", "database_field"."name", "database_field"."primary", "database_field"."content_type_id", T6."id", T6."created_on", T6."updated_on", T6."trashed", T6."table_id", T6."order", T6."name", T6."primary", T6."content_type_id", "database_linkrowfield"."field_ptr_id", "database_linkrowfield"."link_row_table_id", "database_linkrowfield"."link_row_related_field_id", "database_linkrowfield"."link_row_relation_id" FROM "database_fielddependency" LEFT OUTER JOIN "database_field" ON ("database_fielddependency"."dependency_id" = "database_field"."id") LEFT OUTER JOIN "database_linkrowfield" ON ("database_fielddependency"."via_id" = "database_linkrowfield"."field_ptr_id") INNER JOIN "database_field" T5 ON ("database_fielddependency"."dependant_id" = T5."id") LEFT OUTER JOIN "database_field" T6 ON ("database_linkrowfield"."field_ptr_id" = T6."id") WHERE ("database_fielddependency"."dependency_id" IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) OR (("database_fielddependency"."via_id" IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) OR "database_linkrowfield"."link_row_related_field_id" IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)) AND NOT ("database_fielddependency"."dependant_id" IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)))) ORDER BY "database_fielddependency"."id" ASC
I believe this is coming from baserow.contrib.database.fields.dependencies.handler.FieldDependencyHandler.get_dependant_fields_with_type
. We might be able to replace this with some changes the field graph system and a recursive CTE.