Uploaded image for project: 'RAML Module Builder'
  1. RAML Module Builder
  2. RMB-468

Foreign key field index

    XMLWordPrintable

Details

    • CP: sprint 72
    • 5
    • Core: Platform

    Description

      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 subquery:

      SELECT holdings_record.jsonb
      FROM holdings_record
      WHERE (holdings_record.jsonb->>'instanceId')::UUID IN (
          SELECT id
          FROM instance
          WHERE lower(f_unaccent(instance.jsonb->>'title')) LIKE 'honey %');
      

      Using join without fix:

      SELECT holdings_record.jsonb
      FROM holdings_record
      JOIN instance ON (holdings_record.jsonb->>'instanceId')::UUID = instance.id
      WHERE lower(f_unaccent(instance.jsonb->>'title')) LIKE 'honey %';
      

      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):

      SELECT instance.jsonb
      FROM instance
      WHERE instance.id IN (
          SELECT (holdings_record.jsonb->>'instanceId')::UUID
          FROM holdings_record
          WHERE true)
      AND lower(f_unaccent(instance.jsonb->>'title')) LIKE 'honey %';
      

      The LIKE filter finds 28 matching instances and takes 9 ms, and finding the holdings requires a full table scan and takes 5151 ms.

      Solution:
      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:

      CREATE INDEX ON holdings_record (instanceId);
      SELECT holdings_record.jsonb
      FROM holdings_record
      WHERE holdings_record.instanceId IN (
          SELECT id
          FROM instance
          WHERE lower(f_unaccent(instance.jsonb->>'title')) LIKE 'honey %');
      

      Using join fixed:

      CREATE INDEX ON holdings_record (instanceId);
      SELECT holdings_record.jsonb
      FROM holdings_record
      JOIN instance ON (holdings_record.instanceId = instance.id)
      WHERE lower(f_unaccent(instance.jsonb->>'title')) LIKE 'honey %';
      

      Example 2 (parent->child) fixed:

      CREATE INDEX ON holdings_record (instanceId);
      SELECT instance.jsonb
      FROM instance
      WHERE instance.id IN (
          SELECT instanceId
          FROM holdings_record
          WHERE true)
      AND lower(f_unaccent(instance.jsonb->>'title')) LIKE 'honey %';
      

      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.

      TestRail: Results

        Attachments

          Issue Links

            Activity

              People

                julianladisch Julian Ladisch
                julianladisch Julian Ladisch
                Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases