Does the Pagination Threshold setting in the Get Items action relate to how many items can be returned or how many items can be searched?

Question

If I have a list that contains, for example, 3,000,000 items.

If I apply the following settings in the Get items action:

Top Count:  `5000`

Settings > Pagination > Threshold:  `100000`  

Odata filter:  `ExpiryDate ne null and ExpiryDate eq '2022-09-08'` 
    
(this filters the list to entries where the ExpiryDate column value is not null AND is equal to todays date)   

Which of the following behaviours will occur:

A) Get items will search the whole list of 3,000,000 items and return a maximum of 100,000 results

OR

B) Get items will only search 100,000 items out of the 3,000,000 items in the list?
(and therefore not do a full search of the list)

OR

C) Something else?

What I am really trying to clarify is:

Does the Pagination Threshold setting in the Get items action relate to how many items can be returned or how many items can be searched?

It would also be good to know:

If I set Pagination > Threshold to 100,000, what should I set the Top Count value to and why?

Edit:

I don't understand the relationship between Top Count and Pagination Threshold.

The official docs state:

To specify the number of items to be returned, expand the Advanced options, and specify the number in the Top Count action property.

However, the following test proves that statement to be wrong:

List Items:  3,600 
Pagination Threshold:  5,000 
Top Count:  500

A Get items action with the above configuration will return ALL items in the list (i.e. 3,600).

Related Reading:

In-depth analysis into Get items and Get files SharePoint actions for flows in Power Automate

How to get more than 5000 items from SharePoint Online in Flow

More action settings and four new connectors

Limits for automated, scheduled, and instant flows

Working with Large Data in Power Automate through Pagination (Video)

Answer 1

To confirm, the Get items action you are describing is "filtering" a data set rather than "searching". Searching implies it is using the SharePoint Search service which it is not.

Which of the following behaviours will occur:

Get items will filter the whole list of 3,000,000 items and return a maximum of 100,000 results

The column "ExpiryDate" would need to be indexed in order for the filter query to work e.g. Power Automate is dealing with the same list view threshold, since it is likely just using the SharePoint REST API.

Supposing that "ExpiryDate" is indexed, then the entire list will be filtered using this indexed column. The pagination value defined in Power Automate will limit your query to whatever you have defined e.g. 100k, meaning only 100k would be returned despite the whole list being filtered.

It's worth noting that in this article, Microsoft acknowledge a limitation with the Get items action:

"While using Get items on lists with more than 5000 items with a filter query, you may observe that no records are returned if there are no items matching the filter query in the first 5000 items. To fix this, enable Pagination on Get items from the action settings menu when working with lists with more than 5000 items."

Does the Pagination Threshold setting in the Get items action relate to how many items can be returned or how many items can be filtered?

The pagination limit relates to how many items will be returned. All items are filtered, since it's defined in your query. Microsoft describe the pagination setting as follows:

"Pagination enables you to handle more records than are returned in a single call from a service. For example, some services only return back 50 items at a time. However, in the flow, you may want to get all of the items in the list. By turning on pagination, the flow engine will continue to call the service until it has all of the items – or – hits the limit that you explicitly define in the flow."

What is the relationship between Top Count and Pagination Threshold?

There is no relationship, if you define a value in the top count then you will only receive that amount of items back from the flow. This is described here:

In-depth analysis into Get items and Get files SharePoint actions for flows in Power Automate