doing SIP2 lookup of a patron who has a metarecord hold can fail

Bug #1321017 reported by Galen Charlton on 2014-05-19
16
This bug affects 3 people
Affects Status Importance Assigned to Milestone
Evergreen
High
Unassigned
2.6
High
Unassigned

Bug Description

When doing a SIP2 patron information lookup of a patron that has an active metarecord hold, the request can time out. This is because the SIP code calls open-ils.search.biblio.metarecord.mods_slim.retrieve to retrieve information about the titles associated with that hold request. Unfortunately, that method ultimately invokes open-ils.storage.ordered.metabib.metarecord.records, which in turn generates an SQL query that can look this:

SELECT record,
      item_type,
      item_form,
      quality,
      FIRST(COALESCE(LTRIM(SUBSTR( value, COALESCE(SUBSTRING(ind2 FROM E'\\d+'),'0')::INT + 1 )),'zzzzzzzz')) AS title
  FROM (
      SELECT rd.record,
          rd.item_type,
          rd.item_form,
          br.quality,
          fr.tag,
          fr.subfield,
          fr.value,
          fr.ind2
        FROM metabib.metarecord_source_map sm
          JOIN biblio.record_entry br ON (sm.source = br.id)
          JOIN metabib.full_rec fr ON (fr.record = br.id)
          JOIN metabib.rec_descriptor rd ON (rd.record = br.id)
        WHERE sm.metarecord = '702263'
          AND (( EXISTS (
                  SELECT 1
                    FROM asset.copy cp,
                      asset.call_number cn,
                      actor.org_unit_descendants(1) d
                    WHERE cn.record = br.id
                      AND cn.deleted = FALSE
                      AND cp.deleted = FALSE
                      AND cp.circ_lib = d.id
                      AND cn.id = cp.call_number
                    LIMIT 1
              ) OR NOT EXISTS (
                  SELECT 1
                    FROM asset.copy cp,
                      asset.call_number cn
                    WHERE cn.record = br.id
                      AND cn.deleted = FALSE
                      AND cp.deleted = FALSE
                      AND cn.id = cp.call_number
                    LIMIT 1
              ))
              OR EXISTS ((
                  SELECT 1
                    FROM config.bib_source src
                    WHERE src.id = br.source
                          AND src.transcendant IS TRUE))
          )
        OFFSET 0
      ) AS x
      WHERE tag = '245'
        AND subfield = 'a'
      GROUP BY record, item_type, item_form, quality
      ORDER BY
        CASE
            WHEN item_type IS NULL -- default
                THEN 0
            WHEN item_type = '' -- default
                THEN 0
            WHEN item_type IN ('a','t') -- books
                THEN 1
            WHEN item_type = 'g' -- movies
                THEN 2
            WHEN item_type IN ('i','j') -- sound recordings
                THEN 3
            WHEN item_type = 'm' -- software
                THEN 4
            WHEN item_type = 'k' -- images
                THEN 5
            WHEN item_type IN ('e','f') -- maps
                THEN 6
            WHEN item_type IN ('o','p') -- mixed
                THEN 7
            WHEN item_type IN ('c','d') -- music
                THEN 8
            WHEN item_type = 'r' -- 3d
                THEN 9
        END,
        title ASC,
        quality DESC;

