UPDATE 7/29/22: this is still happening in the 3.9 BOOPAC despite 9.6 being the minimum PG for 3.9.
Original report in full below.
===========================================
The TPAC shelf browser (/eg/opac/record/XXX?expand=cnbrowse#cnbrowse) can be quite slow to return results. I've traced this down to the database queries used to establish the pivot, from _label_sortkey_from_label() in Open-ILS/src/perlmods/lib/OpenILS/Application/SuperCat.pm. For example:
SELECT "acn".create_date, "acn".creator, "acn".deleted, "acn".edit_date, "acn".editor, "acn".id, "acn".label, "acn".owning_lib, "acn".record, "acn".label_sortkey, "acn".label_class, "acn".prefix, "acn".suffix FROM asset.call_number AS "acn" WHERE oils_text_as_bytea("acn".label ) >= oils_text_as_bytea( '289.7 Hos' ) AND "acn".owning_lib IN ('1', '194', '194', '195', '195', '197', '197', '300', '300', '199', '199', '202', '202', '205', '205', '213', '213', '214', '214', '229', '229', '230', '230', '234', '234', '236', '236', '232', '232', '238', '238', '239', '239', '106', '106', '107', '107', '101', '101', '102', '102', '137', '137', '143', '143', '160', '160', '166', '166', '139', '139', '142', '142', '148', '148', '151', '151', '154', '154', '157', '157', '172', '172', '169', '169', '175', '175', '163', '163', '178', '178', '181', '181', '296', '296', '297', '297', '189', '189', '190', '190', '210', '210', '211', '211', '217', '217', '219', '219', '224', '224', '282', '282', '222', '222', '227', '227', '186', '186', '187', '187', '301', '301', '302', '302', '305', '305', '306', '306', '303', '303', '304', '304', '307', '307', '308', '308', '309', '309', '310', '310', '331', '331', '342', '342', '332', '332', '343', '343', '333', '333', '334', '334', '344', '344', '335', '335', '336', '336', '337', '337', '338', '338', '339', '339', '340', '340', '341', '341', '311', '311', '324', '324', '323', '323', '328', '328', '326', '326', '313', '313', '314', '314', '315', '315', '316', '316', '330', '330', '327', '327', '325', '325', '317', '317', '312', '312', '320', '320', '318', '318', '319', '319', '329', '329', '322', '322', '321', '321') AND "acn".deleted = 'f' ORDER BY oils_text_as_bytea(label), id LIMIT 1;
Under PostgreSQL 9.2 and 9.3, this generates the following plan that includes a sequential scan on asset.call_number:
Limit (cost=45845.28..45845.28 rows=1 width=92)
-> Sort (cost=45845.28..46057.67 rows=84957 width=92)
Sort Key: ((regexp_replace(upper(label), '\\'::text, '\\\\'::text, 'g'::text))::bytea), id
-> Seq Scan on call_number acn (cost=0.00..45420.50 rows=84957 width=92)
Filter: ((NOT deleted) AND ((regexp_replace(upper(label), '\\'::text, '\\\\'::text, 'g'::text))::bytea >= '\x3238392e3720484f53'::bytea) AND (owning_lib = ANY ('{1,194,194,195,195,197,197,300,300,199,199,202,202,205,205,213,213,214,214,229,229,230,230,234,234,236,236,232,232,238,238,239,239,106,106,107,107,101,101,102,102,137,137,143,143,160,160,166,166,139,139,142,142,148,148,151,151,154,154,157,157,172,172,169,169,175,175,163,163,178,178,181,181,296,296,297,297,189,189,190,190,210,210,211,211,217,217,219,219,224,224,282,282,222,222,227,227,186,186,187,187,301,301,302,302,305,305,306,306,303,303,304,304,307,307,308,308,309,309,310,310,331,331,342,342,332,332,343,343,333,333,334,334,344,344,335,335,336,336,337,337,338,338,339,339,340,340,341,341,311,311,324,324,323,323,328,328,326,326,313,313,314,314,315,315,316,316,330,330,327,327,325,325,317,317,312,312,320,320,318,318,319,319,329,329,322,322,321,321}'::integer[])))
Under PostgreSQL 9.4, the query plan is much better and uses an index:
Limit (cost=0.16..1.26 rows=1 width=133)
-> Index Scan using asset_call_number_upper_label_id_owning_lib_idx on call_number acn (cost=0.16..87.32 rows=79 width=133)
Index Cond: ((regexp_replace(upper(label), '\\'::text, '\\\\'::text, 'g'::text))::bytea >= '\x3238392e3720484f53'::bytea)
Filter: ((NOT deleted) AND (owning_lib = ANY ('{1,194,194,195,195,197,197,300,300,199,199,202,202,205,205,213,213,214,214,229,229,230,230,234,234,236,236,232,232,238,238,239,239,106,106,107,107,101,101,102,102,137,137,143,143,160,160,166,166,139,139,142,142,148,148,151,151,154,154,157,157,172,172,169,169,175,175,163,163,178,178,181,181,296,296,297,297,189,189,190,190,210,210,211,211,217,217,219,219,224,224,282,282,222,222,227,227,186,186,187,187,301,301,302,302,305,305,306,306,303,303,304,304,307,307,308,308,309,309,310,310,331,331,342,342,332,332,343,343,333,333,334,334,344,344,335,335,336,336,337,337,338,338,339,339,340,340,341,341,311,311,324,324,323,323,328,328,326,326,313,313,314,314,315,315,316,316,330,330,327,327,325,325,317,317,312,312,320,320,318,318,319,319,329,329,322,322,321,321}'::integer[])))
Since it will be a while before we can count on Pg 9.4+ being used, it would be nice if the query or the indexing were tweaked so that it runs well on 9.2 and 9.3.
Evergreen master
PostgreSQL 9.2 and 9.3
This still seems slow in 3.9 BOOPAC, even though minimum PG is now 9.6 & I'm on a test system with a small number of records.
Curious what large data sites are seeing here.