API tools faq
paste
cmptrwz

QP Tests

cmptrwz
Sep 6th, 2012
136
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.40 KB | None | 0 0
raw download clone embed print report
  1. Master:
  2.  
  3. "harry (item_lang(eng) || item_type(g))"
  4.  
  5. WITH x244b0a8_keyword_xq AS (SELECT
  6. to_tsquery('keyword', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_27778$harry$_27778$)),E'(?:\\s+|:)','&','g'),'&|') || ')', '()'), '')) AS tsq )
  7. SELECT m.source AS id,
  8. ARRAY[m.source] AS records,
  9. 1.0/(AVG(
  10. (COALESCE(ts_rank_cd(x244b0a8_keyword.index_vector, x244b0a8_keyword.tsq, 0) * x244b0a8_keyword.weight, 0.0)
  11. * /* word_order */ COALESCE(NULLIF( (search_normalize(x244b0a8_keyword.value) ~ (search_normalize($_27778$harry$_27778$))), FALSE )::INT * 10, 1))
  12. )+1)::NUMERIC AS rel,
  13. 1.0/(AVG(
  14. (COALESCE(ts_rank_cd(x244b0a8_keyword.index_vector, x244b0a8_keyword.tsq, 0) * x244b0a8_keyword.weight, 0.0)
  15. * /* word_order */ COALESCE(NULLIF( (search_normalize(x244b0a8_keyword.value) ~ (search_normalize($_27778$harry$_27778$))), FALSE )::INT * 10, 1))
  16. )+1)::NUMERIC AS rank,
  17. FIRST(mrd.attrs->'date1') AS tie_break
  18. FROM metabib.metarecord_source_map m
  19.  
  20. LEFT JOIN (
  21. SELECT fe.*, fe_weight.weight, x244b0a8_keyword_xq.tsq /* search */
  22. FROM metabib.keyword_field_entry AS fe
  23. JOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)
  24. JOIN x244b0a8_keyword_xq ON (fe.index_vector @@ x244b0a8_keyword_xq.tsq)
  25. ) AS x244b0a8_keyword ON (m.source = x244b0a8_keyword.source)
  26. INNER JOIN metabib.record_attr mrd ON (m.source = mrd.id AND (attrs->'item_lang' IN ($_27778$eng$_27778$) OR attrs->'item_type' IN ($_27778$g$_27778$)))
  27. WHERE 1=1
  28.  
  29. AND ((x244b0a8_keyword.id IS NOT NULL))
  30. GROUP BY 1
  31. ORDER BY 4 ASC NULLS LAST, 5 DESC NULLS LAST, 3 DESC
  32. LIMIT 25000
  33.  
  34. "harry (item_lang(eng) || item_type(g)) title:stone"
  35. NOTE: Invalid SQL, see end of mrd inner join condition and middle of keyword/title join in where clause
  36.  
  37. WITH x160c0a8_keyword_xq AS (SELECT
  38. to_tsquery('keyword', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_27791$harry$_27791$)),E'(?:\\s+|:)','&','g'),'&|') || ')', '()'), '')) AS tsq ),
  39. x28816c8_title_xq AS (SELECT
  40. to_tsquery('title', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_27791$stone$_27791$)),E'(?:\\s+|:)','&','g'),'&|') || ')', '()'), '')) AS tsq )
  41. SELECT m.source AS id,
  42. ARRAY[m.source] AS records,
  43. 1.0/(AVG(
  44. (COALESCE(ts_rank_cd(x160c0a8_keyword.index_vector, x160c0a8_keyword.tsq, 0) * x160c0a8_keyword.weight, 0.0)
  45. * /* word_order */ COALESCE(NULLIF( (search_normalize(x160c0a8_keyword.value) ~ (search_normalize($_27791$harry$_27791$))), FALSE )::INT * 10, 1))+
  46. (COALESCE(ts_rank_cd(x28816c8_title.index_vector, x28816c8_title.tsq, 0) * x28816c8_title.weight, 0.0)
  47. * /* word_order */ COALESCE(NULLIF( (search_normalize(x28816c8_title.value) ~ (search_normalize($_27791$stone$_27791$))), FALSE )::INT * 10, 1)
  48. * /* first_word */ COALESCE(NULLIF( (search_normalize(x28816c8_title.value) ~ ('^'||search_normalize($_27791$stone$_27791$))), FALSE )::INT * 1.5, 1)
  49. * /* full_match */ COALESCE(NULLIF( (search_normalize(x28816c8_title.value) ~ ('^'||search_normalize($_27791$stone$_27791$)||'$')), FALSE )::INT * 20, 1))
  50. )+1)::NUMERIC AS rel,
  51. 1.0/(AVG(
  52. (COALESCE(ts_rank_cd(x160c0a8_keyword.index_vector, x160c0a8_keyword.tsq, 0) * x160c0a8_keyword.weight, 0.0)
  53. * /* word_order */ COALESCE(NULLIF( (search_normalize(x160c0a8_keyword.value) ~ (search_normalize($_27791$harry$_27791$))), FALSE )::INT * 10, 1))+
  54. (COALESCE(ts_rank_cd(x28816c8_title.index_vector, x28816c8_title.tsq, 0) * x28816c8_title.weight, 0.0)
  55. * /* word_order */ COALESCE(NULLIF( (search_normalize(x28816c8_title.value) ~ (search_normalize($_27791$stone$_27791$))), FALSE )::INT * 10, 1)
  56. * /* first_word */ COALESCE(NULLIF( (search_normalize(x28816c8_title.value) ~ ('^'||search_normalize($_27791$stone$_27791$))), FALSE )::INT * 1.5, 1)
  57. * /* full_match */ COALESCE(NULLIF( (search_normalize(x28816c8_title.value) ~ ('^'||search_normalize($_27791$stone$_27791$)||'$')), FALSE )::INT * 20, 1))
  58. )+1)::NUMERIC AS rank,
  59. FIRST(mrd.attrs->'date1') AS tie_break
  60. FROM metabib.metarecord_source_map m
  61.  
  62. LEFT JOIN (
  63. SELECT fe.*, fe_weight.weight, x160c0a8_keyword_xq.tsq /* search */
  64. FROM metabib.keyword_field_entry AS fe
  65. JOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)
  66. JOIN x160c0a8_keyword_xq ON (fe.index_vector @@ x160c0a8_keyword_xq.tsq)
  67. ) AS x160c0a8_keyword ON (m.source = x160c0a8_keyword.source)
  68. LEFT JOIN (
  69. SELECT fe.*, fe_weight.weight, x28816c8_title_xq.tsq /* search */
  70. FROM metabib.title_field_entry AS fe
  71. JOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)
  72. JOIN x28816c8_title_xq ON (fe.index_vector @@ x28816c8_title_xq.tsq)
  73. ) AS x28816c8_title ON (m.source = x28816c8_title.source)
  74. INNER JOIN metabib.record_attr mrd ON (m.source = mrd.id AND (attrs->'item_lang' IN ($_27791$eng$_27791$) OR attrs->'item_type' IN ($_27791$g$_27791$)) AND )
  75. WHERE 1=1
  76.  
  77. AND ((x160c0a8_keyword.id IS NOT NULL)(x28816c8_title.id IS NOT NULL))
  78. GROUP BY 1
  79. ORDER BY 4 ASC NULLS LAST, 5 DESC NULLS LAST, 3 DESC
  80. LIMIT 25000
  81.  
  82.  
  83. Post my fwhere removal:
  84.  
  85. "harry (item_lang(eng) || item_type(g))"
  86.  
  87. WITH x20050a8_keyword_xq AS (SELECT
  88. to_tsquery('keyword', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_32602$harry$_32602$)),E'(?:\\s+|:)','&','g'),'&|') || ')', '()'), '')) AS tsq )
  89. SELECT m.source AS id,
  90. ARRAY[m.source] AS records,
  91. 1.0/(AVG(
  92. (COALESCE(ts_rank_cd(x20050a8_keyword.index_vector, x20050a8_keyword.tsq, 0) * x20050a8_keyword.weight, 0.0)
  93. * /* word_order */ COALESCE(NULLIF( (search_normalize(x20050a8_keyword.value) ~ (search_normalize($_32602$harry$_32602$))), FALSE )::INT * 10, 1))
  94. )+1)::NUMERIC AS rel,
  95. 1.0/(AVG(
  96. (COALESCE(ts_rank_cd(x20050a8_keyword.index_vector, x20050a8_keyword.tsq, 0) * x20050a8_keyword.weight, 0.0)
  97. * /* word_order */ COALESCE(NULLIF( (search_normalize(x20050a8_keyword.value) ~ (search_normalize($_32602$harry$_32602$))), FALSE )::INT * 10, 1))
  98. )+1)::NUMERIC AS rank,
  99. FIRST(mrd.attrs->'date1') AS tie_break
  100. FROM metabib.metarecord_source_map m
  101.  
  102. LEFT JOIN (
  103. SELECT fe.*, fe_weight.weight, x20050a8_keyword_xq.tsq /* search */
  104. FROM metabib.keyword_field_entry AS fe
  105. JOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)
  106. JOIN x20050a8_keyword_xq ON (fe.index_vector @@ x20050a8_keyword_xq.tsq)
  107. ) AS x20050a8_keyword ON (m.source = x20050a8_keyword.source)
  108. INNER JOIN metabib.record_attr mrd ON (m.source = mrd.id)
  109. WHERE 1=1
  110.  
  111. AND ((x20050a8_keyword.id IS NOT NULL) AND (( OR attrs->'item_lang' IN ($_32602$eng$_32602$) OR attrs->'item_type' IN ($_32602$g$_32602$))))
  112. GROUP BY 1
  113. ORDER BY 4 ASC NULLS LAST, 5 DESC NULLS LAST, 3 DESC
  114. LIMIT 25000
  115.  
  116. "harry (item_lang(eng) || item_type(g)) title:stone"
  117.  
  118. WITH x15a90a8_keyword_xq AS (SELECT
  119. to_tsquery('keyword', COALESCE(NULLIF( '(' || btrim(regexp_replace(split_date_range(search_normalize($_32612$harry$_32612$)),E'(?:\\s+|:)','&','g'),'&|') || ')', '()'), '')) AS tsq ),
  120. x281e628_title_xq AS (SELECT
  121. to_tsquery('title', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_32612$stone$_32612$)),E'(?:\\s+|:)','&','g'),'&|') || ')', '()'), '')) AS tsq )
  122. SELECT m.source AS id,
  123. ARRAY[m.source] AS records,
  124. 1.0/(AVG(
  125. (COALESCE(ts_rank_cd(x15a90a8_keyword.index_vector, x15a90a8_keyword.tsq, 0) * x15a90a8_keyword.weight, 0.0)
  126. * /* word_order */ COALESCE(NULLIF( (search_normalize(x15a90a8_keyword.value) ~ (search_normalize($_32612$harry$_32612$))), FALSE )::INT * 10, 1))+
  127. (COALESCE(ts_rank_cd(x281e628_title.index_vector, x281e628_title.tsq, 0) * x281e628_title.weight, 0.0)
  128. * /* word_order */ COALESCE(NULLIF( (search_normalize(x281e628_title.value) ~ (search_normalize($_32612$stone$_32612$))), FALSE )::INT * 10, 1)
  129. * /* first_word */ COALESCE(NULLIF( (search_normalize(x281e628_title.value) ~ ('^'||search_normalize($_32612$stone$_32612$))), FALSE )::INT * 1.5, 1)
  130. * /* full_match */ COALESCE(NULLIF( (search_normalize(x281e628_title.value) ~ ('^'||search_normalize($_32612$stone$_32612$)||'$')), FALSE )::INT * 20, 1))
  131. )+1)::NUMERIC AS rel,
  132. 1.0/(AVG(
  133. (COALESCE(ts_rank_cd(x15a90a8_keyword.index_vector, x15a90a8_keyword.tsq, 0) * x15a90a8_keyword.weight, 0.0)
  134. * /* word_order */ COALESCE(NULLIF( (search_normalize(x15a90a8_keyword.value) ~ (search_normalize($_32612$harry$_32612$))), FALSE )::INT * 10, 1))+
  135. (COALESCE(ts_rank_cd(x281e628_title.index_vector, x281e628_title.tsq, 0) * x281e628_title.weight, 0.0)
  136. * /* word_order */ COALESCE(NULLIF( (search_normalize(x281e628_title.value) ~ (search_normalize($_32612$stone$_32612$))), FALSE )::INT * 10, 1)
  137. * /* first_word */ COALESCE(NULLIF( (search_normalize(x281e628_title.value) ~ ('^'||search_normalize($_32612$stone$_32612$))), FALSE )::INT * 1.5, 1)
  138. * /* full_match */ COALESCE(NULLIF( (search_normalize(x281e628_title.value) ~ ('^'||search_normalize($_32612$stone$_32612$)||'$')), FALSE )::INT * 20, 1))
  139. )+1)::NUMERIC AS rank,
  140. FIRST(mrd.attrs->'date1') AS tie_break
  141. FROM metabib.metarecord_source_map m
  142.  
  143. LEFT JOIN (
  144. SELECT fe.*, fe_weight.weight, x15a90a8_keyword_xq.tsq /* search */
  145. FROM metabib.keyword_field_entry AS fe
  146. JOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)
  147. JOIN x15a90a8_keyword_xq ON (fe.index_vector @@ x15a90a8_keyword_xq.tsq)
  148. ) AS x15a90a8_keyword ON (m.source = x15a90a8_keyword.source)
  149. LEFT JOIN (
  150. SELECT fe.*, fe_weight.weight, x281e628_title_xq.tsq /* search */
  151. FROM metabib.title_field_entry AS fe
  152. JOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)
  153. JOIN x281e628_title_xq ON (fe.index_vector @@ x281e628_title_xq.tsq)
  154. ) AS x281e628_title ON (m.source = x281e628_title.source)
  155. INNER JOIN metabib.record_attr mrd ON (m.source = mrd.id)
  156. WHERE 1=1
  157.  
  158. AND ((x15a90a8_keyword.id IS NOT NULL) AND (( OR attrs->'item_lang' IN ($_32612$eng$_32612$) OR attrs->'item_type' IN ($_32612$g$_32612$))) AND (x281e628_title.id IS NOT NULL))
  159. GROUP BY 1
  160. ORDER BY 4 ASC NULLS LAST, 5 DESC NULLS LAST, 3 DESC
  161. LIMIT 25000
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
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!