Как создать индекс по внешним ключам с помощью kotlin для базы данных PostgreSQL

Question

Я использую Kotlin Exposed для определения таблицы так

object Headers : Table("headers") {
    val id = uuid("id").primaryKey()
    // tons more columns
}

И у меня есть другая таблица, определенная так

object Transactions : Table("transactions") {
    val headerId = (uuid("header_id").references(Headers.id)).index("custom_header_index")
    // tons more columns
}

Теперь, как вы можете видеть, я пытаюсь создать индекс для этой ссылки /FOREIGN KEY, потому что, если я пытаюсь запросить это отношение, я получаю следующий результат

EXPLAIN SELECT * FROM  transactions WHERE header_id = 'bdbfc5d6-9cf1-430a-a361-a5f96cc7d799'

Gather  (cost=1000.00..13771.31 rows=8 width=171)"
  Workers Planned: 2"
  ->  Parallel Seq Scan on transactions  (cost=0.00..12770.51 rows=3 width=171)"
        Filter: (header_id = 'bdbfc5d6-9cf1-430a-a361-a5f96cc7d799'::uuid)"

Это делает последовательное сканирование. И поскольку эта таблица будет содержать сотни тысяч записей, это невыносимо медленно.

Я попробовал пару вещей, чтобы заставить его работать, но не синтаксические изменения, кажется, вызывают любые изменения схемы

exampes:

// no infix
val headerId = (uuid("header_id").references(Headers.id)).index("custom_header_index")
// infix
val headerId = (uuid("header_id") references Headers.id).index("custom_header_index")
// index before reference
val headerId = (uuid("header_id").index("custom_header_index") references Headers.id)

Я пробовал все это с и без пользовательских имен индекса.

Я должен также отметить, что я использую

SchemaUtils.createMissingTablesAndColumns(Headers, Transactions)

который в любом другом типе столбца обнаруживает изменение индекса, но не для внешних связей.

Что я делаю не так, и как я могу получить доступ к kotlin для создания этих индексов?

РЕДАКТИРОВАТЬ: УТОЧНЕНИЕ

Извините, мой вопрос не указал, что моя проблема в том, что Kotlin Exposed не создает эти индексы. Когда я создаю их вручную, все работает как положено.

В то же время я также нашел вероятную причину этой проблемы

for (table in tables) {
    val existingTableIndices = currentDialect.existingIndices(table)[table].orEmpty().filterFKeys() 
    val mappedIndices = table.indices.filterFKeys() // Here foreign keys are filtered

    existingTableIndices.forEach { index ->
        mappedIndices.firstOrNull { it.onlyNameDiffer(index) }?.let {
            exposedLogger.trace("Index on table '${table.tableName}' differs only in name: in db ${index.indexName} -> in mapping ${it.indexName}")
            nameDiffers.add(index)
            nameDiffers.add(it)
        }
    }

    notMappedIndices.getOrPut(table.nameInDatabaseCase(), {hashSetOf()}).addAll(existingTableIndices.subtract(mappedIndices))

    missingIndices.addAll(mappedIndices.subtract(existingTableIndices))
}

Приведенный выше код взят из библиотеки Exposed и фильтрует все индексы, которые он должен создать, и удаляет их, если в столбце есть внешний ключ, что делает невозможным создание регулярных индексов для этих столбцов.

Я, вероятно, сделаю проблему в их репозитории github и обновлю этот вопрос, если он когда-нибудь будет исправлен.

Answer 1

Для тех, кто читает это в 2022 году, это работает, как и ожидалось, в Exposed 0.39.2.

Порядокindex()иreferences()не имеет значения, если вы используете инфиксную нотацию (в любом случае это просто синтаксический сахар)

Одно примечание: проверка того, был ли индекс создан с использованиемEXPLAIN PLANкак показано здесь, это не лучший способ, поскольку индекс мог быть создан, но ваша БД, возможно, решила не использовать его в любом случае.

Лучшим способом было бы использовать в PostgreSQL

      \d transactions

Это бы описало вашу таблицу и показало бы что-то вроде

      Indexes:
    "transactions_header_id" btree (header_id)

В этом случае.