Comparison predicates with row values don't create index conditions

[ghost]

Hi everyone,

Im using spring-data-jpa 3.0.3 along with H2 2.1.214. Im experiencing slowness with basic function like findById.

Here is a generated query made by spring-data-jpa :

My table name is : course_info
The primary key is :

• info_date
• class_id
• course_to_id

explain analyze select c1_0.info_date,c1_0.class_id,c1_0.course_to_id,c1_0.quantity from course_info c1_0 where (c1_0.info_date,c1_0.class_id,c1_0.course_to_id)=('2023-03-18',1,85);

SELECT
"C1_0"."INFO_DATE",
"C1_0"."CLASS_ID",
"C1_0"."COURSE_TO_ID",
"C1_0"."QUANTITY"
FROM "PUBLIC"."COURSE_INFO" "C1_0"
/* PUBLIC.COURSE_INFO.tableScan /
/ scanCount: 885380 /
WHERE ROW ("C1_0"."INFO_DATE", "C1_0"."CLASS_ID", "C1_0"."COURSE_TO_ID") = ROW ('2023-03-18', 1, 85)
/
reads: 38417
*/

We can see that it does a full table scan instead of using the primary key index.

If i rewrite myself the query in a more standard way

explain analyze select c1_0.info_date,c1_0.class_id,c1_0.course_to_id,c1_0.quantity from course_info c1_0 where c1_0.info_date= '2023-03-18' and c1_0.class_id= 1 and c1_0.course_to_id= 85;

The result is very fast since it use the primary key index :

SELECT
"C1_0"."INFO_DATE",
"C1_0"."CLASS_ID",
"C1_0"."COURSE_TO_ID",
"C1_0"."QUANTITY"
FROM "PUBLIC"."COURSE_INFO" "C1_0"
/* PUBLIC.PRIMARY_KEY_6F: COURSE_TO_ID= CAST(85 AS BIGINT)
AND INFO_DATE= DATE '2023-03-18'
AND CLASS_ID= CAST(1 AS BIGINT)
/
/ scanCount: 2 /
WHERE ("C1_0"."COURSE_TO_ID" = CAST(85 AS BIGINT))
AND ("C1_0"."INFO_DATE" = DATE '2023-03-18')
AND ("C1_0"."CLASS_ID" = CAST(1 AS BIGINT))
/
reads: 4
*/

Do you have any idea why the first query (the generated one) doesnt use the existing index? Is there something I can do? I am using the last version of spring-data-jpa and h2.

Thank you!

[ghost]

Thank you very much @katzyn for that fast PR!