An index for the foreign key field is required for queries that filter on parent record values.
Example 1 (child->parent) without fix: Find all holdings where the instance title starts with "Honey ":
Using join without fix:
For both subquery and join the LIKE filter finds 28 matching instances and takes 9 ms, and finding the holdings requires a full table scan and takes 2761 ms.
Example 2 (parent->child) without fix: Find all instances where the instance title starts with "Honey " and there exists a least one holding.
Using subquery (this is not possible using join):
The LIKE filter finds 28 matching instances and takes 9 ms, and finding the holdings requires a full table scan and takes 5151 ms.
1. Replace any usage of the jsonb foreign key field by the database table field, for example replace (holdings_record.jsonb->>'instanceId')::UUID by holdings_record.instanceId. We already have a trigger that ensures that the value is always the same.
2. Always create an index for the database table foreign key field. Example: CREATE INDEX ON holdings_record (instanceId);
Applying the fix to the queries results in this:
Example 1 (child->parent) fixed:
Using join fixed:
Example 2 (parent->child) fixed:
Performance with fix:
Finding the holdings takes less than 1 ms resulting in a total execution time of 9 ms.
Note: If you don't want to do 1. you may create the index using the jsonb field: CREATE INDEX ON holdings_record (((holdings_record.jsonb->>'instanceId')::UUID)); However, this doesn't improve the execution time of the two Example 1 queries, it only improves the Example 2 query.
This is likely caused by the additional operators/functions for the index expression, the query optimizer may take that into account (cpu_operator_cost https://www.postgresql.org/docs/current/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS ) and a proper database table field has statistics for Postgres' query optimizer which an jsonb field doesn't have.