Hello.

Since the topic died down I would like to give it another go and explain all that transpired, in the hopes of trying to understand this behavior.
I am unable to determine is this is normal/intended behavior, or something that might be worth being looked upon by the devs.

-I have a query that returns 4791 results. There are no row limit on the query.
-There is automated daily maintenance on this database, we run both GC and fully recompute selectivity of indices every day.

-The fetch operation is very fast until it reaches record number 4000, then it hangs for 20-30 seconds, and resumes the fetch normally.

-I tried identifying and removing the record in question, but the problem persisted (the “new” row 4000 of the fetch hanged after I deleted the old row).

-If I limit the query to 4790 results (or less), the entire fetch runs fast, no hang-ups.

-Trying deleting the last few records because of this, both behavior remained unchanged (hangs at record 4000 if query is without limit, runs fast if query is limited by 1 or more rows from the total).
-Ran those tests trough 3 different applications : isql, raw php and ibexpert, the behavior is consistent among all.

This happened in firebird 3.0.4, embedded and remotely.
I copied the db over and reproduced the same behavior in 2 different servers successfully, to eliminate the hardware from the equation.

Now, in the last few days, I created 2 new databases, one in firebird 2.5 and another in 3.0, and copied over all the structure and data from the original db, I wanted to test the behavior in a raw new database with zeroed usage statistics, and on the older firebird 2.5.

Was also able to reproduce the same behavior in both of these new databases.

I recorded a video of the behavior from ibexpert here http://cb.multidadosti.com.br/plaenge.mp4

Again, I’m sorry if I’m missing something and there might be a logical explanation for this happening.