Hi Melissa, Quick question, are you looking for all ebooks or a certain subset indicate by the 'full text via%' matching? It's not clear to me if that's how you're identifying they are ebooks at all versus a subset of them. If you're looking for all of them you can use the fact that the fixed field data will map to entries in the config.coded_values_map table to identify them. So, for example if the ebook search format is id 614 in that table then vlist entries in metabib.record_attr_vector_list with a 614 as one of the values will be an ebook. That will certainly search faster than multiple joins against metabib.real_full_rec.
On Wed, Jul 10, 2019 at 2:23 PM Melissa Belvadi <[email protected]> wrote: > We're on 3.0.3, with under 2 million total biblio recs, under 1 million of > those undeleted. > The following query is taking over 10 hours to run at the command line or > through pgadmin or dbeaver. For a system our size, that seems far too slow. > We've checked other aspects of our server, e.g. other processes running on > it and index integrity, and can't account for the long execution time. > > I'd appreciate any advice on how to either optimize this search or > restructure it as we'll need to run this regularly. As you can guess, > there's another query that comes after this that uses the temp table, but > that responds really fast once the temp table is finally made. > The point of this query is to find all of the ebooks added to the system > over a given time period, and pull together a bunch of data about those > books. > If there's some entirely different way to go about this, eg using SRU or > some other tool, I'm totally open to that too. > > create temp table temp_newebooks (create_date, call_number, author, > publisher, pubdate, elocation, title, id, isbn, contentnote, crecord) as > > WITH myconstants as (select to_date('20190101','YYYYMMDD') as startdate, > to_date('20190701','YYYYMMDD') as enddate, cast('20160101' as varchar) as > pubstart) > > select distinct bre.create_date, mfr.value, r.author, r.publisher, > r.pubdate, mfr2.value, tfe.value, r.id, r.isbn, mfr3.value, mfr3.record > > from biblio.record_entry bre > > join metabib.real_full_rec mfr on (mfr.record=bre.id) > > left join metabib.real_full_rec mfr3 on (mfr3.record=bre.id) and mfr3.tag > = '520' and mfr3.subfield = 'a' > > join metabib.real_full_rec mfr2 on (mfr2.record=bre.id) > > join reporter.materialized_simple_record r on (r.id = bre.id) > > join metabib.title_field_entry tfe on (tfe.source=bre.id) > > where > > not bre.deleted > > and substring(r.pubdate from 1 for 4) >= (select pubstart from myconstants) > > and tfe.field = 5 > > and (mfr.tag like '050' or mfr.tag like '090') and mfr.subfield like 'a' > > and mfr2.tag like '856' and mfr2.subfield like 'y' > > and mfr2.value ilike 'full text via%' > > and bre.create_date > (select startdate from myconstants) and > bre.create_date < (select enddate from myconstants); > > > Thank you! > > Melissa Belvadi > Collections Librarian > University of Prince Edward Island > [email protected] 902-566-0581 > my public calendar > <http://www.google.com/calendar/embed?src=mbelvadi%40upei.ca&ctz=America/Halifax&mode=week> > Make an appointment <https://mbelvadi.youcanbook.me/> via YouCanBookMe > > > >
