Greetings. As far as I understand it, Evgenji just committed a change regarding index use in `IN()` clause. Maybe try the very lastest GIT Master first.
Also I wonder, why you would not use a JOIN instead of a IN() when you have a list of FK_A from C: select * from a inner join c on a.pk = c.fk_a Why use (uncorrelated?) sub-queries? Best regards Andreas On Wed, 2023-04-26 at 02:03 -0700, Silvio wrote: > We have some heavy queries that involve selecting records from a base > cached table A (~100K records) that satisfy a quite a number of > conditions expressed as > > A.PK [NOT] IN (...) > > on a secondary cached table B (~10M) records. Although the subqueries > use indexed columns the overall query is very slow. > > We are thinking of using a temporary memory table C that holds > primary keys of table A, evaluating the subqueries on B seperately > inserting or removing keys into table C as needed and finally having > a single subquery > > A.PK NOT IN (SELECT FK_A FROM C) > > Has anoyone ever tried such an approach in H2? Is there any reason to > expect an improvement in performance in comparison to the single > large query we have now? > > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/6e7570af-a74c-4e83-9560-a85cfad1e8d4n%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/798e67aa1384cc368d8af4fdcdae95b57e87435c.camel%40manticore-projects.com.
