Feature "Rewrite queries using LIKE with wildcards using CONCAT" kills performance on Postgres

[Sukramolas]

This change introduced in Spring JPA 2.7.13 changes LIKE queries to CONCAT($1, '%') format. However Postgres cannot use table indexes when right-hand side clause has a function (like CONCAT), so invoking query containing CONCAT will perform very poorly on large tables.

As suggested fix this optimization should be optional (e.g. via Spring JPA property setting), and can default to be active. But it should be possible to return to 2.7.12 version of LIKE query.

[mp911de]

See also #2939 and #2760

[Sukramolas]

To add to my original comment, looking at index behavior with LIKE 'ABC%' vs LIKE ( CONCAT ( 'ABC', '%' ) )

The LIKE % results into Index Scan, whereas CONCAT results into SEQ_SCAN which has much worse performance over large tables (> 100M rows).

[mp911de]

Closing this issue as the 2.7 development line is no longer maintained. The 3.x development line no longer uses CONCAT for like queries.