BEGIN;

\timing
\echo without function
\o core-query-compare-output

EXPLAIN ANALYZE
WITH xa741de8_title_xq AS (SELECT
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$how$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$how$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
     (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$to$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$to$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
     (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$become$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$become$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$a$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$a$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$video$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$video$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$game$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$game$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$artist$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$artist$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) AS tsq,
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$how$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$how$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$to$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$to$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$become$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$become$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$a$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$a$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$video$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$video$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$game$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$game$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$artist$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$artist$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) AS tsq_rank ),lang_with AS (SELECT id FROM config.coded_value_map WHERE ctype = 'item_lang' AND code = $_11190$eng$_11190$)
SELECT  m.source AS id,
        ARRAY[m.source] AS records,
        1.0/((AVG(
          (COALESCE(ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', xa741de8_title.index_vector, xa741de8_title.tsq_rank, 14) * xa741de8_title.weight, 0.0))
        )+1 * COALESCE( NULLIF( FIRST(mrv.vlist @> ARRAY[lang_with.id]), FALSE )::INT * 5, 1)))::NUMERIC AS rel,
        1.0/((AVG(
          (COALESCE(ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', xa741de8_title.index_vector, xa741de8_title.tsq_rank, 14) * xa741de8_title.weight, 0.0))
        )+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 (
          SELECT fe.*, fe_weight.weight, xa741de8_title_xq.tsq, xa741de8_title_xq.tsq_rank /* search */
            FROM  metabib.title_field_entry AS fe
              JOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)
            JOIN xa741de8_title_xq ON (fe.index_vector @@ xa741de8_title_xq.tsq)
        ) AS xa741de8_title ON (m.source = xa741de8_title.source)
        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 (
          (xa741de8_title.id IS NOT NULL)
          AND mrv.vlist @@ '(610)'
        )
  GROUP BY 1
  ORDER BY 4 ASC NULLS LAST, 5 DESC NULLS LAST, 3 DESC
  LIMIT 10000
;

CREATE FUNCTION evergreen.query_int_wrapper (INT[],TEXT)
 RETURNS BOOL AS $$ BEGIN RETURN $1 @@ $2::query_int; END; $$
LANGUAGE PLPGSQL STABLE;

\echo with function
EXPLAIN ANALYZE
WITH xa741de8_title_xq AS (SELECT
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$how$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$how$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
     (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$to$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$to$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
     (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$become$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$become$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$a$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$a$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$video$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$video$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$game$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$game$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$artist$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$artist$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) AS tsq,
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$how$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$how$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$to$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$to$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$become$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$become$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$a$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$a$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$video$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$video$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$game$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$game$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
      (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$artist$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_11190$artist$_11190$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) AS tsq_rank ),lang_with AS (SELECT id FROM config.coded_value_map WHERE ctype = 'item_lang' AND code = $_11190$eng$_11190$)
SELECT  m.source AS id,
        ARRAY[m.source] AS records,
        1.0/((AVG(
          (COALESCE(ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', xa741de8_title.index_vector, xa741de8_title.tsq_rank, 14) * xa741de8_title.weight, 0.0))
        )+1 * COALESCE( NULLIF( FIRST(mrv.vlist @> ARRAY[lang_with.id]), FALSE )::INT * 5, 1)))::NUMERIC AS rel,
        1.0/((AVG(
          (COALESCE(ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', xa741de8_title.index_vector, xa741de8_title.tsq_rank, 14) * xa741de8_title.weight, 0.0))
        )+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 (
          SELECT fe.*, fe_weight.weight, xa741de8_title_xq.tsq, xa741de8_title_xq.tsq_rank /* search */
            FROM  metabib.title_field_entry AS fe
              JOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)
            JOIN xa741de8_title_xq ON (fe.index_vector @@ xa741de8_title_xq.tsq)
        ) AS xa741de8_title ON (m.source = xa741de8_title.source)
        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 (
          (xa741de8_title.id IS NOT NULL)
          AND evergreen.query_int_wrapper( mrv.vlist, '(610)' )
        )
  GROUP BY 1
  ORDER BY 4 ASC NULLS LAST, 5 DESC NULLS LAST, 3 DESC
  LIMIT 10000
;

\o
\timing

ROLLBACK;

