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()); } } } }