I read the great blog post about index use and pagination here:
I have a lot of document that can be related to different categories. Let’s assume an article that can be “sport”, but also “news” and “football”. So I store all categories in a array. I now need a pagination over the newest article in a category, someting like:
SELECT id FROM
content WHERE ANY v IN category SATISFIES v=2 END AND ORDER by createdDate DESC OFFSET 10000 LIMIT 20;
As described in the blog post “Index order will not be used when Array Indexes are involved.” that doesn’t work. In this case 10’021 documents are going to “Distinct Scan” which is too slow.
Is there a way to improve this pagination? Of course a keyset pagination would be much faster, but the business wants a pagination with page numbers in this case.