Contain vs Matching in Pagination: Which Strategy Yields Better Performance?

Goal

We want to identify where the ORM introduces latency when paginating a dataset that uses eager loading or filtering of associated records.

Constraints

Using CakePHP 5.x, paginate a Posts model with a hasMany Comments association. When using contain, each page can trigger additional queries if foreign keys lack indexes, leading to an N+1 pattern. When using matching, the ORM may return duplicate root entities because it performs a join, but does not deduplicate automatically, inflating the reported page size.

Unresolved Decision

Should we rely on matching to filter associated records and then deduplicate in PHP, or should we use contain and perform post‑filtering, given that the documentation does not prescribe a single optimal strategy?

Questions

1. In a paginated query, does contain always trigger an additional query per page, or can it be optimized with proper indexing?
2. When using matching, how does CakePHP handle duplicate root entities across pages, and is there a documented way to deduplicate automatically?
3. Which approach yields the lowest overall runtime when measured with Profiler or DebugKit for a typical Posts–Comments dataset?