Тот же запрос SQL, CockroachDB занимает 4 минуты, SQL Server занимает 35 мс. Я что-то упускаю?

Похоже, что на самом деле это связано с разницей в планах запросов между SQL Server и CockroachDB, но это можно обойти несколькими способами.

Основная проблема заключается в том, что таблица transaction имеет два ограничения внешнего ключа, указывающих на таблицу wallet, но оба внешних ключа допускают значение NULL. Это не позволяет CockroachDB нажимать ограничение в 10 строк через соединение, потому что при сканировании таблицы transaction может потребоваться создать более 10 строк, чтобы весь запрос выдал до 10 строк.

Мы видим это в плане запроса:

> explain select * from transaction t
  join wallet s on t.sender_id=s.id
  join wallet r on t.receiver_id=r.id
  limit 10;
                    info
---------------------------------------------
  distribution: full
  vectorized: true

  • limit
  │ count: 10
  │
  └── • lookup join
      │ table: wallet@primary
      │ equality: (receiver_id) = (id)
      │ equality cols are key
      │
      └── • lookup join
          │ table: wallet@primary
          │ equality: (sender_id) = (id)
          │ equality cols are key
          │
          └── • scan
                estimated row count: 10,000
                table: transaction@primary
                spans: FULL SCAN

Обратите внимание, что ограничение применяется после обоих объединений.

Есть два относительно простых способа исправить это. Во-первых, мы могли бы заменить joins на left joins. Это позволит снизить ограничение до сканирования таблицы transaction, потому что левое соединение никогда не отбрасывает строки.

> explain select * from transaction t
  left join wallet s on t.sender_id=s.id
  left join wallet r on t.receiver_id=r.id
  limit 10;
                  info
----------------------------------------
  distribution: full
  vectorized: true

  • lookup join (left outer)
  │ table: wallet@primary
  │ equality: (receiver_id) = (id)
  │ equality cols are key
  │
  └── • lookup join (left outer)
      │ table: wallet@primary
      │ equality: (sender_id) = (id)
      │ equality cols are key
      │
      └── • scan
            estimated row count: 10
            table: transaction@primary
            spans: LIMITED SCAN
            limit: 10

Другой вариант — сделать ссылочные столбцы в ограничениях внешнего ключа non null. Это также позволит снизить ограничение до сканирования таблицы transaction, потому что даже внутреннее соединение никогда не отбрасывает строки.

> alter table transaction alter column sender_id set not null;
ALTER TABLE

> alter table transaction alter column receiver_id set not null;
ALTER TABLE

> explain select * from transaction t
  join wallet s on t.sender_id=s.id
  join wallet r on t.receiver_id=r.id
  limit 10;
                  info
----------------------------------------
  distribution: full
  vectorized: true

  • lookup join
  │ table: wallet@primary
  │ equality: (receiver_id) = (id)
  │ equality cols are key
  │
  └── • lookup join
      │ table: wallet@primary
      │ equality: (sender_id) = (id)
      │ equality cols are key
      │
      └── • scan
            estimated row count: 10
            table: transaction@primary
            spans: LIMITED SCAN
            limit: 10

Я считаю, что в вашей таблице может быть устаревшая статистика. (Хотя, надеюсь, к этому моменту статистика автоматически обновится, и у вас больше не будет этой проблемы.) Вы можете прочитать об этом в Документация по CockroachDB, а также там есть ссылка, описывающая, как вручную создать новую статистику.