G2A Many GEOs
SHARE
TWEET

Staff catalog query global flag enabled

kmlussier Jan 4th, 2018 (edited) 76 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. 2018-01-04 10:40:00.665 EST [21004] evergreen@evergreen LOG:  duration: 185.446 ms  statement:         -- bib search: #CD_documentLength #CD_meanHarmonic #CD_uniqueWords #staff core_limit(100000) badge_orgs(1,2) estimation_strategy(inclusion) skip_check(0) check_limit(1000) immigration law site(SYS1) depth(1)
  2.                 WITH w AS (
  3.  
  4.  
  5.         WITH xe503f80_keyword_xq AS (SELECT
  6.               (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_21003$immigration$_21003$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_21003$immigration$_21003$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
  7.               (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_21003$law$_21003$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_21003$law$_21003$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) AS tsq,
  8.               (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_21003$immigration$_21003$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_21003$immigration$_21003$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
  9.               (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_21003$law$_21003$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_21003$law$_21003$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) AS tsq_rank ),lang_with AS (SELECT id FROM config.coded_value_map WHERE ctype = 'item_lang' AND code = $_21003$eng$_21003$),        pop_with AS (
  10.                     SELECT  record,
  11.                             ARRAY_AGG(badge) AS badges,
  12.                             SUM(s.score::NUMERIC*b.weight::NUMERIC)/SUM(b.weight::NUMERIC) AS total_score
  13.                       FROM  rating.record_badge_score s
  14.                             JOIN rating.badge b ON (
  15.                                 b.id = s.badge
  16.          AND b.scope = ANY ('{1,2}')) GROUP BY 1)
  17.         ,c_attr AS (SELECT (ARRAY_TO_STRING(ARRAY[search.calculate_visibility_attribute_test('circ_lib','{2,4,5,8}',FALSE)],'&'))::query_int AS vis_test FROM asset.patron_default_visibility_mask() x)
  18.         ,b_attr AS (SELECT (ARRAY_TO_STRING(ARRAY[search.calculate_visibility_attribute_test('luri_org','{1,2}',FALSE)],'&'))::query_int AS vis_test FROM asset.patron_default_visibility_mask() x)
  19.         SELECT  id,
  20.                 rel,
  21.                 CASE WHEN cardinality(records) = 1 THEN records[1] ELSE NULL END AS record,
  22.                 NULL::INT AS total,
  23.                 NULL::INT AS checked,
  24.                 NULL::INT AS visible,
  25.                 NULL::INT AS deleted,
  26.                 NULL::INT AS excluded,
  27.                 badges,
  28.                 popularity
  29.           FROM  (SELECT m.source AS id,
  30.                       ARRAY[m.source] AS records,
  31.                         (AVG(
  32.                   (COALESCE(ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', xe503f80_keyword.index_vector, xe503f80_keyword.tsq_rank, 14) * xe503f80_keyword.weight, 0.0)
  33.                   * evergreen.rel_bump(('{' || quote_literal(search_normalize($_21003$immigration$_21003$)) || ',' || quote_literal(search_normalize($_21003$law$_21003$)) || '}')::TEXT[], xe503f80_keyword.value, '{word_order}'::TEXT[], '{10}'::NUMERIC[]))
  34.                 )+1 * COALESCE( NULLIF( FIRST(mrv.vlist @> ARRAY[lang_with.id]), FALSE )::INT * 5, 1))::NUMERIC AS rel,
  35.                         1.0/((AVG(
  36.                   (COALESCE(ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', xe503f80_keyword.index_vector, xe503f80_keyword.tsq_rank, 14) * xe503f80_keyword.weight, 0.0)
  37.                   * evergreen.rel_bump(('{' || quote_literal(search_normalize($_21003$immigration$_21003$)) || ',' || quote_literal(search_normalize($_21003$law$_21003$)) || '}')::TEXT[], xe503f80_keyword.value, '{word_order}'::TEXT[], '{10}'::NUMERIC[]))
  38.                 )+1 * COALESCE( NULLIF( FIRST(mrv.vlist @> ARRAY[lang_with.id]), FALSE )::INT * 5, 1)))::NUMERIC AS rank,
  39.                         FIRST(pubdate_t.value) AS tie_break,
  40.                         STRING_AGG(ARRAY_TO_STRING(pop_with.badges,','),',') AS badges,
  41.                         AVG(COALESCE(pop_with.total_score::NUMERIC,0.0::NUMERIC))::NUMERIC(2,1) AS popularity
  42.                   FROM  metabib.metarecord_source_map m
  43.  
  44.                 LEFT JOIN (
  45.                   SELECT fe.*, fe_weight.weight, xe503f80_keyword_xq.tsq, xe503f80_keyword_xq.tsq_rank /* search */
  46.                     FROM  metabib.keyword_field_entry AS fe
  47.                       JOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)
  48.                     JOIN xe503f80_keyword_xq ON (fe.index_vector @@ xe503f80_keyword_xq.tsq)
  49.                 ) AS xe503f80_keyword ON (m.source = xe503f80_keyword.source)
  50.  
  51.                         INNER JOIN metabib.record_attr_vector_list mrv ON m.source = mrv.source
  52.                         INNER JOIN biblio.record_entry bre ON m.source = bre.id
  53.                         LEFT JOIN pop_with ON ( m.source = pop_with.record )
  54.                         LEFT JOIN metabib.record_sorter pubdate_t ON m.source = pubdate_t.source AND attr = 'pubdate'
  55.                         ,lang_with
  56.                         ,c_attr
  57.                         ,b_attr
  58.                   WHERE 1=1
  59.                         AND (
  60.                   (xe503f80_keyword.id IS NOT NULL)
  61.                 )
  62.                 AND (
  63.                   ((EXISTS (SELECT 1 FROM asset.copy_vis_attr_cache WHERE record = m.source AND vis_attr_vector @@ c_attr.vis_test) OR (NOT EXISTS (SELECT 1 FROM asset.copy_vis_attr_cache WHERE record = m.source) AND (bre.vis_attr_vector IS NULL OR NOT ( int4range(0,268435455,'[]') @> ANY(bre.vis_attr_vector) ))))) OR ((b_attr.vis_test IS NULL OR bre.vis_attr_vector @@ b_attr.vis_test))
  64.                 )
  65.                   GROUP BY 1
  66.                   ORDER BY 4 ASC NULLS LAST,  5 DESC NULLS LAST, 3 DESC
  67.                       LIMIT 100000
  68.                 ) AS core_query
  69.         ) (SELECT * FROM w LIMIT 1000 OFFSET 0)
  70.                 UNION ALL
  71.           SELECT NULL,NULL,NULL,COUNT(*),COUNT(*),COUNT(*),0,0,NULL,NULL FROM w;
RAW Paste Data
Ledger Nano X - The secure hardware wallet
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top