[OPEN-ILS-DEV] request help optimizing very slow select query for ebooks

[Rogan Hamby]

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 <mbelvadi at upei.ca> 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
> mbelvadi at upei.ca  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
>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://libmail.georgialibraries.org/pipermail/open-ils-dev/attachments/20190710/19ee8dc2/attachment-0001.html>