services/api/db/migrate/20230815160000_jsonb_exists_functions.rb

   1 # Copyright (C) The Arvados Authors. All rights reserved.
   2 #
   3 # SPDX-License-Identifier: AGPL-3.0
   4
   5 class JsonbExistsFunctions < ActiveRecord::Migration[5.2]
   6   def up
   7
   8     # Define functions for the "?" and "?&" operators.  We can't use
   9     # "?" and "?&" directly in ActiveRecord queries because "?" is
  10     # used for parameter substitution.
  11     #
  12     # We used to use jsonb_exists() and jsonb_exists_all() but
  13     # apparently Postgres associates indexes with operators but not
  14     # with functions, so while a query using an operator can use the
  15     # index, the equivalent clause using the function will always
  16     # perform a full row scan.
  17     #
  18     # See ticket https://dev.arvados.org/issues/20858 for examples.
  19     #
  20     # As a workaround, we can define IMMUTABLE functions, which are
  21     # directly inlined into the query, which then uses the index as
  22     # intended.
  23     #
  24     # Huge shout out to this stack overflow post that explained what
  25     # is going on and provides the workaround used here.
  26     #
  27     # https://dba.stackexchange.com/questions/90002/postgresql-operator-uses-index-but-underlying-function-does-not
  28
  29     ActiveRecord::Base.connection.execute %{
  30 CREATE OR REPLACE FUNCTION jsonb_exists_inline_op(jsonb, text)
  31 RETURNS bool
  32 LANGUAGE sql
  33 IMMUTABLE
  34 AS $$SELECT $1 ? $2$$
  35 }
  36
  37     ActiveRecord::Base.connection.execute %{
  38 CREATE OR REPLACE FUNCTION jsonb_exists_all_inline_op(jsonb, text[])
  39 RETURNS bool
  40 LANGUAGE sql
  41 IMMUTABLE
  42 AS 'SELECT $1 ?& $2'
  43 }
  44   end
  45
  46   def down
  47     ActiveRecord::Base.connection.execute "DROP FUNCTION jsonb_exists_inline_op"
  48     ActiveRecord::Base.connection.execute "DROP FUNCTION jsonb_exists_all_inline_op"
  49   end
  50 end