OPAC shelf browser can be very slow

Bug #1452761 reported by Galen Charlton
12
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Evergreen
Confirmed
Medium
Unassigned

Bug Description

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

Galen Charlton (gmc)
Changed in evergreen:
importance: Undecided → Low
tags: added: performance tpac
description: updated
tags: added: opac
removed: tpac
Revision history for this message
Andrea Neiman (aneiman) wrote :

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.

Revision history for this message
Terran McCanna (tmccanna) wrote :

It's still very slow on BooPAC with a large data set (see gapines.org for testing).

Revision history for this message
Andrea Neiman (aneiman) wrote :

Thanks, marking confirmed & editing title / description accordingly

summary: - TPac shelf browser can be very slow
+ OPAC shelf browser can be very slow
Changed in evergreen:
status: New → Confirmed
importance: Low → Medium
description: updated
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.