search

benjamin658 / typeorm-cursor-pagination

Cursor-based pagination works with TypeORM Query Builder.
MIT License
186 stars 40 forks source link

Performance regression in v0.8.0 #51

Open avatarneil opened 2 years ago

avatarneil commented 2 years ago

Hello!

I'm a maintainer on a project https://github.com/lacuna-tech/mds-core which utilizes this library, and first off would like to thank you for putting this together! It definitely makes pagination with TypeORM much easier :)

Recently, we upgraded to v0.8.1 of this library, and discovered a performance regression when paginating on keys that are not the unique key. I believe that this regression occurred in this commit (which went into v0.8.0). The PG query planner (in our case, we are running against Postgres 13.4 running in RDS Aurora) seems to handle WHERE someColumn>someValue OR someColumn=someValue very poorly; we were seeing some queries which used to take 2s take >8min to return. One minor hand-made change to our queries, however, had them returning very quickly again: WHERE someColumn>=someValue.

I think that there is a pretty trivial change to the paginator that would fix this:

qb.where(`${this.alias}.${key} ${operator} ${this.paginationUniqueKey !== key ? '=' : ''}:${key}_1`, paramsHolder);

If you think this approach makes sense, I'm happy to cut a PR!

benjamin658 commented 2 years ago

Hi @avatarneil,

My apologies for the late reply.

The 3d2c20f98b94af9adea0794450b73998884a7436 changes resulted in a slow query, which is difficult to detect from the current test cases. I appreciate you doing some hand-made changes and recognizing the issue.

The PR is always welcome, thanks for your feedback!