I have a query that returns over 190,000 records, and I need to break those records up into smaller chunks for performance and presentation in Visualforce. Each chunk is supposed to presented on a single page, and pagination is implemented using the OFFSET and LIMIT clauses in SOQL. A simplified version of the query containing the key elements is shown below:

SELECT Account__c, Product2__c, MyValue__c
FROM MyObject__c
ORDER BY Account__r.Name, Product2__r.Name

As implied, Account__c and Product2__c are both relationships to the standard Account and Product2 objects. For what it's worth, Account__c is a Master-Detail(Account) field and Product2__c is a Lookup(Product) field.

I want to limit each page to a consistent number of Account-Product2 combinations. But I am having trouble doing this because each combination can have a variable number of MyObject__c records.

What would be a good solution for implementing consistent pagination in this situation? I have tried creating a "composite ID" formula field by concatenating Account__c and Product2__c. But the MyObject__c query times out when I add "AccountProductCompositeId__c IN :compositeIdSet" to the WHERE clause, even when the number of combinations per page is limited to 25.

At this point I am thinking of running two queries to pull in more information than I need, and then use Apex to drop the excess data before presenting the data on the Visualforce page.