"SELECT .. WHERE ... IN ... OR ... IN" on large tables

[Dhanasekar Rengasamy]

We are using H2 db version 2.1.210 in in-memory mode and trying to fetch the data from large table. The query is looks below format. We have 4424 arguments in IN clause.

**

select * from \"Policy\" policy0_
    where
policy0_.headEndUuid in (?, ?...)
    or
policy0_.tailEndUuid in (? , ?...)
    order by policy0_.policyName asc, policy0_.lastUpdate asc limit ?
{1: 'id1',
:
:
4425:50
};

**

We are using Spring JPA to fetch the data with using spring filter Specification.

Currently this table has total 19997 rows and trying to filter the rows based on the WHERE and IN clause and fetching 50 rows using pagination.

Select query taking 20 sec to return the result. Attached the sql trace for reference.

Kindly provide your suggestion to improve the SELECT query execution performance.

Thanks in Advance !!!

[Dhanasekar Rengasamy]

[Evgenij Ryazanov]

Hello!

H2 is unable to optimize such OR conditions when multiple columns are involved.

To make your query faster, you need to rewrite it with a UNION between two inner queries, one with filtration by first column and second with filtration by second one.

These inner queries should be able to use indexes on these columns.

[Dhanasekar Rengasamy]

Thanks @Evgenij for the suggestion.. Will try the same.

Regards,

RDS