From 43290aed39953adecbfd8835d6ccbb912db7164a Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Thu, 10 Sep 2020 13:43:03 -0400 Subject: [PATCH] LP#1846354: Move the deleted filter out of the query for speed Also tweak the aump view so that PG can use better plans Signed-off-by: Mike Rylander Signed-off-by: Jason Etheridge --- Open-ILS/src/perlmods/lib/OpenILS/Application/Actor.pm | 15 +++++++++------ Open-ILS/src/sql/Pg/005.schema.actors.sql | 1 + .../Pg/upgrade/XXXX.schema.note_and_message_consolidation | 1 + 3 files changed, 11 insertions(+), 6 deletions(-) diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Actor.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Actor.pm index 3b9b36229f..7c6a6e1271 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Actor.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Actor.pm @@ -3305,18 +3305,21 @@ sub new_flesh_user { } if($fetch_notes) { - # grab undeleted notes (now actor.usr_message_penalty) that have not hit their stop_date - $user->notes( - $e->search_actor_usr_message_penalty([ + # grab notes (now actor.usr_message_penalty) that have not hit their stop_date + # NOTE: This is a view that already filters out deleted messages that are not + # attached to a penalty, but the query is slow if we include deleted=f, so we + # post-filter that. This counts both user messages and standing penalties, but + # linked ones are only counted once. + $user->notes([ + grep { !$_->deleted or $_->deleted eq 'f' } @{ $e->search_actor_usr_message_penalty([ { usr => $id, - deleted => 'f', '-or' => [ {stop_date => undef}, {stop_date => {'>' => 'now'}} ], }, {} - ]) - ); + ]) } + ]); } # retrieve the most recent usr_activity entry diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index e699b3529e..b7e673c3d4 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -863,6 +863,7 @@ FULL OUTER JOIN actor.usr_message aum ON ( ausp.usr_message = aum.id + AND ausp.usr = aum.usr ) WHERE NOT (ausp.id IS NULL AND aum.deleted); diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.note_and_message_consolidation b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.note_and_message_consolidation index 28c5c8937a..d86f1a41da 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.note_and_message_consolidation +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.note_and_message_consolidation @@ -121,6 +121,7 @@ FULL OUTER JOIN actor.usr_message aum ON ( ausp.usr_message = aum.id + AND ausp.usr = aum.usr ) WHERE NOT (ausp.id IS NULL AND aum.deleted); -- 2.11.0