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
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.