Bug 41365 - objects.search_rs puts too much pressure on the DB
Summary: objects.search_rs puts too much pressure on the DB
Status: RESOLVED WORKSFORME
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low major
Assignee: Tomás Cohen Arazi (tcohen)
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2025-12-03 16:19 UTC by Tomás Cohen Arazi (tcohen)
Modified: 2025-12-03 18:35 UTC (History)
3 users (show)

See Also:
GIT URL:
Initiative type: ---
Sponsorship status: ---
Comma delimited list of Sponsors:
Crowdfunding goal: 0
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Tomás Cohen Arazi (tcohen) 2025-12-03 16:19:10 UTC
Found in the wild while analyzing a performance issue on a very big partner site.

```shell
perl -MKoha::Patrons -MTime::HiRes=time -e '$q={"-or"=>[map{{("me.$_"=>{like=>"%nick%"})}}qw(surname firstname cardnumber)]};$t=time;$rs=Koha::Patrons->search($q,{prefetch=>"branchcode"});$c=$rs->count;$p=time-$t;$t=time;$rs=Koha::Patrons->search($q);$c=$rs->count;$n=time-$t;printf"Pagination COUNT with prefetch: %.4fs\nPagination COUNT without: %.4fs\nSlowdown: %.1fx\n",$p,$n,$p/$n'
Pagination COUNT with prefetch: 2.7349s
Pagination COUNT without: 0.8491s
Slowdown: 3.2x
```

The key here is that 'prefetch' not only introduces a JOIN, but also makes DBIC inflate the DB response into structures, so it takes a lot of resources, adding to the API response latency.

This oneliner highlights a specific design issue. I'll now explain how it impacts API searches:

When we invoke `objects.search_rs`, we usually do it with pagination parameters and filters (on the UI, for example in patron search). This means the actual search shouldn't have a performance impact because the resultset is tiny! But there's a catch: before adding pagination and filters, we perform a `search->count()` on the resultset! To populate our pagination-related headers (x-total-count, etc)! So we are effectively performing a search, with many joins and hashref inflation on the full resultset, only to get a count!

I'll provide a trivial patch for this.
Comment 1 Tomás Cohen Arazi (tcohen) 2025-12-03 18:35:09 UTC
I'm sure I was wrong. Because the two queries before the actual search are not really taking so long. They don't have the joins set, and DBIC is smart enough to not use ->columns internally when asked to ->count. This was my main hypothesis.