Comment 3 for bug 1187433

Dan Scott (denials) wrote :

Here's the core query that QP is passing to search.query_parser_fts() for a title search for "mens health"

WITH x105058a8_title_xq AS (SELECT
      to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_9405$mens$_9405$)),E'(?:\\s+|:)','&','g'),'&|') || ')', '()'), ''))&&
      to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_9405$health$_9405$)),E'(?:\\s+|:)','&','g'),'&|') || ')', '()'), '')) AS tsq,
      to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_9405$mens$_9405$)),E'(?:\\s+|:)','&','g'),'&|') || ')', '()'), '')) ||
      to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_9405$health$_9405$)),E'(?:\\s+|:)','&','g'),'&|') || ')', '()'), '')) AS tsq_rank )
SELECT m.source AS id,
        ARRAY[m.source] AS records,
        1.0/((AVG(
          (1)
          + (COALESCE(ts_rank_cd('{0.1, 0.2, 0.4, 1}', x105058a8_title.index_vector, x105058a8_title.tsq_rank, 14) * x105058a8_title.weight, 0.0))
        )+1 * COALESCE( NULLIF( FIRST(mrd.attrs @> hstore('item_lang', $_9405$eng$_9405$)), FALSE )::INT * 5, 1)))::NUMERIC AS rel,
        1.0/((AVG(
          (1)
          + (COALESCE(ts_rank_cd('{0.1, 0.2, 0.4, 1}', x105058a8_title.index_vector, x105058a8_title.tsq_rank, 14) * x105058a8_title.weight, 0.0))
        )+1 * COALESCE( NULLIF( FIRST(mrd.attrs @> hstore('item_lang', $_9405$eng$_9405$)), FALSE )::INT * 5, 1)))::NUMERIC AS rank,
        FIRST(mrd.attrs->'date1') AS tie_break
  FROM metabib.metarecord_source_map m

        LEFT JOIN (
          SELECT fe.*, fe_weight.weight, x105058a8_title_xq.tsq, x105058a8_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 x105058a8_title_xq ON (fe.index_vector @@ x105058a8_title_xq.tsq)
        ) AS x105058a8_title ON (m.source = x105058a8_title.source)
        INNER JOIN metabib.record_attr mrd ON m.source = mrd.id

  WHERE 1=1
        AND (
          TRUE
          AND (x105058a8_title.id IS NOT NULL)
        )
  GROUP BY 1