From 09e4b9d134fe9de6f22c7942929b848ddc7e6347 Mon Sep 17 00:00:00 2001 From: gmc Date: Mon, 24 Jan 2011 18:42:13 +0000 Subject: [PATCH] improve performance of multi-class searches Backport of trunk commit 18233. For as yet unknown reasons, Postgres sometimes uses nested loops instead of hash joins when joining subqueries in a multi-class bib search. This can result in searches such as "author:rowling title:harry potter" never completing. Removing the interior limit clause can both speed up the query and make search results more accurate when combining queries of two or more common words. Signed-off-by: Galen Charlton git-svn-id: svn://svn.open-ils.org/ILS/branches/rel_1_6@19263 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/300.schema.staged_search.sql | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) diff --git a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql index 30ef658c38..aaa73b74ba 100644 --- a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql +++ b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql @@ -232,7 +232,7 @@ BEGIN from_clause := from_clause || ' JOIN ( SELECT * FROM ' || query_table || ' WHERE ' || inner_where_clause || - CASE WHEN core_rel_limit > 0 THEN ' LIMIT ' || core_rel_limit::TEXT ELSE '' END || ' ) AS ' || query_part.table_alias || + ' ) AS ' || query_part.table_alias || ' ON ( m.source = ' || query_part.table_alias || '.source )' || ' JOIN config.metabib_field AS ' || query_part.table_alias || '_weight' || ' ON ( ' || query_part.table_alias || '.field = ' || query_part.table_alias || '_weight.id AND ' || query_part.table_alias || '_weight.search_field)'; @@ -349,7 +349,8 @@ BEGIN END IF; core_rel_query := select_clause || from_clause || where_clause || - ' GROUP BY 1 ORDER BY 4' || CASE WHEN sort_desc THEN ' DESC' ELSE ' ASC' END || ';'; + ' GROUP BY 1 ORDER BY 4' || CASE WHEN sort_desc THEN ' DESC' ELSE ' ASC' END || + CASE WHEN core_rel_limit > 0 THEN ' LIMIT ' || core_rel_limit::TEXT ELSE '' END || ';'; --RAISE NOTICE 'Base Query: %', core_rel_query; IF param_search_ou > 0 THEN -- 2.11.0