Advertisement
Guest User

Untitled

a guest
May 19th, 2015
265
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH x9ffb7d0_keyword_xq AS (SELECT  
  2.       (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,
  3.       (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$)
  4. SELECT  m.source AS id,
  5.         ARRAY[m.source] AS records,
  6.         1.0/((AVG(
  7.           (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))
  8.         )+1 * COALESCE( NULLIF( FIRST(mrv.vlist @> ARRAY[lang_with.id]), FALSE )::INT * 5, 1)))::NUMERIC AS rel,
  9.         1.0/((AVG(
  10.           (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))
  11.         )+1 * COALESCE( NULLIF( FIRST(mrv.vlist @> ARRAY[lang_with.id]), FALSE )::INT * 5, 1)))::NUMERIC AS rank,  
  12.         FIRST(pubdate_t.value) AS tie_break
  13.   FROM  metabib.metarecord_source_map m
  14.          
  15.         LEFT JOIN (
  16.           SELECT fe.*, fe_weight.weight, x9ffb7d0_keyword_xq.tsq, x9ffb7d0_keyword_xq.tsq_rank /* search */
  17.             FROM  metabib.keyword_field_entry AS fe
  18.               JOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)
  19.             JOIN x9ffb7d0_keyword_xq ON (fe.index_vector @@ x9ffb7d0_keyword_xq.tsq)
  20.         ) AS x9ffb7d0_keyword ON (m.source = x9ffb7d0_keyword.source)
  21.         LEFT JOIN metabib.record_sorter pubdate_t ON m.source = pubdate_t.source AND attr = 'pubdate'
  22.          
  23.         INNER JOIN metabib.record_attr_vector_list mrv ON m.source = mrv.source
  24.          
  25.         ,lang_with
  26.   WHERE 1=1
  27.         AND (
  28.           (x9ffb7d0_keyword.id IS NOT NULL)
  29.           AND mrv.vlist @@ '(610)'
  30.         )
  31.   GROUP BY 1
  32.   ORDER BY 4 ASC NULLS LAST, 5 DESC NULLS LAST, 3 DESC
  33.   LIMIT 10000;
  34.  
  35.  
  36.                                                                                                                                                                          QUERY PLAN                                                                                                                                                                          
  37. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  38.  Limit  (cost=15555.88..15555.91 rows=10 width=695) (actual time=30202.210..30202.257 rows=125 loops=1)
  39.    CTE x9ffb7d0_keyword_xq
  40.      ->  Result  (cost=0.00..0.03 rows=1 width=0) (actual time=0.653..0.655 rows=1 loops=1)
  41.    CTE lang_with
  42.      ->  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)
  43.            Filter: ((ctype = 'item_lang'::text) AND (code = 'eng'::text))
  44.            Rows Removed by Filter: 652
  45.    ->  Sort  (cost=15540.05..15540.08 rows=10 width=695) (actual time=30202.208..30202.228 rows=125 loops=1)
  46.          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))
  47.          Sort Method: quicksort  Memory: 42kB
  48.          ->  HashAggregate  (cost=15539.34..15539.89 rows=10 width=695) (actual time=30201.375..30201.956 rows=125 loops=1)
  49.                ->  Nested Loop Left Join  (cost=139.37..15531.56 rows=10 width=695) (actual time=30194.613..30199.191 rows=125 loops=1)
  50.                      ->  Nested Loop  (cost=138.94..15523.93 rows=10 width=680) (actual time=30194.489..30196.706 rows=125 loops=1)
  51.                            Join Filter: (fe.field = fe_weight.id)
  52.                            Rows Removed by Join Filter: 5500
  53.                            ->  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)
  54.                            ->  Materialize  (cost=138.94..15515.75 rows=10 width=680) (actual time=23.052..670.989 rows=125 loops=45)
  55.                                  ->  Nested Loop  (cost=138.94..15515.70 rows=10 width=680) (actual time=1037.290..30191.394 rows=125 loops=1)
  56.                                        Join Filter: (fe.index_vector @@ x9ffb7d0_keyword_xq.tsq)
  57.                                        Rows Removed by Join Filter: 1478964
  58.                                        ->  Nested Loop  (cost=0.00..0.05 rows=1 width=68) (actual time=0.828..0.881 rows=1 loops=1)
  59.                                              ->  CTE Scan on lang_with  (cost=0.00..0.02 rows=1 width=4) (actual time=0.168..0.212 rows=1 loops=1)
  60.                                              ->  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)
  61.                                        ->  Nested Loop  (cost=138.94..15491.73 rows=1914 width=644) (actual time=352.828..22616.674 rows=1479089 loops=1)
  62.                                              ->  Nested Loop  (cost=138.51..13362.23 rows=1914 width=99) (actual time=348.005..10026.647 rows=1479089 loops=1)
  63.                                                    ->  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)
  64.                                                          Recheck Cond: (vlist @@ '610'::query_int)
  65.                                                          ->  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)
  66.                                                                Index Cond: (vlist @@ '610'::query_int)
  67.                                                    ->  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)
  68.                                                          Index Cond: (source = mrv.source)
  69.                                                          Heap Fetches: 51509
  70.                                              ->  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)
  71.                                                    Index Cond: (source = m.source)
  72.                                                    Filter: (id IS NOT NULL)
  73.                      ->  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)
  74.                            Index Cond: (m.source = source)
  75.                            Filter: (attr = 'pubdate'::text)
  76.                            Rows Removed by Filter: 2
  77.  Total runtime: 30203.681 ms
  78. (40 rows)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement