Searching by shelving location issues a monster query that often times out

[Jane Sandberg]

For many of our shelving locations, if I try to limit my OPAC search results to a particular shelving location, Evergreen produces an SQL query that takes an incredible amount of time. The query continues to run after the Apache time out so the search returns no results to the user. The query continues for around to 30 seconds after Evergreen returns that blank results page.

Here is what the query looks like for our shelving location #436:

   SELECT *
   -- bib search: #CD_documentLength #CD_meanHarmonic #CD_uniqueWords skip_check(8000) core_limit(10000) limit(1000) estimation_strategy(inclusion) keyword: locations(436) site(LBCCLIB) depth(2)+
          FROM search.query_parser_fts(
                    7::INT,
                    2::INT,
                    $core_query_3828$
WITH lang_with AS (SELECT id FROM config.coded_value_map WHERE ctype = 'item_lang' AND code = $_3828$eng$_3828$)
SELECT m.source AS id,
        ARRAY[m.source] AS records,
        1.0/((AVG(
          (1)
        )+1 * COALESCE( NULLIF( FIRST(mrv.vlist @> ARRAY[lang_with.id]), FALSE )::INT * 5, 1)))::NUMERIC AS rel,
        1.0/((AVG(
          (1)
        )+1 * COALESCE( NULLIF( FIRST(mrv.vlist @> ARRAY[lang_with.id]), FALSE )::INT * 5, 1)))::NUMERIC AS rank, +
        FIRST(pubdate_t.value) AS tie_break
  FROM metabib.metarecord_source_map m

        LEFT JOIN metabib.record_sorter pubdate_t ON m.source = pubdate_t.source AND attr = 'pubdate'

        INNER JOIN metabib.record_attr_vector_list mrv ON m.source = mrv.source

        ,lang_with
  WHERE 1=1
        AND (
          TRUE
        )
  GROUP BY 1
  ORDER BY 4 ASC NULLS LAST, 5 DESC NULLS LAST, 3 DESC
  LIMIT 10000
$core_query_3828$::TEXT,
                    $${}$$::INT[],
                    $${"436"}$$::INT[],
                    8000::INT,
                    1000::INT,
                    10000::INT,
                    'f'::BOOL,
                    'f'::BOOL,
                    'f'::BOOL,
                    NULL::INT
                );

Thanks very much to Rogan Hamby for putting together the detailed information in this bug report.

[Mike Rylander (mrylander)]

The query shows that no search terms were entered, just filters. We try to prevent that in the UI, so I wonder if that was intended?

[Chris Sharp (chrissharp123)]

Looks like the long-running queries we see on the PINES servers where users restrict to a branch or shelving location and try to display the newest items by pub date. This is very common from both staff and patrons.

[Chris Sharp (chrissharp123)]

For reference, here is my bug about pubdate searches: https://bugs.launchpad.net/evergreen/+bug/1514549

I haven't determined if this bug should be considered a duplicate of mine or not.

[Mike Rylander (mrylander)]

Chris, it is the same set of complications, so I call this a duplicate. The bug, as it were, is that we don't have "show me everything"-optimized browse-ish options as yet.

[Chris Sharp (chrissharp123)]

Marked as duplicate of bug 1514549.