Pagy::Backend#pagy_get_vars count_args gets ignored

[lasha-meister]

Description

rails - 7.0.8.1
mysql2 - 0.5.5
pagy - 7.0.2

I have observed that using pagy was slow with a table of 40 million rows. I checked the slow query and found that it was using COUNT(*) which is super slow, I looked up the documentation and found that you can pass in count_args. I passed in count_args: :id which was totally ignored. I did a binding at Pagy::Backend#pagy_get_vars and found that

pagy/lib/pagy/backend.rb

Line 22 in 5e65471

vars[:count] ||= (count = collection.count(*count_args)).is_a?(Hash) ? count.size : count

collection.count(:id) was still sending a COUNT(*) query, I made a monkey-patch to fix this issue like this collection.klass.count(*count_args), now the query became COUNT(table.id) which made load times much faster.

[ddnexus]

Hi @lasha-meister,
Not sure about how you get that...

0> Book.all.count
D, [2024-03-19T19:45:51.028977 #6880] DEBUG -- :   Book Count (0.1ms)  SELECT COUNT(*) FROM "books"
=> 11

0> Book.all.count(:id)
D, [2024-03-19T19:45:56.231342 #6880] DEBUG -- :   Book Count (0.2ms)  SELECT COUNT("books"."id") FROM "books"
=> 11

0> p, r = pagy(Book.all)
D, [2024-03-19T19:46:07.115069 #6880] DEBUG -- :   Book Count (0.1ms)  SELECT COUNT(*) FROM "books"
D, [2024-03-19T19:46:07.140286 #6880] DEBUG -- :   Book Load (0.4ms)  SELECT "books".* FROM "books" /* loading for inspect */ LIMIT ? OFFSET ?  [["LIMIT", 11], ["OFFSET", 0]]
=> Error: the evaluation of `to_s` with the arguments `[]` failed with the exception 'SQLite3::SQLException: no such table: books'

0> p, r = pagy(Book.all, count_args: :id)
D, [2024-03-19T19:46:53.505508 #6880] DEBUG -- :   Book Count (0.1ms)  SELECT COUNT("books"."id") FROM "books"
D, [2024-03-19T19:46:53.510209 #6880] DEBUG -- :   Book Load (0.5ms)  SELECT "books".* FROM "books" /* loading for inspect */ LIMIT ? OFFSET ?  [["LIMIT", 11], ["OFFSET", 0]]
=> Error: the evaluation of `to_s` with the arguments `[]` failed with the exception 'SQLite3::SQLException: no such table: books'

[lasha-meister]

Hi @lasha-meister, Not sure about how you get that...

0> Book.all.count
D, [2024-03-19T19:45:51.028977 #6880] DEBUG -- :   Book Count (0.1ms)  SELECT COUNT(*) FROM "books"
=> 11

0> Book.all.count(:id)
D, [2024-03-19T19:45:56.231342 #6880] DEBUG -- :   Book Count (0.2ms)  SELECT COUNT("books"."id") FROM "books"
=> 11

0> p, r = pagy(Book.all)
D, [2024-03-19T19:46:07.115069 #6880] DEBUG -- :   Book Count (0.1ms)  SELECT COUNT(*) FROM "books"
D, [2024-03-19T19:46:07.140286 #6880] DEBUG -- :   Book Load (0.4ms)  SELECT "books".* FROM "books" /* loading for inspect */ LIMIT ? OFFSET ?  [["LIMIT", 11], ["OFFSET", 0]]
=> Error: the evaluation of `to_s` with the arguments `[]` failed with the exception 'SQLite3::SQLException: no such table: books'

0> p, r = pagy(Book.all, count_args: :id)
D, [2024-03-19T19:46:53.505508 #6880] DEBUG -- :   Book Count (0.1ms)  SELECT COUNT("books"."id") FROM "books"
D, [2024-03-19T19:46:53.510209 #6880] DEBUG -- :   Book Load (0.5ms)  SELECT "books".* FROM "books" /* loading for inspect */ LIMIT ? OFFSET ?  [["LIMIT", 11], ["OFFSET", 0]]
=> Error: the evaluation of `to_s` with the arguments `[]` failed with the exception 'SQLite3::SQLException: no such table: books'

That's the thing, with the console I also get correct behavior, but when I do this:

@pagy, @collection = pagy(@ranksack_collection, count_args: :id)

I get this in rack mini profiler, only after my monkey-patch do I get count on the id column :)

[ddnexus]

I cannot help without any provided code support.

I already ignored that fundamental prerequisite and answered to your issue, which only wasted both of our times, because we got exactly zero progress in understanding it.

You guessed a "solution" for your special case, which may be OK for your case, but it's worth nothing in the context of pagy, because we cannot even say whether it's a pagy issue, an AR issue, a DB issue, an issue of some other library used by your code or an issue of your own code.

By just guessing I would say that it's not a pagy issue at all, because either the way we pass the arguments to count is wrong in pagy (i.e. a design problem), OR it's not a problem with pagy since you get the same error without pagy. If it's wrong in pagy, then please point me to some doc that explains why and we will change pagy or its doc.

If you think it's pagy that is causing the problem, please provide some basic code (and please extract the very minimum from your app). Consider that I must be able to run it with a single command and reproduce it without steps to follow, configuration to prepare and especially no guessing.

Thank you for understanding.

[ddnexus]

I converted it in a discussion, because technically it's not a reproducible "bug" so far.

[lasha-meister]

Thank you for your time, I never implied that this is a pagy issue, I from the very beginning was blaming mysql2 gem 🤷 If this issue never ever came up for anyone else then it's legitimate that we close it. Take it as - now you know one more edge case that you /may/ find useful in the future :) thanks again!

[ddnexus]

I never implied that this is a pagy issue, I from the very beginning was blaming mysql2 gem

Oh sure, when you "blame the mysql2 gem" you file a "bug" in the pagy gem! Absolutely!

And, of course, in order to fix the alleged mysql2 gem issue, you patch a pagy method! Logical!

And now "blaming the mysql2 gem" without a single line of code that points at it... it's so professional!

now you know one more edge case that you /may/ find useful in the future

Thank you for sharing your wisdom! It's certainly a "case" to reference in any programming manual!

So much so that I close it before anyone could waste any more time on this.