如何在PostgreSQL中用Kotlin Exposed为外键创建索引

I ran into a frustrating issue where Kotlin Exposed wouldn't create an index on a foreign key column, leading to full table scans on a table that will eventually hold hundreds of thousands of records.

Table Definitions #

Here's how I've defined my tables:

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

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

The Problem #

When running EXPLAIN SELECT * FROM transactions WHERE header_id = 'bdbfc5d6-9cf1-430a-a361-a5f96cc7d799', the query plan showed a Parallel Seq Scan (full table scan). This is going to be a major performance bottleneck once the table grows.

Failed Attempts #

I tried multiple syntax variations to define the index, none of which triggered any schema changes when using SchemaUtils.createMissingTablesAndColumns(Headers, Transactions):

• Non-infix notation:

  val headerId = (uuid("header_id").references(Headers.id)).index("custom_header_index")
  

• Infix notation:

  val headerId = (uuid("header_id") references Headers.id).index("custom_header_index")
  

• Defining index before foreign key reference:

  val headerId = (uuid("header_id").index("custom_header_index") references Headers.id)
  

Note that SchemaUtils.createMissingTablesAndColumns works fine for creating indices on non-foreign key columns, just not on columns with foreign key constraints. Manually creating the index in the database fixes the performance issue immediately.

Root Cause Identified #

Digging into Exposed's source code, I found that the index creation logic filters out indices associated with foreign key columns:

for (table in tables) {
    val existingTableIndices = currentDialect.existingIndices(table)[table].orEmpty().filterFKeys()
    val mappedIndices = table.indices.filterFKeys() // This line excludes indices on foreign key columns
    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))
}

Next Steps #

I plan to submit an issue to the Exposed GitHub repository to address this bug. I'll update this post if the issue gets fixed.

内容的提问来源于stack exchange，提问作者TFennis