This, in turn, can have an atrocious query plan:

 Sort (cost=1807422585.83..1807422585.84 rows=4 width=110)
   Sort Key: (CASE WHEN (x.item_type IS NULL) THEN 0 WHEN (x.item_type = ''::text) THEN 0 WHEN (x.item_type = ANY ('{a,t}'::text[])) THEN 1 WHEN (x.item_type = 'g'::text) THEN 2 WHEN (x.item_type = ANY ('{i,j}'::text[])) THEN 3 WHEN (x.item_type = 'm'::text) THEN 4 WHEN (x.item_type = 'k'::text) THEN 5 WHEN (x.item_type = ANY ('{e,f}'::text[])) THEN 6 WHEN (x.item_type = ANY ('{o,p}'::text[])) THEN 7 WHEN (x.item_type = ANY ('{c,d}'::text[])) THEN 8 WHEN (x.item_type = 'r'::text) THEN 9 ELSE NULL::integer END), (first(COALESCE(ltrim(substr(x.value, ((COALESCE("substring"(x.ind2, '\d+'::text), '0'::text))::integer + 1))), 'zzzzzzzz'::text))), x.quality
   -> HashAggregate (cost=1807422585.65..1807422585.79 rows=4 width=110)
         -> Subquery Scan on x (cost=28127.66..1807422584.55 rows=4 width=110)
               Filter: ((x.tag = '245'::bpchar) AND (x.subfield = 'a'::text))
               -> Limit (cost=28127.66..1807422580.98 rows=238 width=66)
                     -> Nested Loop (cost=28127.66..1807422580.98 rows=238 width=66)
                           Join Filter: (sm.source = real_full_rec.record)
                           -> Merge Join (cost=28127.66..1807422320.05 rows=3 width=60)
                                 Merge Cond: (v.source = sm.source)
                                 -> GroupAggregate (cost=28127.66..1807326814.94 rows=1618446 width=72)
                                       -> Nested Loop (cost=28127.66..1781176357.63 rows=3482951268 width=72)
                                             Join Filter: (m.id = ANY (v.vlist))
                                             -> Index Scan using record_attr_vector_list_pkey on record_attr_vector_list v (cost=0.00..128338.86 rows=1618446 width=91)
                                             -> Materialize (cost=28127.66..29228.11 rows=44018 width=72)
                                                   -> Subquery Scan on m (cost=28127.66..29008.02 rows=44018 width=72)
                                                         -> HashAggregate (cost=28127.66..28567.84 rows=44018 width=72)
                                                               -> Append (cost=26464.03..27797.53 rows=44018 width=72)
                                                                     -> HashAggregate (cost=26464.03..26901.11 rows=43708 width=17)
                                                                           -> Append (cost=0.00..26136.22 rows=43708 width=17)
                                                                                 -> Seq Scan on uncontrolled_record_attr_value (cost=0.00..25679.98 rows=43398 width=17)
                                                                                 -> Subquery Scan on "*SELECT* 2" (cost=1.52..22.26 rows=310 width=16)
                                                                                       -> Hash Join (cost=1.52..19.16 rows=310 width=16)
                                                                                             Hash Cond: (c.ctype = d.name)
                                                                                             -> Seq Scan on coded_value_map c (cost=0.00..12.21 rows=621 width=16)
                                                                                             -> Hash (cost=1.32..1.32 rows=16 width=7)
                                                                                                   -> Seq Scan on record_attr_definition d (cost=0.00..1.32 rows=16 width=7)
                                                                                                         Filter: (NOT composite)
                                                                     -> Subquery Scan on "*SELECT* 2" (cost=1.52..22.26 rows=310 width=16)
                                                                           -> Hash Join (cost=1.52..19.16 rows=310 width=16)
                                                                                 Hash Cond: (c.ctype = d.name)
                                                                                 -> Seq Scan on coded_value_map c (cost=0.00..12.21 rows=621 width=16)
                                                                                 -> Hash (cost=1.32..1.32 rows=16 width=7)
                                                                                       -> Seq Scan on record_attr_definition d (cost=0.00..1.32 rows=16 width=7)
                                                                                             Filter: composite
                                 -> Materialize (cost=0.00..75274.49 rows=4 width=20)
                                       -> Nested Loop (cost=0.00..75274.48 rows=4 width=20)
                                             -> Index Scan using metabib_metarecord_source_map_source_record_idx on metarecord_source_map sm (cost=0.00..74570.66 rows=5 width=8)
                                                   Filter: (metarecord = 702263::bigint)
                                             -> Index Scan using record_entry_pkey on record_entry br (cost=0.00..140.75 rows=1 width=12)
                                                   Index Cond: (id = sm.source)
                                                   Filter: ((SubPlan 1) OR (NOT (SubPlan 2)) OR (alternatives: SubPlan 3 or hashed SubPlan 4))
                                                   SubPlan 1
                                                     -> Limit (cost=0.25..122.95 rows=1 width=0)
                                                           -> Nested Loop (cost=0.25..122.95 rows=1 width=0)
                                                                 Join Filter: (cp.circ_lib = d.id)
                                                                 -> Function Scan on org_unit_descendants d (cost=0.25..0.26 rows=1 width=4)
                                                                 -> Nested Loop (cost=0.00..122.56 rows=10 width=4)
                                                                       -> Index Scan using asset_call_number_record_idx on call_number cn (cost=0.00..21.58 rows=11 width=8)
                                                                             Index Cond: (record = br.id)
                                                                             Filter: (NOT deleted)
                                                                       -> Append (cost=0.00..9.15 rows=3 width=12)
                                                                             -> Index Scan using cp_cn_idx on copy cp (cost=0.00..5.61 rows=2 width=12)
                                                                                   Index Cond: (call_number = cn.id)
                                                                                   Filter: (NOT deleted)
                                                                             -> Index Scan using unit_cn_idx on unit cp (cost=0.00..3.54 rows=1 width=12)
                                                                                   Index Cond: (call_number = cn.id)
                                                                                   Filter: (NOT deleted)
                                                   SubPlan 2
                                                     -> Limit (cost=0.00..12.26 rows=1 width=0)
                                                           -> Nested Loop (cost=0.00..122.56 rows=10 width=0)
                                                                 -> Index Scan using asset_call_number_record_idx on call_number cn (cost=0.00..21.58 rows=11 width=8)
                                                                       Index Cond: (record = br.id)
                                                                       Filter: (NOT deleted)
                                                                 -> Append (cost=0.00..9.15 rows=3 width=8)
                                                                       -> Index Scan using cp_cn_idx on copy cp (cost=0.00..5.61 rows=2 width=8)
                                                                             Index Cond: (call_number = cn.id)
                                                                             Filter: (NOT deleted)
                                                                       -> Index Scan using unit_cn_idx on unit cp (cost=0.00..3.54 rows=1 width=8)
                                                                             Index Cond: (call_number = cn.id)
                                                                             Filter: (NOT deleted)
                                                   SubPlan 3
                                                     -> Seq Scan on bib_source src (cost=0.00..1.07 rows=1 width=0)
                                                           Filter: ((transcendant IS TRUE) AND (id = br.source))
                                                   SubPlan 4
                                                     -> Seq Scan on bib_source src (cost=0.00..1.06 rows=4 width=4)
                                                           Filter: (transcendant IS TRUE)
                           -> Index Scan using metabib_full_rec_record_idx on real_full_rec (cost=0.00..58.83 rows=2204 width=30)
                                 Index Cond: (record = br.id)
