API tools faq
paste
Advertisement
Guest User

Fleshing holds in the web client

a guest
May 24th, 2018
55
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.98 KB | None | 0 0
raw download clone embed print report
  1. SELECT "ahopl".capture_time, "ahopl".current_copy, "ahopl".email_notify, "ahopl".expire_time, "ahopl".fulfillment_lib, "ahopl".fulfillment_staff, "ahopl".fulfillment_time, "ahopl".hold_type, "ahopl".holdable_formats, "ahopl".id, "ahopl".phone_notify, "ahopl".sms_notify, "ahopl".sms_carrier, "ahopl".pickup_lib, "ahopl".prev_check_time, "ahopl".request_lib, "ahopl".request_time, "ahopl".requestor, "ahopl".selection_depth, "ahopl".selection_ou, "ahopl".target, "ahopl".usr, "ahopl".cancel_time, "ahopl".frozen, "ahopl".thaw_date, "ahopl".shelf_time, "ahopl".cancel_cause, "ahopl".cancel_note, "ahopl".cut_in_line, "ahopl".mint_condition, "ahopl".shelf_expire_time, "ahopl".current_shelf_lib, "ahopl".copy_location_order_position, "ahopl".usr_first_given_name, "ahopl".usr_second_given_name, "ahopl".usr_family_name, "ahopl".usr_prefix, "ahopl".usr_suffix, "ahopl".usr_alias_or_first_given_name, "ahopl".usr_display_name, "ahopl".usr_alias_or_display_name, "ahopl".usr_alias, "ahopl".call_number_label, "ahopl".issuance_label, "ahopl".is_staff_hold, "ahopl".potential_copies, "ahopl".behind_desk FROM (
  2. SELECT
  3. ahr.*,
  4. COALESCE(acplo.position, acpl_ordered.fallback_position) AS
  5. copy_location_order_position,
  6. CASE WHEN au.alias IS NOT NULL THEN
  7. au.alias
  8. ELSE
  9. au.first_given_name
  10. END AS usr_alias_or_first_given_name,
  11. au.first_given_name AS usr_first_given_name,
  12. au.second_given_name AS usr_second_given_name,
  13. au.family_name AS usr_family_name,
  14. au.prefix AS usr_prefix,
  15. au.suffix AS usr_suffix,
  16. au.alias AS usr_alias,
  17. CASE WHEN au.alias IS NOT NULL THEN
  18. au.alias
  19. ELSE
  20. REGEXP_REPLACE(ARRAY_TO_STRING(ARRAY[
  21. COALESCE(au.family_name, ''),
  22. COALESCE(au.suffix, ''),
  23. ', ',
  24. COALESCE(au.prefix, ''),
  25. COALESCE(au.first_given_name, ''),
  26. COALESCE(au.second_given_name, '')
  27. ], ' '), E'\\s+,', ',')
  28. END AS usr_alias_or_display_name,
  29. REGEXP_REPLACE(ARRAY_TO_STRING(ARRAY[
  30. COALESCE(au.family_name, ''),
  31. COALESCE(au.suffix, ''),
  32. ', ',
  33. COALESCE(au.prefix, ''),
  34. COALESCE(au.first_given_name, ''),
  35. COALESCE(au.second_given_name, '')
  36. ], ' '), E'\\s+,', ',') AS usr_display_name,
  37. TRIM(acnp.label || ' ' || acn.label || ' ' || acns.label)
  38. AS call_number_label,
  39. siss.label AS issuance_label,
  40. (ahr.usr <> ahr.requestor) AS is_staff_hold,
  41. ahcm_1.copy_count AS potential_copies
  42. FROM action.hold_request ahr
  43. JOIN asset.copy acp ON (acp.id = ahr.current_copy)
  44. JOIN asset.call_number acn ON (acp.call_number = acn.id)
  45. JOIN asset.call_number_prefix acnp ON (acn.prefix = acnp.id)
  46. JOIN asset.call_number_suffix acns ON (acn.suffix = acns.id)
  47. JOIN actor.usr au ON (au.id = ahr.usr)
  48. JOIN (
  49. SELECT *, (ROW_NUMBER() OVER (ORDER BY name) + 1000000) AS fallback_position
  50. FROM asset.copy_location
  51. ) acpl_ordered ON (acpl_ordered.id = acp.location)
  52. LEFT JOIN actor.usr_standing_penalty ausp
  53. ON (ahr.usr = ausp.usr AND (ausp.stop_date IS NULL OR ausp.stop_date > NOW()))
  54. LEFT JOIN config.standing_penalty csp
  55. ON (
  56. csp.id = ausp.standing_penalty AND
  57. csp.block_list LIKE '%CAPTURE%' AND (
  58. (csp.org_depth IS NULL AND ahr.pickup_lib = ausp.org_unit) OR
  59. (csp.org_depth IS NOT NULL AND ahr.pickup_lib IN (
  60. SELECT id FROM actor.org_unit_descendants(ausp.org_unit, csp.org_depth))
  61. )
  62. )
  63. )
  64. JOIN (
  65. SELECT COUNT(target_copy) AS copy_count, hold
  66. FROM action.hold_copy_map
  67. GROUP BY 2
  68. ) ahcm_1 ON (ahcm_1.hold = ahr.id)
  69. LEFT JOIN serial.issuance siss
  70. ON (ahr.hold_type = 'I' AND siss.id = ahr.target)
  71. LEFT JOIN asset.copy_location_order acplo
  72. ON (acp.location = acplo.location AND
  73. acp.circ_lib = acplo.org)
  74. WHERE
  75. ahr.capture_time IS NULL AND
  76. ahr.cancel_time IS NULL AND
  77. csp.id IS NULL AND
  78. (ahr.expire_time is NULL OR ahr.expire_time > NOW()) AND
  79. acp.status IN (0,7)
  80. ) AS "ahopl" WHERE "ahopl".id = '4489817';
  81.  
  82.  
  83. Explain Analyze:
  84.  
  85. QUERY PLAN
  86. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  87. Nested Loop Anti Join (cost=954.24..1327.06 rows=2 width=424) (actual time=11.018..12.015 rows=1 loops=1)
  88. Join Filter: (((csp.org_depth IS NULL) AND (ahr.pickup_lib = ausp.org_unit)) OR ((csp.org_depth IS NOT NULL) AND (SubPlan 1)))
  89. -> Nested Loop Left Join (cost=954.10..1321.76 rows=2 width=432) (actual time=10.977..11.974 rows=1 loops=1)
  90. -> Nested Loop Left Join (cost=953.68..1309.78 rows=1 width=424) (actual time=10.968..11.965 rows=1 loops=1)
  91. -> Nested Loop (cost=953.53..1309.60 rows=1 width=428) (actual time=10.966..11.963 rows=1 loops=1)
  92. -> Hash Join (cost=953.41..1309.45 rows=1 width=400) (actual time=10.942..11.938 rows=1 loops=1)
  93. Hash Cond: (copy_location.id = acp.location)
  94. -> WindowAgg (cost=909.42..1120.40 rows=10549 width=36) (actual time=7.939..11.264 rows=10549 loops=1)
  95. -> Sort (cost=909.42..935.79 rows=10549 width=36) (actual time=7.925..8.366 rows=10549 loops=1)
  96. Sort Key: copy_location.name
  97. Sort Method: quicksort Memory: 1398kB
  98. -> Seq Scan on copy_location (cost=0.00..204.49 rows=10549 width=36) (actual time=0.004..1.625 rows=10549 loops=1)
  99. -> Hash (cost=43.98..43.98 rows=1 width=392) (actual time=0.079..0.079 rows=1 loops=1)
  100. Buckets: 1024 Batches: 1 Memory Usage: 1kB
  101. -> Nested Loop (cost=2.28..43.98 rows=1 width=392) (actual time=0.072..0.076 rows=1 loops=1)
  102. -> Nested Loop (cost=2.15..43.81 rows=1 width=364) (actual time=0.069..0.072 rows=1 loops=1)
  103. -> Nested Loop (cost=1.72..43.23 rows=1 width=353) (actual time=0.062..0.065 rows=1 loops=1)
  104. -> Nested Loop (cost=1.29..34.77 rows=1 width=298) (actual time=0.053..0.056 rows=1 loops=1)
  105. -> Nested Loop (cost=0.86..26.11 rows=1 width=290) (actual time=0.035..0.038 rows=1 loops=1)
  106. -> Nested Loop Left Join (cost=0.43..9.47 rows=1 width=274) (actual time=0.023..0.023 rows=1 loops=1)
  107. Join Filter: ((ahr.hold_type = 'I'::text) AND (siss.id = ahr.target))
  108. Rows Removed by Join Filter: 10
  109. -> Index Scan using hold_request_pkey on hold_request ahr (cost=0.43..8.45 rows=1 width=242) (actual time=0.015..0.015 rows=1 loops=1)
  110. Index Cond: (id = 4489817)
  111. Filter: ((capture_time IS NULL) AND (cancel_time IS NULL) AND ((expire_time IS NULL) OR (expire_time > now())))
  112. -> Seq Scan on issuance siss (cost=0.00..1.00 rows=1 width=36) (actual time=0.004..0.005 rows=10 loops=1)
  113. -> Append (cost=0.43..16.62 rows=2 width=24) (actual time=0.011..0.014 rows=1 loops=1)
  114. -> Index Scan using copy_pkey on copy acp (cost=0.43..8.46 rows=1 width=24) (actual time=0.010..0.011 rows=1 loops=1)
  115. Index Cond: (id = ahr.current_copy)
  116. Filter: (status = ANY ('{0,7}'::integer[]))
  117. -> Index Scan using unit_pkey on unit acp_1 (cost=0.14..8.17 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=1)
  118. Index Cond: (id = ahr.current_copy)
  119. Filter: (status = ANY ('{0,7}'::integer[]))
  120. -> GroupAggregate (cost=0.42..8.64 rows=1 width=12) (actual time=0.017..0.017 rows=1 loops=1)
  121. Group Key: hold_copy_map.hold
  122. -> Index Only Scan using copy_once_per_hold on hold_copy_map (cost=0.42..8.58 rows=9 width=12) (actual time=0.009..0.013 rows=29 loops=1)
  123. Index Cond: (hold = 4489817)
  124. Heap Fetches: 0
  125. -> Index Scan using usr_pkey on usr au (cost=0.43..8.45 rows=1 width=59) (actual time=0.007..0.007 rows=1 loops=1)
  126. Index Cond: (id = ahr.usr)
  127. -> Index Scan using call_number_pkey on call_number acn (cost=0.44..0.58 rows=1 width=27) (actual time=0.007..0.007 rows=1 loops=1)
  128. Index Cond: (id = acp.call_number)
  129. -> Index Scan using call_number_prefix_pkey on call_number_prefix acnp (cost=0.13..0.15 rows=1 width=36) (actual time=0.002..0.003 rows=1 loops=1)
  130. Index Cond: (id = acn.prefix)
  131. -> Index Scan using call_number_suffix_pkey on call_number_suffix acns (cost=0.12..0.15 rows=1 width=36) (actual time=0.023..0.024 rows=1 loops=1)
  132. Index Cond: (id = acn.suffix)
  133. -> Index Scan using acplo_once_per_org on copy_location_order acplo (cost=0.14..0.17 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=1)
  134. Index Cond: ((acp.location = location) AND (acp.circ_lib = org))
  135. -> Index Scan using actor_usr_standing_penalty_usr_idx on usr_standing_penalty ausp (cost=0.42..11.97 rows=2 width=12) (actual time=0.008..0.008 rows=0 loops=1)
  136. Index Cond: (ahr.usr = usr)
  137. Filter: ((stop_date IS NULL) OR (stop_date > now()))
  138. -> Index Scan using standing_penalty_pkey on standing_penalty csp (cost=0.14..0.16 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
  139. Index Cond: (id = ausp.standing_penalty)
  140. Filter: (block_list ~~ '%CAPTURE%'::text)
  141. SubPlan 1
  142. -> Function Scan on org_unit_descendants (cost=0.25..3.75 rows=350 width=4) (never executed)
  143. Planning time: 1431.502 ms
  144. Execution time: 12.352 ms
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!