Tam Metin Arama — LIKE '%kira%' Neden Hem Yavaş Hem Eksik?

1
-- A word vector built with PostgreSQL's Turkish configuration, kept up to date by the database.
2
ALTER TABLE account_transaction
3
    ADD COLUMN description_search tsvector
4
    GENERATED ALWAYS AS (to_tsvector('turkish', coalesce(description, ''))) STORED;
5

6
CREATE INDEX ix_tx_description_search ON account_transaction USING GIN (description_search);

1
interface TransactionSearchRepository extends Repository<AccountTransaction, Long> {
2

3
    // The query text goes through the same Turkish rules as the stored descriptions.
4
    @Query(value = """
5
            SELECT * FROM account_transaction
6
            WHERE account_id = :accountId
7
              AND description_search @@ plainto_tsquery('turkish', :text)
8
            ORDER BY created_at DESC
9
            LIMIT 50
10
            """, nativeQuery = true)
11
    List<AccountTransaction> searchDescriptions(String accountId, String text);
12

13
    List<AccountTransaction> findByAccountIdAndCounterpartyIban(String accountId, String iban);
14
}

1
@Service
2
class TransactionSearch {
3

4
    private static final Pattern IBAN = Pattern.compile("^TR\\d{24}$");
5
    private final TransactionSearchRepository transactions;
6

7
    TransactionSearch(TransactionSearchRepository transactions) {
8
        this.transactions = transactions;
9
    }
10

11
    List<AccountTransaction> search(String accountId, String input) {
12
        String compact = input.replace(" ", "").toUpperCase(Locale.ROOT);
13
        if (IBAN.matcher(compact).matches()) {
14
            return transactions.findByAccountIdAndCounterpartyIban(accountId, compact);   // a code: exact match
15
        }
16
        return transactions.searchDescriptions(accountId, input);   // free text: full-text search
17
    }
18
}

1
@DataJpaTest
2
@Testcontainers
3
class TransactionSearchIT {
4

5
    @Container
6
    @ServiceConnection
7
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16-alpine");
8

9
    @Autowired TransactionSearchRepository search;
10
    @Autowired TestEntityManager db;
11

12
    @Test
13
    void findsEverySpellingOfRentButNotCarRental() {
14
        for (String text : List.of("KİRA ödemesi", "Kira depozitosu", "Ekim kirası", "Araç kiralama")) {
15
            db.persist(AccountTransaction.of("TR01", text));
16
        }
17
        assertThat(search.searchDescriptions("TR01", "kira")).extracting(AccountTransaction::getDescription)
18
                .containsExactlyInAnyOrder("KİRA ödemesi", "Kira depozitosu", "Ekim kirası");
19
    }
20
}