Comment 3 for bug 788379

Just for fun, rather than retrieving the set of bib records that matches the text part of the query, and then iterating over those records to filter out those that don't match other conditions (owning library / circ library / deleted / copy status), I built those conditions into a single query:

EXPLAIN ANALYZE
  SELECT mkfe.record
    FROM metabib.keyword_field_entry mkfe
      INNER JOIN asset.call_number acn ON acn.record = mkfe.source
      INNER JOIN asset.copy ac ON ac.call_number = acn.id
  WHERE mkfe.index_vector @@ to_tsquery('europe')
    AND acn.owning_lib = 103
    AND ac.circ_lib = 103
    AND ac.deleted IS FALSE
   AND ac.status = 0;

Needed to create an index on asset.call_number(owning_lib) to avoid a sequential scan, but the results came back in 3 seconds. Not bad.

Ordering by title slows things down considerably:

EXPLAIN ANALYZE SELECT mkfe.source
  FROM metabib.keyword_field_entry mkfe
    INNER JOIN asset.call_number acn ON acn.record = mkfe.source
    INNER JOIN asset.copy ac ON ac.call_number = acn.id
    INNER JOIN metabib.full_rec mfr ON mfr.record = acn.record
  WHERE mkfe.index_vector @@ to_tsquery('europe')
    AND acn.owning_lib = 103
    AND ac.circ_lib = 103
    AND ac.deleted IS FALSE
    AND ac.status = 0
    AND mfr.tag = 'tnf'
  ORDER BY ts_rank_cd(mfr.index_vector, to_tsquery('europe'));

... that comes in at 18 seconds. Of course, bear in mind that our system is still horribly hobbled by its use of default settings...