From d8d832489f054bfba51854354ae00d7a26edc237 Mon Sep 17 00:00:00 2001 From: Thomas Berezansky Date: Wed, 20 Feb 2013 12:26:01 -0500 Subject: [PATCH] QueryParser: Improve container searches Add a with/from set for containers for record limiting instead of using the EXISTS methodology for better index usage. Signed-off-by: Thomas Berezansky Signed-off-by: Ben Shum --- .../Storage/Driver/Pg/QueryParser.pm | 55 +++++++++---------- 1 file changed, 27 insertions(+), 28 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 e11aa0d4a9..57558e5750 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 @@ -1229,36 +1229,35 @@ sub flatten { if ($class) { my ($u,$e) = $apputils->checksesperm($token) if ($token); $perm_join = ' OR c.owner = ' . $u->id if ($u && !$e); - $where .= $joiner if $where ne ''; - my $spcdepth = $self->plan_level + 5; - if($class eq 'copy') { - $spcdepth += 1; - $where .= "(\n" . ${spc} x $spcdepth; - } - $where .= "${NOT}EXISTS(\n" - . ${spc} x ($spcdepth + 1) . "SELECT 1 FROM container.${class}_bucket_item ci\n" - . ${spc} x ($spcdepth + 4) . "JOIN container.${class}_bucket c ON (c.id = ci.bucket) $rec_join\n" - . ${spc} x ($spcdepth + 1) . "WHERE c.btype = " . $self->QueryParser->quote_value($ctype) . "\n" - . ${spc} x ($spcdepth + 4) . "AND c.id = " . $self->QueryParser->quote_value($cid) . "\n" - . ${spc} x ($spcdepth + 4) . "AND (c.pub IS TRUE$perm_join)\n" - . ${spc} x ($spcdepth + 4) . "AND $rec_field = m.source\n" - . ${spc} x ($spcdepth + 1) . "LIMIT 1\n" - . ${spc} x $spcdepth . ")"; + + my $filter_alias = "$filter"; + $filter_alias =~ s/^.*\(0(x[0-9a-fA-F]+)\)$/$1/go; + $filter_alias =~ s/\|/_/go; + + $with .= ",\n " if $with; + $with .= "container_${filter_alias} AS (\n"; + $with .= " SELECT $rec_field AS record FROM container.${class}_bucket_item ci\n" + . " JOIN container.${class}_bucket c ON (c.id = ci.bucket) $rec_join\n" + . " WHERE c.btype = " . $self->QueryParser->quote_value($ctype) . "\n" + . " AND c.id = " . $self->QueryParser->quote_value($cid) . "\n" + . " AND (c.pub IS TRUE$perm_join)\n"; if ($class eq 'copy') { - my $subjoiner = $filter->negate ? 'AND' : 'OR'; - $where .= "\n" - . ${spc} x ($spcdepth) . $subjoiner . "\n" - . ${spc} x ($spcdepth) . "${NOT}EXISTS(\n" - . ${spc} x ($spcdepth + 1) . "SELECT 1 FROM container.copy_bucket_item ci\n" - . ${spc} x ($spcdepth + 4) . "JOIN container.copy_bucket c ON (c.id = ci.bucket)\n" - . ${spc} x ($spcdepth + 4) . "JOIN biblio.peer_bib_copy_map pr ON ci.target_copy = pr.target_copy\n" - . ${spc} x ($spcdepth + 1) . "WHERE c.btype = " . $self->QueryParser->quote_value($cid) . "\n" - . ${spc} x ($spcdepth + 4) . "AND (c.pub IS TRUE$perm_join)\n" - . ${spc} x ($spcdepth + 4) . "AND pr.peer_record = m.source\n" - . ${spc} x ($spcdepth + 1) . "LIMIT 1\n" - . ${spc} x $spcdepth . ")\n" - . ${spc} x ($spcdepth - 1) . ")"; + $with .= " UNION\n" + . " SELECT pr.peer_record AS record FROM container.copy_bucket_item ci\n" + . " JOIN container.copy_bucket c ON (c.id = ci.bucket)\n" + . " JOIN biblio.peer_bib_copy_map pr ON ci.target_copy = pr.target_copy\n" + . " WHERE c.btype = " . $self->QueryParser->quote_value($ctype) . "\n" + . " AND c.id = " . $self->QueryParser->quote_value($cid) . "\n" + . " AND (c.pub IS TRUE$perm_join)\n"; } + $with .= " )"; + + $from .= "\n" . ${spc} x 3 . "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)"; } } } elsif ($filter->name eq 'record_list') { -- 2.43.2