API tools faq
paste
Advertisement
chrissharp123

Untitled

chrissharp123
Dec 5th, 2022
948
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 11.25 KB | None | 0 0
raw download clone embed print report
  1. -- bib search: #CD_documentLength #CD_meanHarmonic #CD_uniqueWords #staff core_limit(100000) badge_orgs(1) estimation_strategy(inclusion) skip_check(0) check_limit(1000) (keyword:Harry potter chamber of secrets) site(PINES)
  2. explain analyze
  3.         WITH w AS (
  4.  
  5.  
  6. WITH x559f99d69060_keyword_xq AS (SELECT
  7.       (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$Harry$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$Harry$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
  8.       (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$potter$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$potter$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
  9.       (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$chamber$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$chamber$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
  10.       (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$of$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$of$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')))&&
  11.       (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$secrets$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$secrets$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) AS tsq,
  12.       (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$Harry$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$Harry$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
  13.       (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$potter$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$potter$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
  14.       (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$chamber$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$chamber$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
  15.       (to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$of$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$of$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) ||
  16.       (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$secrets$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_2623591$secrets$_2623591$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) AS tsq_rank ),lang_with AS (SELECT id FROM config.coded_value_map WHERE ctype = 'item_lang' AND code = $_2623591$eng$_2623591$),        pop_with AS (
  17.             SELECT  record,
  18.                     ARRAY_AGG(badge) AS badges,
  19.                     SUM(s.score::NUMERIC*b.weight::NUMERIC)/SUM(b.weight::NUMERIC) AS total_score
  20.               FROM  rating.record_badge_score s
  21.                     JOIN rating.badge b ON (
  22.                         b.id = s.badge
  23.  AND b.scope = ANY ('{1}')) GROUP BY 1)
  24. ,c_attr AS (SELECT
  25.   (ARRAY_TO_STRING(ARRAY[search.calculate_visibility_attribute_test('circ_lib','{1,252,221,365,87,321,163,140,152,376,131,106,102,328,46,119,353,145,211,65,21,156,226,203,115,301,182,2,233,68,260,85,96,362,80,231,346,275,197,58,292,184,122,6,344,31,55,216,177,311,124,267,213,33,25,39,209,409,259,234,258,126,117,103,360,191,82,142,3,17,408,246,194,77,248,79,247,356,107,263,411,99,110,15,279,405,166,240,112,288,243,244,242,251,239,241,403,293,345,297,298,86,352,36,372,300,175,227,254,250,331,118,37,262,28,74,355,410,81,84,395,23,11,280,29,62,201,306,59,63,60,135,24,294,193,88,303,94,404,49,111,123,402,26,121,120,137,138,101,155,132,199,144,235,192,374,73,97,9,281,153,90,255,228,5,256,253,284,95,335,57,4,342,52,336,245,148,354,202,407,91,92,93,104,307,308,134,174,109,167,45,18,229,186,343,366,176,147,261,333,116,157,236,210,149,89,208,187,48,161,136,165,206,27,172,158,207,204,212,299,188,309,139,41,180,313,225,108,285,323,322,349,169,183,171,205,56,51,314,78,351,151,43,53,371,317,71,200,282,369,319,230,67,196,113,66,150,286,50,357,223,160,170,72,291,287,350,358,320,290,257,330,98,61,195,305,318,367,141,237,154,304,329,173,100,278,75,401,276,168,114,198,54,266,265,70,295,296,222,190,83,340,22,69,264,146,289,185,380,143,10,133,382,337,249,378,20,8,47,224,379,385,238,381,377,383,384,232,386,373,64,159,398,399,105,7,13,219,220,189,310,30,324,347,76,40,44,38,391,393,396,394,387,179,178,181,19,397,14,12,16,217,218,125,127,128,129,130,269,272,270,32,271,273,268,361,215,214,34,35,162,389,388,390,392,42,316,315,312,326,375,164}',FALSE)],'&'))::query_int AS vis_test FROM asset.patron_default_visibility_mask() x)
  26. ,b_attr AS (SELECT (search.calculate_visibility_attribute_test('luri_org','{1}',FALSE))::query_int AS vis_test FROM asset.patron_default_visibility_mask() x)
  27. SELECT  id,
  28.         rel,
  29.         CASE WHEN cardinality(records) = 1 THEN records[1] ELSE NULL END AS record,
  30.         NULL::INT AS total,
  31.         NULL::INT AS checked,
  32.         NULL::INT AS visible,
  33.         NULL::INT AS deleted,
  34.         NULL::INT AS excluded,
  35.         badges,
  36.         popularity
  37.   FROM  (SELECT m.source AS id,
  38.                 ARRAY[m.source] AS records,
  39.                 (AVG(
  40.           (COALESCE(ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', x559f99d69060_keyword.index_vector, x559f99d69060_keyword.tsq_rank, 14) * x559f99d69060_keyword.weight * 1000, 0.0))
  41.         )+1 * COALESCE( NULLIF( FIRST(mrv.vlist @> ARRAY[lang_with.id]), FALSE )::INT * 5, 1))::NUMERIC AS rel,
  42.                 1.0/((AVG(
  43.           (COALESCE(ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', x559f99d69060_keyword.index_vector, x559f99d69060_keyword.tsq_rank, 14) * x559f99d69060_keyword.weight * 1000, 0.0))
  44.         )+1 * COALESCE( NULLIF( FIRST(mrv.vlist @> ARRAY[lang_with.id]), FALSE )::INT * 5, 1)))::NUMERIC AS rank,
  45.                 FIRST(pubdate_t.value) AS tie_break,
  46.                 STRING_AGG(ARRAY_TO_STRING(pop_with.badges,','),',') AS badges,
  47.                 AVG(COALESCE(pop_with.total_score::NUMERIC,0.0::NUMERIC))::NUMERIC(2,1) AS popularity
  48.           FROM  metabib.metarecord_source_map m
  49.                
  50.         LEFT JOIN (
  51.           SELECT fe.*, fe_weight.weight, x559f99d69060_keyword_xq.tsq, x559f99d69060_keyword_xq.tsq_rank /* search */
  52.             FROM  metabib.keyword_field_entry AS fe
  53.               JOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)
  54.               JOIN x559f99d69060_keyword_xq ON (fe.index_vector @@ x559f99d69060_keyword_xq.tsq)
  55.                 UNION ALL
  56.           SELECT fe.id, fe.source, fe.field, fe.value, fe.index_vector, fe_weight.weight, x559f99d69060_keyword_xq.tsq, x559f99d69060_keyword_xq.tsq_rank /* virtual field addition */
  57.             FROM  metabib.title_field_entry AS fe
  58.               JOIN config.metabib_field_virtual_map AS fe_weight ON (fe_weight.virtual = 45 AND fe_weight.real IN (6,53) AND fe_weight.real = fe.field)
  59.               JOIN x559f99d69060_keyword_xq ON (fe.index_vector @@ x559f99d69060_keyword_xq.tsq)
  60.                 UNION ALL
  61.           SELECT fe.id, fe.source, fe.field, fe.value, fe.index_vector, fe_weight.weight, x559f99d69060_keyword_xq.tsq, x559f99d69060_keyword_xq.tsq_rank /* virtual field addition */
  62.             FROM  metabib.keyword_field_entry AS fe
  63.               JOIN config.metabib_field_virtual_map AS fe_weight ON (fe_weight.virtual = 45 AND fe_weight.real IN (39,41,42,46,47,48,50) AND fe_weight.real = fe.field)
  64.               JOIN x559f99d69060_keyword_xq ON (fe.index_vector @@ x559f99d69060_keyword_xq.tsq)
  65.                 UNION ALL
  66.           SELECT fe.id, fe.source, fe.field, fe.value, fe.index_vector, fe_weight.weight, x559f99d69060_keyword_xq.tsq, x559f99d69060_keyword_xq.tsq_rank /* virtual field addition */
  67.             FROM  metabib.author_field_entry AS fe
  68.               JOIN config.metabib_field_virtual_map AS fe_weight ON (fe_weight.virtual = 45 AND fe_weight.real IN (8) AND fe_weight.real = fe.field)
  69.               JOIN x559f99d69060_keyword_xq ON (fe.index_vector @@ x559f99d69060_keyword_xq.tsq)
  70.                 UNION ALL
  71.           SELECT fe.id, fe.source, fe.field, fe.value, fe.index_vector, fe_weight.weight, x559f99d69060_keyword_xq.tsq, x559f99d69060_keyword_xq.tsq_rank /* virtual field addition */
  72.             FROM  metabib.subject_field_entry AS fe
  73.               JOIN config.metabib_field_virtual_map AS fe_weight ON (fe_weight.virtual = 45 AND fe_weight.real IN (16) AND fe_weight.real = fe.field)
  74.               JOIN x559f99d69060_keyword_xq ON (fe.index_vector @@ x559f99d69060_keyword_xq.tsq)
  75.         ) AS x559f99d69060_keyword ON (m.source = x559f99d69060_keyword.source)
  76.                
  77.                 INNER JOIN metabib.record_attr_vector_list mrv ON m.source = mrv.source
  78.                 INNER JOIN biblio.record_entry bre ON m.source = bre.id AND NOT bre.deleted
  79.                 LEFT JOIN pop_with ON ( m.source = pop_with.record )
  80.                 LEFT JOIN metabib.record_sorter pubdate_t ON m.source = pubdate_t.source AND attr = 'pubdate'
  81.                 ,lang_with
  82.                 ,c_attr
  83.                 ,b_attr
  84.           WHERE 1=1
  85.                 AND (
  86.           (
  87.             (x559f99d69060_keyword.id IS NOT NULL)
  88.           )
  89.         )
  90.         AND (
  91.           ((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))
  92.         )
  93.           GROUP BY 1
  94.           ORDER BY 4 ASC NULLS LAST,  5 DESC NULLS LAST, 3 DESC
  95.           LIMIT 100000
  96.         ) AS core_query
  97. ) (SELECT * FROM w LIMIT 1000 OFFSET 0)
  98.         UNION ALL
  99.   SELECT NULL,NULL,NULL,COUNT(*),COUNT(*),COUNT(*),0,0,NULL,NULL FROM w;
  100.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!