QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=31684.47..31684.49 rows=8 width=268) (actual time=87.039..87.039 rows=0 loops=1) CTE xa741de8_title_xq -> Result (cost=0.00..0.22 rows=1 width=0) (actual time=0.929..0.932 rows=1 loops=1) CTE lang_with -> Seq Scan on coded_value_map (cost=0.00..18.63 rows=1 width=4) (actual time=0.036..0.189 rows=1 loops=1) Filter: ((ctype = 'item_lang'::text) AND (code = 'eng'::text)) -> Sort (cost=31665.63..31665.65 rows=8 width=268) (actual time=87.036..87.036 rows=0 loops=1) Sort Key: ((1.0 / ((avg(COALESCE((ts_rank_cd('{0.1,0.2,0.4,1}'::real[], fe.index_vector, xa741de8_title_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: 25kB -> HashAggregate (cost=31665.07..31665.51 rows=8 width=268) (actual time=86.991..86.991 rows=0 loops=1) -> Nested Loop (cost=1682.31..31658.85 rows=8 width=268) (actual time=86.987..86.987 rows=0 loops=1) -> CTE Scan on lang_with (cost=0.00..0.02 rows=1 width=4) (actual time=0.044..0.198 rows=1 loops=1) -> Nested Loop Left Join (cost=1682.31..31658.75 rows=8 width=264) (actual time=86.782..86.782 rows=0 loops=1) -> Nested Loop (cost=1682.31..31645.65 rows=8 width=249) (actual time=86.781..86.781 rows=0 loops=1) -> Nested Loop (cost=1682.31..31643.41 rows=8 width=249) (actual time=86.779..86.779 rows=0 loops=1) -> Nested Loop (cost=1682.31..31580.10 rows=8 width=257) (actual time=86.778..86.778 rows=0 loops=1) -> Nested Loop (cost=1682.31..25641.11 rows=8107 width=167) (actual time=86.776..86.776 rows=0 loops=1) -> CTE Scan on xa741de8_title_xq (cost=0.00..0.02 rows=1 width=64) (actual time=0.933..0.938 rows=1 loops=1) -> Bitmap Heap Scan on title_field_entry fe (cost=1682.31..25539.75 rows=8107 width=135) (actual time=85.830..85.830 rows=0 loops=1) Recheck Cond: (index_vector @@ xa741de8_title_xq.tsq) Filter: (id IS NOT NULL) -> Bitmap Index Scan on metabib_title_field_entry_index_vector_idx (cost=0.00..1680.28 rows=8107 width=0) (actual time=85.321..85.321 rows=5 loops=1) Index Cond: (index_vector @@ xa741de8_title_xq.tsq) -> Index Scan using record_attr_vector_list_pkey on record_attr_vector_list mrv (cost=0.00..0.72 rows=1 width=90) (never executed) Index Cond: (source = fe.source) Filter: (vlist @@ '610'::query_int) -> Index Scan using metabib_metarecord_source_map_source_record_idx on metarecord_source_map m (cost=0.00..7.90 rows=1 width=8) (never executed) Index Cond: (source = fe.source) -> Index Scan using metabib_field_pkey on metabib_field fe_weight (cost=0.00..0.27 rows=1 width=8) (never executed) Index Cond: (id = fe.field) -> Index Scan using metabib_sorter_source_idx on record_sorter pubdate_t (cost=0.00..1.61 rows=2 width=23) (never executed) Index Cond: (m.source = source) Filter: (attr = 'pubdate'::text) Total runtime: 87.635 ms (34 rows) CREATE FUNCTION QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=42532.35..42539.13 rows=2712 width=268) (actual time=57.868..57.868 rows=0 loops=1) CTE xa741de8_title_xq -> Result (cost=0.00..0.22 rows=1 width=0) (actual time=0.170..0.171 rows=1 loops=1) CTE lang_with -> Seq Scan on coded_value_map (cost=0.00..18.63 rows=1 width=4) (actual time=0.017..0.145 rows=1 loops=1) Filter: ((ctype = 'item_lang'::text) AND (code = 'eng'::text)) -> Sort (cost=42513.50..42520.28 rows=2712 width=268) (actual time=57.866..57.866 rows=0 loops=1) Sort Key: ((1.0 / ((avg(COALESCE((ts_rank_cd('{0.1,0.2,0.4,1}'::real[], fe.index_vector, xa741de8_title_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: 25kB -> HashAggregate (cost=42209.69..42358.85 rows=2712 width=268) (actual time=57.851..57.851 rows=0 loops=1) -> Nested Loop (cost=1686.09..40101.11 rows=2712 width=268) (actual time=57.832..57.832 rows=0 loops=1) -> CTE Scan on lang_with (cost=0.00..0.02 rows=1 width=4) (actual time=0.020..0.150 rows=1 loops=1) -> Nested Loop Left Join (cost=1686.09..40073.97 rows=2712 width=264) (actual time=57.678..57.678 rows=0 loops=1) -> Hash Join (cost=1686.09..35649.49 rows=2702 width=249) (actual time=57.677..57.677 rows=0 loops=1) Hash Cond: (fe.field = fe_weight.id) -> Nested Loop (cost=1682.31..35608.55 rows=2702 width=249) (actual time=57.515..57.515 rows=0 loops=1) -> Nested Loop (cost=1682.31..33586.59 rows=2702 width=257) (actual time=57.514..57.514 rows=0 loops=1) -> Nested Loop (cost=1682.31..25641.11 rows=8107 width=167) (actual time=57.512..57.512 rows=0 loops=1) -> CTE Scan on xa741de8_title_xq (cost=0.00..0.02 rows=1 width=64) (actual time=0.173..0.178 rows=1 loops=1) -> Bitmap Heap Scan on title_field_entry fe (cost=1682.31..25539.75 rows=8107 width=135) (actual time=57.328..57.328 rows=0 loops=1) Recheck Cond: (index_vector @@ xa741de8_title_xq.tsq) Filter: (id IS NOT NULL) -> Bitmap Index Scan on metabib_title_field_entry_index_vector_idx (cost=0.00..1680.28 rows=8107 width=0) (actual time=57.103..57.103 rows=5 loops=1) Index Cond: (index_vector @@ xa741de8_title_xq.tsq) -> Index Scan using record_attr_vector_list_pkey on record_attr_vector_list mrv (cost=0.00..0.97 rows=1 width=90) (never executed) Index Cond: (source = fe.source) Filter: query_int_wrapper(vlist, '(610)'::text) -> Index Scan using metabib_metarecord_source_map_source_record_idx on metarecord_source_map m (cost=0.00..0.74 rows=1 width=8) (never executed) Index Cond: (source = fe.source) -> Hash (cost=3.35..3.35 rows=35 width=8) (actual time=0.083..0.083 rows=35 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 2kB -> Seq Scan on metabib_field fe_weight (cost=0.00..3.35 rows=35 width=8) (actual time=0.020..0.059 rows=35 loops=1) -> Index Scan using metabib_sorter_source_idx on record_sorter pubdate_t (cost=0.00..1.61 rows=2 width=23) (never executed) Index Cond: (m.source = source) Filter: (attr = 'pubdate'::text) Total runtime: 58.486 ms (36 rows)