21030: Add index_materialized_permissions_target_is_not_user

See comment on 20231013000000_compute_permission_index.rb

Arvados-DCO-1.1-Signed-off-by: Peter Amstutz <peter.amstutz@curii.com>

diff --git a/services/api/db/migrate/20231013000000_compute_permission_index.rb b/services/api/db/migrate/20231013000000_compute_permission_index.rb
new file mode 100644 (file)
index 0000000..ecd85ef
--- /dev/null
+++ b/services/api/db/migrate/20231013000000_compute_permission_index.rb
@@ -0,0 +1,27 @@
+# Copyright (C) The Arvados Authors. All rights reserved.
+#
+# SPDX-License-Identifier: AGPL-3.0
+
+class ComputePermissionIndex < ActiveRecord::Migration[5.2]
+  def up
+    # The inner part of compute_permission_subgraph has a query clause like this:
+    #
+    #    where u.perm_origin_uuid = m.target_uuid AND m.traverse_owned
+    #         AND (m.user_uuid = m.target_uuid or m.target_uuid not like '_____-tpzed-_______________')
+    #
+    # This will end up doing a sequential scan on
+    # materialized_permissions, which can easily have millions of
+    # rows, unless we fully index the table for this query.  In one test,
+    # this brought the compute_permission_subgraph query from over 6
+    # seconds down to 250ms.
+    #
+    ActiveRecord::Base.connection.execute "drop index if exists index_materialized_permissions_target_is_not_user"
+    ActiveRecord::Base.connection.execute %{
+create index index_materialized_permissions_target_is_not_user on materialized_permissions (target_uuid, traverse_owned, (user_uuid = target_uuid or target_uuid not like '_____-tpzed-_______________'));
+}
+  end
+
+  def down
+    ActiveRecord::Base.connection.execute "drop index if exists index_materialized_permissions_target_is_not_user"
+  end
+end

diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql
index 6e8b128c9e5e3be02da7b5da622e00661269ee71..28259a08f29f191315f7e1dc81078e2188bfbf9f 100644 (file)
--- a/services/api/db/structure.sql
+++ b/services/api/db/structure.sql
@@ -342,30 +342,6 @@ SET default_tablespace = '';
 
 SET default_with_oids = false;
 
---
--- Name: groups; Type: TABLE; Schema: public; Owner: -
---
-
-CREATE TABLE public.groups (
-    id bigint NOT NULL,
-    uuid character varying(255),
-    owner_uuid character varying(255),
-    created_at timestamp without time zone NOT NULL,
-    modified_by_client_uuid character varying(255),
-    modified_by_user_uuid character varying(255),
-    modified_at timestamp without time zone,
-    name character varying(255) NOT NULL,
-    description character varying(524288),
-    updated_at timestamp without time zone NOT NULL,
-    group_class character varying(255),
-    trash_at timestamp without time zone,
-    is_trashed boolean DEFAULT false NOT NULL,
-    delete_at timestamp without time zone,
-    properties jsonb DEFAULT '{}'::jsonb,
-    frozen_by_uuid character varying
-);
-
-
 --
 -- Name: api_client_authorizations; Type: TABLE; Schema: public; Owner: -
 --
@@ -690,6 +666,30 @@ CREATE TABLE public.frozen_groups (
 );
 
 
+--
+-- Name: groups; Type: TABLE; Schema: public; Owner: -
+--
+
+CREATE TABLE public.groups (
+    id bigint NOT NULL,
+    uuid character varying(255),
+    owner_uuid character varying(255),
+    created_at timestamp without time zone NOT NULL,
+    modified_by_client_uuid character varying(255),
+    modified_by_user_uuid character varying(255),
+    modified_at timestamp without time zone,
+    name character varying(255) NOT NULL,
+    description character varying(524288),
+    updated_at timestamp without time zone NOT NULL,
+    group_class character varying(255),
+    trash_at timestamp without time zone,
+    is_trashed boolean DEFAULT false NOT NULL,
+    delete_at timestamp without time zone,
+    properties jsonb DEFAULT '{}'::jsonb,
+    frozen_by_uuid character varying
+);
+
+
 --
 -- Name: groups_id_seq; Type: SEQUENCE; Schema: public; Owner: -
 --
@@ -2576,6 +2576,13 @@ CREATE INDEX index_logs_on_summary ON public.logs USING btree (summary);
 CREATE UNIQUE INDEX index_logs_on_uuid ON public.logs USING btree (uuid);
 
 
+--
+-- Name: index_materialized_permissions_target_is_not_user; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX index_materialized_permissions_target_is_not_user ON public.materialized_permissions USING btree (target_uuid, traverse_owned, ((((user_uuid)::text = (target_uuid)::text) OR ((target_uuid)::text !~~ '_____-tpzed-_______________'::text))));
+
+
 --
 -- Name: index_nodes_on_created_at; Type: INDEX; Schema: public; Owner: -
 --
@@ -3293,6 +3300,7 @@ INSERT INTO "schema_migrations" (version) VALUES
 ('20230421142716'),
 ('20230503224107'),
 ('20230815160000'),
-('20230821000000');
+('20230821000000'),
+('20231013000000');