Performance issues when using offset

[alexgb]

When issuing queries using offset, the generated SQL performs poorly.

Article.limit(20).offset(20)

Yields the following SQL, which takes 45 seconds to complete on a table with 100 million rows.

SELECT * FROM (
 SELECT raw_sql_.*, rownum raw_rnum_
 FROM (SELECT "ARTICLE".* FROM "ARTICLE") raw_sql_
)
WHERE raw_rnum_ between 21 and 40

It would be better if it generated this instead which takes only 50ms on the same table – a 900x improvement.

SELECT * FROM (
 SELECT raw_sql_.*, rownum raw_rnum_
 FROM (SELECT "ARTICLE".* FROM "ARTICLE" ) raw_sql_
 WHERE rownum <= 40
)
WHERE raw_rnum_ > 21

[alexgb]

Actually this appears to be an issue with arel that is fixed in master.

[eduardordm]

@alexgb would you have more info on that fix?

Edit:

rails/arel#99

rails/arel@d1c9c46