From d1d41d16c7ace2c097c0dbea8c0b72a3bc6a690c Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Thu, 12 Sep 2013 13:13:23 -0400 Subject: [PATCH] Optimize container filters in QueryParser When a container filter is used at the top level of a QueryParser query (that is, in a simple query with no OR-logic branches or explicit nesting groups) we can use an INNER join instead of LEFT + IS NOT NULL. On some production datasets this showed an increase in performance from 20+ seconds to 1ms for the core query. Signed-off-by: Mike Rylander Signed-off-by: Dan Scott --- .../Application/Storage/Driver/Pg/QueryParser.pm | 11 ++++++----- 1 file changed, 6 insertions(+), 5 deletions(-) diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm index 544dba9980..e4e237c035 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm @@ -1096,12 +1096,13 @@ sub flatten { } $with .= " )"; - $from .= "\n" . ${spc} x 3 . "LEFT JOIN container_${filter_alias} ON container_${filter_alias}.record = m.source"; + my $optimize_join = 1 if $self->top_plan and !$NOT; + $from .= "\n" . ${spc} x 3 . ( $optimize_join ? 'INNER' : 'LEFT') . " JOIN container_${filter_alias} ON container_${filter_alias}.record = m.source"; - my $spcdepth = $self->plan_level + 5; - - $where .= $joiner if $where ne ''; - $where .= "${NOT}(container_${filter_alias} IS NOT NULL)"; + if (!$optimize_join) { + $where .= $joiner if $where ne ''; + $where .= "(container_${filter_alias} IS " . ( $NOT ? 'NULL)' : 'NOT NULL)'); + } } } } elsif ($filter->name eq 'record_list') { -- 2.43.2