Reduce query count in PageEntry.from_page_instance() #927

Context

The synctree.py module synchronizes page trees across locales by creating aliases that point to the original content of each page.

To support this process, it builds a PageIndex — an in-memory index that maps each page to its translations and aliases across locales.

PageIndex.from_database() retrieves pages from the database and builds this index by creating one Entry object for each page.

Entry.from_page_instance() is responsible for extracting the data required for each entry, including basic page information, existing translations (locales), and existing aliases (aliased_locales).

Current behavior

Building the index generates multiple database queries. The sequence is as follows.

synchronize_tree() calls from_database() to build the index:

page_index = PageIndex.from_database().sort_by_tree_position()

Inside from_database(), a single query fetches all non-alias pages below the root:

for page in Page.objects.filter(alias_of__isnull=True, depth__gt=1).only(
    *PageIndex.Entry.REQUIRED_PAGE_FIELDS
):
    pages.append(PageIndex.Entry.from_page_instance(page))

For each page in the loop, from_page_instance() issues up to 3 additional queries:

@classmethod
def from_page_instance(cls, page):
    # Query 1 — fetches the parent page (only if depth > 2)
    parent_page = page.get_parent() if page.depth > 2 else None

    return cls(
        ...
        # Query 2 — fetches all locales where this page exists as a real translation
        list(
            Page.objects.filter(
                translation_key=page.translation_key,
                alias_of__isnull=True,
            ).values_list("locale", flat=True)
        ),
        # Query 3 — fetches all locales where this page exists as an alias
        list(
            Page.objects.filter(
                translation_key=page.translation_key,
                alias_of__isnull=False,
            ).values_list("locale", flat=True)
        ),
    )

Problem

Given the structure described above, building the index for a site with N pages results in up to 3N + 1 database queries.

Possible approach

One possible direction would be to evaluate whether the data currently fetched inside the loop can instead be retrieved upfront using a small number of aggregated queries. This would reduce the number of queries from 3N + 1 to a constant number, regardless of how many pages the site has.