Geo: Stop joining with tables on the primary in ProjectRegistryFinder?

Crazy idea. I know I've missed FDW conversations, so feel free to close if I'm totally missing the point (but please lmk why). I know it was supposed to help performance and that turned out to be true for just a few situations at the moment.

But joining with a remote table can't be faster than joining with the local table, and if it's acceptable to join with a local table in some situations (due to selective sync, or no FDW), why wouldn't we do it that way all the time?

I'm trying to understand our queries while working on https://gitlab.com/gitlab-org/gitlab-ee/issues/5430, and I’m wondering if we can get away with not joining the projects table to find/count projects/wikis. It would be much easier to reason about (and prevent bugs).

Sometimes we join the projects table on the primary (FDW), and sometimes we join the local projects table, and sometimes we don’t join with a projects table at all. It’s really confusing.

Shouldn't an orphaned project_registry row be a transient condition, even with flipping selective_sync on and off? Could it be acceptable to be inaccurate during that time?

I would prefer not joining to any projects table at all, but maybe we could standardize on only joining the local projects table?

With FDW, and without selective sync

Repos

  • Total repos: Count local projects
  • Synced repos: Count project_registry.synced_repos
  • Failed repos: Count project_registry.failed_repos

Wikis

  • Total wikis: Count local projects, joined with local project_features where wiki enabled
  • Synced wikis: Count project_registry.synced_wikis, joined with primary project_features where wiki enabled
  • Failed wikis: Count project_registry.failed_wikis, joined with nothing

With FDW, and with selective sync

Repos

  • Total repos: Count local projects
  • Synced repos: Count project_registry.synced_repos, joined with local projects
  • Failed repos: Count project_registry.failed_repos, joined with local projects

Wikis

  • Total wikis: Count local projects, joined with local project_features where wiki enabled
  • Synced wikis: Count local projects, joined with local project_features where wiki enabled, joined with project_registry.synced_wikis
  • Failed wikis: Count project_registry.failed_wikis, joined with local projects, joined with local project_features where wiki enabled

Without FDW, and without selective sync

Repos same as “With FDW, and without selective sync”

Wikis same as “With FDW, and with selective sync”

Without FDW, and with selective sync

Repos same as “With FDW, and with selective sync”

Wikis same as “With FDW, and with selective sync”