QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=142069.95..142071.28 rows=533 width=248) (actual time=2771.203..2771.203 rows=1 loops=1) CTE xa741de8_title_xq -> Result (cost=0.00..0.22 rows=1 width=0) (actual time=1.010..1.021 rows=1 loops=1) CTE lang_with -> Seq Scan on coded_value_map (cost=0.00..15.78 rows=1 width=4) (actual time=0.052..0.294 rows=1 loops=1) Filter: ((ctype = 'item_lang'::text) AND (code = 'eng'::text)) -> Sort (cost=142053.95..142055.28 rows=533 width=248) (actual time=2771.201..2771.201 rows=1 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=142000.50..142029.81 rows=533 width=248) (actual time=2771.122..2771.124 rows=1 loops=1) -> Nested Loop (cost=16138.49..141586.09 rows=533 width=248) (actual time=2768.582..2770.534 rows=1 loops=1) -> CTE Scan on lang_with (cost=0.00..0.02 rows=1 width=4) (actual time=0.055..0.301 rows=1 loops=1) -> Nested Loop Left Join (cost=16138.49..141580.74 rows=533 width=244) (actual time=2768.516..2770.221 rows=1 loops=1) -> Nested Loop (cost=16138.49..140707.79 rows=234 width=226) (actual time=2768.393..2770.096 rows=1 loops=1) -> Hash Join (cost=16138.49..140216.23 rows=45 width=234) (actual time=2768.279..2769.981 rows=1 loops=1) Hash Cond: (fe.field = fe_weight.id) -> Hash Join (cost=16136.72..140213.84 rows=45 width=234) (actual time=2768.207..2769.907 rows=1 loops=1) Hash Cond: (fe.source = mrv.source) -> Nested Loop (cost=568.61..124251.69 rows=44982 width=158) (actual time=822.586..822.616 rows=1 loops=1) -> CTE Scan on xa741de8_title_xq (cost=0.00..0.02 rows=1 width=64) (actual time=1.013..1.028 rows=1 loops=1) -> Bitmap Heap Scan on title_field_entry fe (cost=568.61..123689.39 rows=44982 width=126) (actual time=821.565..821.577 rows=1 loops=1) Recheck Cond: (index_vector @@ xa741de8_title_xq.tsq) Filter: (id IS NOT NULL) -> Bitmap Index Scan on metabib_title_field_entry_value_idx (cost=0.00..557.36 rows=44982 width=0) (actual time=820.263..820.263 rows=1 loops=1) Index Cond: (index_vector @@ xa741de8_title_xq.tsq) -> Hash (cost=15509.31..15509.31 rows=4704 width=76) (actual time=1945.465..1945.465 rows=1829416 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 228261kB -> Bitmap Heap Scan on record_attr_vector_list mrv (cost=124.46..15509.31 rows=4704 width=76) (actual time=413.471..1234.128 rows=1829416 loops=1) Recheck Cond: (vlist @@ '610'::query_int) -> Bitmap Index Scan on mrca_vlist_idx (cost=0.00..123.28 rows=4704 width=0) (actual time=401.178..401.178 rows=1829701 loops=1) Index Cond: (vlist @@ '610'::query_int) -> Hash (cost=1.34..1.34 rows=34 width=8) (actual time=0.028..0.028 rows=34 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 2kB -> Seq Scan on metabib_field fe_weight (cost=0.00..1.34 rows=34 width=8) (actual time=0.004..0.013 rows=34 loops=1) -> Index Scan using metabib_metarecord_source_map_source_record_idx on metarecord_source_map m (cost=0.00..10.81 rows=9 width=8) (actual time=0.071..0.072 rows=1 loops=1) Index Cond: (source = fe.source) -> Index Scan using metabib_sorter_source_idx on record_sorter pubdate_t (cost=0.00..3.71 rows=2 width=26) (actual time=0.097..0.099 rows=1 loops=1) Index Cond: (m.source = source) Filter: (attr = 'pubdate'::text) Total runtime: 2793.118 ms (40 rows) CREATE FUNCTION QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=667709.48..667734.48 rows=10000 width=248) (actual time=882.761..882.761 rows=1 loops=1) CTE xa741de8_title_xq -> Result (cost=0.00..0.22 rows=1 width=0) (actual time=0.335..0.336 rows=1 loops=1) CTE lang_with -> Seq Scan on coded_value_map (cost=0.00..15.78 rows=1 width=4) (actual time=0.059..0.208 rows=1 loops=1) Filter: ((ctype = 'item_lang'::text) AND (code = 'eng'::text)) -> Sort (cost=667693.49..668137.83 rows=177737 width=248) (actual time=882.760..882.760 rows=1 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=645220.68..654996.21 rows=177737 width=248) (actual time=882.371..882.733 rows=1 loops=1) -> Nested Loop (cost=570.37..507030.16 rows=177737 width=248) (actual time=881.861..882.019 rows=1 loops=1) -> CTE Scan on lang_with (cost=0.00..0.02 rows=1 width=4) (actual time=0.063..0.212 rows=1 loops=1) -> Nested Loop Left Join (cost=570.37..505252.77 rows=177737 width=244) (actual time=881.793..881.802 rows=1 loops=1) -> Nested Loop (cost=570.37..213740.61 rows=78142 width=226) (actual time=881.767..881.772 rows=1 loops=1) -> Hash Join (cost=570.37..200985.42 rows=14994 width=234) (actual time=881.744..881.749 rows=1 loops=1) Hash Cond: (fe.field = fe_weight.id) -> Nested Loop (cost=568.61..200777.48 rows=14994 width=234) (actual time=881.673..881.677 rows=1 loops=1) -> Nested Loop (cost=568.61..124251.69 rows=44982 width=158) (actual time=881.118..881.121 rows=1 loops=1) -> CTE Scan on xa741de8_title_xq (cost=0.00..0.02 rows=1 width=64) (actual time=0.338..0.340 rows=1 loops=1) -> Bitmap Heap Scan on title_field_entry fe (cost=568.61..123689.39 rows=44982 width=126) (actual time=880.770..880.771 rows=1 loops=1) Recheck Cond: (index_vector @@ xa741de8_title_xq.tsq) Filter: (id IS NOT NULL) -> Bitmap Index Scan on metabib_title_field_entry_value_idx (cost=0.00..557.36 rows=44982 width=0) (actual time=880.757..880.757 rows=1 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..1.69 rows=1 width=76) (actual time=0.548..0.549 rows=1 loops=1) Index Cond: (source = fe.source) Filter: query_int_wrapper(vlist, '(610)'::text) -> Hash (cost=1.34..1.34 rows=34 width=8) (actual time=0.036..0.036 rows=34 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 2kB -> Seq Scan on metabib_field fe_weight (cost=0.00..1.34 rows=34 width=8) (actual time=0.004..0.018 rows=34 loops=1) -> Index Scan using metabib_metarecord_source_map_source_record_idx on metarecord_source_map m (cost=0.00..0.74 rows=9 width=8) (actual time=0.019..0.019 rows=1 loops=1) Index Cond: (source = fe.source) -> Index Scan using metabib_sorter_source_idx on record_sorter pubdate_t (cost=0.00..3.71 rows=2 width=26) (actual time=0.023..0.024 rows=1 loops=1) Index Cond: (m.source = source) Filter: (attr = 'pubdate'::text) Total runtime: 886.562 ms (36 rows)