private void createForeignKeyIndices(Connection conn, int minTuples) throws SQLException {
List<ForeignKeyConstraint> fkConstraints = new ArrayList<ForeignKeyConstraint>();
PreparedStatement selectStmt = conn.prepareStatement(constraintQuery);
ResultSet rs = selectStmt.executeQuery();
while (rs.next()) {
String tableName = rs.getString(1);
String columnName = rs.getString(2);
String constraintName = rs.getString(3);
fkConstraints.add(new ForeignKeyConstraint(tableName, columnName, constraintName));
}
for (ForeignKeyConstraint fk : fkConstraints) {
selectStmt = conn.prepareStatement(tuplesQuery);
selectStmt.setString(1, fk.getConstraintName());
rs = selectStmt.executeQuery();
if (rs.next()) {
fk.setTuples(rs.getLong(1));
}
}
Collections.sort(fkConstraints);
for (ForeignKeyConstraint fk : fkConstraints) {
String indexName = "fki_" + fk.getConstraintName();
if (indexName.lastIndexOf("_fkey") > 0) {
indexName = indexName.substring(0, indexName.lastIndexOf("_fkey"));
}
if (fk.getTuples() >= minTuples && !hasIndex(conn, indexName)) {
System.out.printf(
"Creating index %s (tuples: %d)\n",
(fk.getTableName() + "." + fk.getColumnName() + " --> " + indexName), fk.getTuples());
PreparedStatement createStmt =
conn.prepareStatement(
String.format(createIndex, indexName, fk.getTableName(), fk.getColumnName()));
createStmt.execute();
}
}
for (Entry<String, Pair<String, String>> entry : indexMap.entrySet()) {
String indexName = entry.getKey();
String tableName = entry.getValue().getLeft();
String columnName = entry.getValue().getRight();
if (!hasIndex(conn, indexName)) {
System.out.printf(
"Creating index %s\n", (tableName + "." + columnName + " --> " + indexName));
try {
PreparedStatement createStmt =
conn.prepareStatement(String.format(createIndex, indexName, tableName, columnName));
createStmt.execute();
} catch (SQLException e) {
System.err.println("Failed to create index " + indexName + ": " + e.getMessage());
}
}
}
}
