sql: generate better join orderings for Hasura-type queries

Questions from template:
- is there a difference between the performance you expect and the performance you observe?
- Yes
- are you surprised by your performance results?
- Yes
- are you comparing CockroachDB with some other database?
- Yes, Postgres

- What is the storage profile?
- how many nodes?
- 16
- how much storage?
- 16T (1T per disk, 25.000 IOPS each)
- how much data?
- Displayed further down
- replication factor?
- 3

- I want CockroachDB to be optimized for my use case.

---

While trying to migrate to CockroachDB from Postgres, I have run into some query performance issues.

I would have liked to use CockroachDB with Hasura, which means I don't have control over the generated queries. This is not a problem on Postgres as they perform, but found it to be a problem on CockroachDB. In order to make it easier to understand the query generated by Hasura, I have simplified (and tested) a query, along with naming of tables.

Setup:
Table: Party
Table: Identifier
Table: party_identifier

The relation between party and identifier is a many-2-many via the party_identifier table. So a Party can refer to many Identifiers via party_identifier, and vice versa.

Eg.:
```
Party:
id|identifier|other_fields...
1|1|...
2|2|...
3|3|...
```
```
Identifier
id|party|other_fields...
1|1|...
2|2|...
3|3|...
```
```
party_identifier
A|B
1|1
1|2
1|3
2|1
3|1
```

Indexes:
```
CREATE UNIQUE INDEX "Identifier_pkey" ON defaultdb.public."Identifier" USING btree (id ASC);
CREATE UNIQUE INDEX "Party_pkey" ON defaultdb.public."Party" USING btree (id ASC);

CREATE INDEX "Identifier.OtherFieldId" ON defaultdb.public."Identifier" USING btree ("OtherFieldId" ASC);
CREATE INDEX "Party.OtherFieldId" ON defaultdb.public."Party" USING btree ("OtherFieldId" ASC);

CREATE INDEX party_identifier_a ON party_identifier (a) STORING (b)
CREATE INDEX party_identifier_b ON party_identifier (b) STORING (a)
CREATE UNIQUE INDEX "party_identifier_pkey" ON defaultdb.public."party_identifier" USING btree (rowid ASC);
CREATE UNIQUE INDEX "party_identifier_ab_unique" ON defaultdb.public."party_identifier" USING btree ("a" ASC, "b" ASC);
```

Query:
```
select pty."id"
from
(select "id" from "public"."Party" limit 10) as pty
left join (select * FROM defaultdb.public."party_identifier"
JOIN defaultdb.public."AssignedIdentifier" ON "party_identifier"."A" = "AssignedIdentifier".id) as mn on pty."id" = mn."B";
```
Explain for query:
```
distribution: full
vectorized: true

��� hash join (right outer)
��� estimated row count: 16
��� equality: (B) = (id)
��� right cols are key
���
��������� ��� hash join
��� ��� estimated row count: 2,050,390
��� ��� equality: (id) = (A)
��� ��� left cols are key
��� ���
��� ��������� ��� scan
��� ��� estimated row count: 2,796,596 (100% of the table; stats collected 6 days ago; using stats forecast for 6 days ago)
��� ��� table: AssignedIdentifier@AssignedIdentifier.OtherFieldId
��� ��� spans: FULL SCAN
��� ���
��� ��������� ��� scan
��� estimated row count: 2,050,390 (100% of the table; stats collected 5 days ago; using stats forecast for 5 days ago)
��� table: party_identifier@party_identifier_pkey
��� spans: FULL SCAN
���
��������� ��� scan
estimated row count: 10 (<0.01% of the table; stats collected 7 days ago)
table: Party@OtherFieldId
spans: LIMITED SCAN
limit: 10
```
As seen from the explain, there are 2 FULL SCANS. Performing the same query on Postgres, does not result in full scans. We would have expected party_identifier_b and Identifier_pkey to be used.

Real world setup:
```
select count(*) from "Party"; --11.752.843
select count(*) from "Identifier"; -- 2.796.596
select count(*) from "party_identifier"; -- 5.822.729

CREATE VIEW public."party_identifier_a" (
"a",
id
) AS SELECT
"a",
"Party".id,
FROM
defaultdb.public."party_identifier"
INNER JOIN defaultdb.public."Party" ON "party_identifier"."B" = "Party".id

CREATE VIEW public."party_identifier_b" (
"b",
id
) AS SELECT
"b",
"Party".id,
FROM
defaultdb.public."party_identifier"
INNER JOIN defaultdb.public."Party" ON "party_identifier"."a" = "Party".id

select count(p.id)
from "Party" p
where p.id
not in
(select "B" from "party_identifier"); -- 7.461.371

select count(i.id)
from "Identifier" i
where i.id
not in
(select "a" from "party_identifier"); -- 746.206

select count(*) from "party_identifier" mn where mn."B" in
(select p.id
from "Party" p
where p.id
not in (select "B" from "party_identifier")); -- 0

select count(*) from "party_identifier" mn where mn."a" in
(select i.id
from "Identifier" i
where i.id
not in (select "a" from "party_identifier")); -- 0
```

References:
https://hasura.io/docs/latest/databases/postgres/cockroachdb/index/

Jira issue: CRDB-28041

labels: 'C-question'