Pagination Pushdown and Arrays

SQL++
1

Hi,

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.

Thanks, Pascal

2

Not sure how many categories you are taking here and data. You should try each index separately and which one works best for you.

CREATE INDEX ix1 ON context(DISTINCT ARRAY cg  FOR cg IN category END,  createdDate DESC);
CREATE INDEX ix1 ON context( createdDate DESC, DISTINCT ARRAY cg  FOR cg IN category END);
CREATE INDEX ix1 ON context( createdDate DESC) WHERE ANY v IN category SATISFIES v=2 END ;



SELECT  META().id 
FROM  `content`  
WHERE ANY v IN category SATISFIES v=2 END AND createdDate IS NOT NULL
ORDER BY  createdDate DESC 
OFFSET 10000 
LIMIT 20;