I did some more testing on Postgres and it looks like this is not as simple as I thought; given a sufficiently large DB postgres too ends up relatively slow. Looks like my previous experiment looked to be much faster in PSQL because it still cached the entire table in memory.
But, compound indices do solve the problem. Thanks! On Thursday, 13 February 2014 07:43:54 UTC+1, Thomas Mueller wrote: > > 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]<javascript:>> > 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]. >> 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. >> > -- 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.
