Prefetching significantly improves the performance of ORM. Let's explore how prefetching works under the hood.

When you iterate on a recordset through a for loop and access the value of a field in the first iteration, the prefetching process starts its magic. Instead of fetching data for the current record in the iteration, prefetching will fetch the data for all of the records. The logic behind this is that if you are accessing a field in the for loop, you are likely to fetch that data for the next record in the iteration as well. In the first iteration of the for loop, prefetching will fetch the data for all of the recordset and keep it in the cache. In the next iteration of the for loop, data will be served from this cache, instead of making a new SQL query. This will reduce the query count from O(n) to O(1).

Let's suppose the recordset has 10 records. When you are in the first loop and access the name field of the record, it will fetch the data for all 10 records. This is not only the case for the name field; it will also fetch all the fields for those 10 records. In the subsequent for loop iterations, the data will be served from the cache. This will reduce the number of queries from 10 to 1:

for record in recordset: # recordset with 10 records
    record.name # It will prefetch data of all 10 records in the first loop
    record.email # data of email will be served from the cache.

Note that the prefetching will fetch the value of all of the fields (except the *2many fields) even if those fields are not used in the body of the for loop. This is because the extra columns only make a minor impact on performance compared to the extra queries for each column. Sometimes, prefetched fields reduce performance. In these cases, you can control the fields that are being prefetched by passing a list of fields to prefetch in the context. To do this, you need to pass prefetch_fields in the context as follows:

recordset.with_context(prefetch_fields=[list of the fields to prefetch])

If you want to disable prefetching, you can pass False into the prefetch_fields context, as shown:

recordset.with_context(prefetch_fields=False)