(79 rows)

All this to (in the case of the SIP2 request) just grab a title.

Evergreen 2.6

Galen Charlton (gmc) wrote :

It looks like that the patch for bug 1296937 will address this problem when the SIP client is only requesting the counts of available and unavailable hold requests. However, it's not a complete solution for this bug, since the SIP client could also specifically request detailed information about held titles, in which case this bug will be triggered.

Changed in evergreen:
assignee: nobody → Mike Rylander (mrylander)
importance: Undecided → High
Mike Rylander (mrylander) wrote :

Here's a fix for the inner-most query generating sub. From the commit message:

    Previously, in some cases (often needlessly) we oredered constituent
    records within a metarecord by a combination of type/form/blvl. This
    is not only of little use, but also expensive. Instead, order them
    by the bib's calculated quality, which takes type/form/blvl into account
    already.

    Also, use the new metabib.record_sorter to find the title tie-breaker
    instead of using the view-of-a-view-of-a-view mrd compatability shim.

    The net result is several orders of magnitude speed increase for constiuent
    record retrieval.

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/miker/lp1321017-fast-MR-constituent-retrieval

tags: added: pullrequest
Changed in evergreen:
assignee: Mike Rylander (mrylander) → nobody
milestone: none → 2.6.1
Galen Charlton (gmc) wrote :

One immediate comment: the $org and $depth parameters still need to be fetched from @_. Minor, of course.

The patch also removes the format filter, and callers would have to be adjusted. I did some tracing back, and it looks like the only place that ultimately expects to pass a format filter (or an OU or depth filter) to ordered_records_from_metarecord is JSPAC, which is on the chopping block anyway.

So opening up the question: do we anticipate ever wanting to have any of the following methods get passed anything other than just a metarecord ID?

open-ils.search.biblio.metarecord_to_records*
open-ils.search.biblio.metarecord.mods_slim.batch.retrieve*
open-ils.search.biblio.metarecord.mods_slim.retrieve*

Mike Rylander (mrylander) wrote :

Thanks, Galen.

The trimming of the parameter list was (hopefully, obviously ;) ) unintentional. I'll push a squash-able commit bringing those back.

As to whether we might want to filter (or, as you put it, pass anything but an MR id) ... probably not with the API as defined today. type/form/blvl are superseded by record attributes, so I think the best route forward is actually to teach QP how to do what we want. OTTOMH, something akin to the container() construct makes sense to me, and would be very simple to implement. Then we don't have to worry about how to add more, or more complicated, filtering options to constituent record retrieval in the future.

But, that's a tomorrow problem, IMO.

I'll leave the org/depth calculation stuff in place for the time being, in case we want to teach the middle layer method to be smarter.

Galen Charlton (gmc) wrote :

This does the trick for me. I've squashed your two patches together (and a minor follow-up by me to remove some unused variables) and pushed the signed-off patch the the user/gmcharlt/lp1321017-fast-MR-constituent-retrieval-signed-off branch in the working/Evergreen repository:

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/gmcharlt/lp1321017-fast-MR-constituent-retrieval-signed-off

Dan Wells (dbw2) wrote :

Pushed to master and rel_2_6. Thank you, Mike and Galen.

Changed in evergreen:
milestone: 2.6.1 → 2.next
status: New → Fix Committed
Ben Shum (bshum) on 2014-07-23
Changed in evergreen:
milestone: 2.next → 2.7.0-alpha1
Changed in evergreen:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers