Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH x9ffb7d0_keyword_xq AS (SELECT
- (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_12192$wiregrass$_12192$)),E'(?:\\s+|:)','&','g'),'&|') || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_12192$wiregrass$_12192$)),E'(?:\\s+|:)','&','g'),'&|') || ')', '()'), ''))) AS tsq,
- (to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_12192$wiregrass$_12192$)),E'(?:\\s+|:)','&','g'),'&|') || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_12192$wiregrass$_12192$)),E'(?:\\s+|:)','&','g'),'&|') || ')', '()'), ''))) AS tsq_rank ),lang_with AS (SELECT id FROM config.coded_value_map WHERE ctype = 'item_lang' AND code = $_12192$eng$_12192$)
- 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}', x9ffb7d0_keyword.index_vector, x9ffb7d0_keyword.tsq_rank, 14) * x9ffb7d0_keyword.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}', x9ffb7d0_keyword.index_vector, x9ffb7d0_keyword.tsq_rank, 14) * x9ffb7d0_keyword.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, x9ffb7d0_keyword_xq.tsq, x9ffb7d0_keyword_xq.tsq_rank /* search */
- FROM metabib.keyword_field_entry AS fe
- JOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)
- JOIN x9ffb7d0_keyword_xq ON (fe.index_vector @@ x9ffb7d0_keyword_xq.tsq)
- ) AS x9ffb7d0_keyword ON (m.source = x9ffb7d0_keyword.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 (
- (x9ffb7d0_keyword.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;
- QUERY PLAN
- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Limit (cost=15555.88..15555.91 rows=10 width=695) (actual time=30202.210..30202.257 rows=125 loops=1)
- CTE x9ffb7d0_keyword_xq
- -> Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.653..0.655 rows=1 loops=1)
- CTE lang_with
- -> Seq Scan on coded_value_map (cost=0.00..15.79 rows=1 width=4) (actual time=0.166..0.208 rows=1 loops=1)
- Filter: ((ctype = 'item_lang'::text) AND (code = 'eng'::text))
- Rows Removed by Filter: 652
- -> Sort (cost=15540.05..15540.08 rows=10 width=695) (actual time=30202.208..30202.228 rows=125 loops=1)
- Sort Key: ((1.0 / ((avg(COALESCE((ts_rank_cd('{0.1,0.2,0.4,1}'::real[], fe.index_vector, x9ffb7d0_keyword_xq.tsq_rank, 14) * (fe_weight.weight)::double precision), 0::double precision)) + ((1 * COALESCE(((NULLIF(first((mrv.vlist @> ARRAY[lang_with.id])), false))::integer * 5), 1)))::double precision))::numeric)), (first(pubdate_t.value))
- Sort Method: quicksort Memory: 42kB
- -> HashAggregate (cost=15539.34..15539.89 rows=10 width=695) (actual time=30201.375..30201.956 rows=125 loops=1)
- -> Nested Loop Left Join (cost=139.37..15531.56 rows=10 width=695) (actual time=30194.613..30199.191 rows=125 loops=1)
- -> Nested Loop (cost=138.94..15523.93 rows=10 width=680) (actual time=30194.489..30196.706 rows=125 loops=1)
- Join Filter: (fe.field = fe_weight.id)
- Rows Removed by Join Filter: 5500
- -> Seq Scan on metabib_field fe_weight (cost=0.00..1.45 rows=45 width=8) (actual time=0.013..0.027 rows=45 loops=1)
- -> Materialize (cost=138.94..15515.75 rows=10 width=680) (actual time=23.052..670.989 rows=125 loops=45)
- -> Nested Loop (cost=138.94..15515.70 rows=10 width=680) (actual time=1037.290..30191.394 rows=125 loops=1)
- Join Filter: (fe.index_vector @@ x9ffb7d0_keyword_xq.tsq)
- Rows Removed by Join Filter: 1478964
- -> Nested Loop (cost=0.00..0.05 rows=1 width=68) (actual time=0.828..0.881 rows=1 loops=1)
- -> CTE Scan on lang_with (cost=0.00..0.02 rows=1 width=4) (actual time=0.168..0.212 rows=1 loops=1)
- -> CTE Scan on x9ffb7d0_keyword_xq (cost=0.00..0.02 rows=1 width=64) (actual time=0.658..0.662 rows=1 loops=1)
- -> Nested Loop (cost=138.94..15491.73 rows=1914 width=644) (actual time=352.828..22616.674 rows=1479089 loops=1)
- -> Nested Loop (cost=138.51..13362.23 rows=1914 width=99) (actual time=348.005..10026.647 rows=1479089 loops=1)
- -> Bitmap Heap Scan on record_attr_vector_list mrv (cost=138.08..6268.44 rows=1817 width=91) (actual time=347.909..916.661 rows=1478279 loops=1)
- Recheck Cond: (vlist @@ '610'::query_int)
- -> Bitmap Index Scan on mrca_vlist_idx (cost=0.00..137.63 rows=1817 width=0) (actual time=334.693..334.693 rows=1478738 loops=1)
- Index Cond: (vlist @@ '610'::query_int)
- -> Index Only Scan using metabib_metarecord_source_map_source_record_idx on metarecord_source_map m (cost=0.43..3.88 rows=2 width=8) (actual time=0.005..0.005 rows=1 loops=1478279)
- Index Cond: (source = mrv.source)
- Heap Fetches: 51509
- -> Index Scan using metabib_keyword_field_entry_source_idx on keyword_field_entry fe (cost=0.43..1.10 rows=1 width=561) (actual time=0.007..0.007 rows=1 loops=1479089)
- Index Cond: (source = m.source)
- Filter: (id IS NOT NULL)
- -> Index Scan using metabib_sorter_source_idx on record_sorter pubdate_t (cost=0.43..0.75 rows=1 width=23) (actual time=0.018..0.018 rows=1 loops=125)
- Index Cond: (m.source = source)
- Filter: (attr = 'pubdate'::text)
- Rows Removed by Filter: 2
- Total runtime: 30203.681 ms
- (40 rows)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement