From: Peter Amstutz <peter.amstutz@curii.com>
Date: Fri, 23 Oct 2020 00:44:15 +0000 (-0400)
Subject: 17040: Swap the order of where clauses in the readable_by query
X-Git-Tag: 2.2.0~238^2~5
X-Git-Url: https://git.arvados.org/arvados.git/commitdiff_plain/90b4ef014fd97787f26735083bad4c2ac1a174c5?ds=sidebyside

17040: Swap the order of where clauses in the readable_by query

This seems work around a query planner bug in Postgres 9.5.

A hotfix provided to a customer resulted in dramatically better
performance.

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

diff --git a/services/api/app/models/arvados_model.rb b/services/api/app/models/arvados_model.rb
index 3966b7c393..37f96c3ff0 100644
--- a/services/api/app/models/arvados_model.rb
+++ b/services/api/app/models/arvados_model.rb
@@ -353,8 +353,9 @@ class ArvadosModel < ApplicationRecord
       # other user owns.
       owner_check = ""
       if sql_table != "api_client_authorizations" and sql_table != "groups" then
-        owner_check = "OR #{sql_table}.owner_uuid IN (SELECT target_uuid FROM #{PERMISSION_VIEW} "+
-          "WHERE user_uuid IN (#{user_uuids_subquery}) AND perm_level >= 1 #{trashed_check} AND traverse_owned) "
+        owner_check = "#{sql_table}.owner_uuid IN (SELECT target_uuid FROM #{PERMISSION_VIEW} "+
+                      "WHERE user_uuid IN (#{user_uuids_subquery}) AND perm_level >= 1 #{trashed_check} AND traverse_owned) "
+        direct_check = " OR " + direct_check
       end
 
       links_cond = ""
@@ -366,7 +367,7 @@ class ArvadosModel < ApplicationRecord
                        "(#{sql_table}.head_uuid IN (#{user_uuids_subquery}) OR #{sql_table}.tail_uuid IN (#{user_uuids_subquery})))"
       end
 
-      sql_conds = "(#{direct_check} #{owner_check} #{links_cond}) #{exclude_trashed_records}"
+      sql_conds = "(#{owner_check} #{direct_check} #{links_cond}) #{exclude_trashed_records}"
 
     end