Hi, This query is quite tricky to optimize. I believe H2 doesn't currently use the most efficient algorithm, even if you do have the right index (an index on two columns: b, then a). There is a workaround, but it's a quite ugly. I didn't test it (I hope I got the syntax right):
create index on foo(indexed_column_b, indexed_column_a desc); select indexed_column_1 from foo where indexex_column_b = value limit 1 order by indexex_column_b, indexex_column_a desc; Regards, Thomas On Wednesday, February 12, 2014, Reinier Zwitserloot <[email protected]> wrote: > This query: > > select MAX(indexed_column_a) from foo; > > gives a near-instant result. However, this query: > > select MAX(indexed_column_a) from foo where indexed_column_b = value; > > does a complete scan of the table, and is therefore many orders of > magnitude slower. It does use the index on column_b, but not the one on > column a. > > I've tried the same stunt in postgresql, and it looks like the second > query is still near instant. > > For this particular exercise, there are about 135000 rows where > indexed_column_b has value 'X' and another 13500 rows where > indexed_column_b has value 'Y'. > > h2 takes about 500ms to calculate, postgres anywhere between 1 and 70 ms. > > > EXPLAIN seems to indicate the index on indexed_column_a is not used at all. > > Is this not solvable in h2, or should I make my indices differently? > > -- > 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]<javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to > [email protected]<javascript:_e(%7B%7D,'cvml','[email protected]');> > . > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/groups/opt_out. > -- 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 post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
