Hi I did also experience this behavior. An analysis from the H2 team would be much appreciated. At the moment, I implemented a work around which drops and recreates all indices on primary keys when the application is started.
Regards, Remo On Oct 5, 10:47 am, Dario Simone <[email protected]> wrote: > Hi, > > Excuse the long wait, I was a little busy lately. > I've attached a test case. Executing the test case on a new database > yields two explain statements which are exactly the same except for > the scanCount values. The second explain statement contains a much > smaller scanCount when joining the table. > > In the given test case the values don't differ by much, but with > increasing number of entries the gap only grows bigger... > > Thanks a lot for having a look at it! > > Regards, > Dario > > On Mon, Sep 27, 2010 at 7:15 PM, Thomas Mueller > > <[email protected]> wrote: > > Hi, > > > I don't know what the problem could be. There is a difference between > > a primary key that is created when there are no records in a table, > > and a primary key that is created after inserting data. But I couldn't > > reproduce the problem you describe. My test case is: > > > drop table test; > > set cache_size 0; > > create table test(id int primary key, name varchar); > > insert into test select x, space(10000) from system_range(1, 5000); > > explain analyze select * from test where id between 10 and 20; > > alter table test drop primary key; > > alter table test add primary key(id); > > explain analyze select * from test where id between 10 and 20; > > > Could you try to change the test case to show the problem you observed? > > > Regards, > > Thomas > > > On Fri, Sep 24, 2010 at 10:58 AM, Dario Simone <[email protected]> > > wrote: > >> I forgot to mention: > > >> I'm using version 1.2.139 > > >> On Fri, Sep 24, 2010 at 10:15 AM, Dario <[email protected]> wrote: > >>> Hi all, > > >>> All in all I'm really happy with H2 and (almost) everything works as > >>> it should. > > >>> While looking at a query, with bad performance I came upon strange > >>> behaviour, though: > >>> H2 didn't use the primary key index to do a join which had only the > >>> table's primary key in the on-clause (explain showed a scanCount for > >>> that join of 106757553). After dropping the Index and recreating the > >>> primary key, the index was used (the scanCount was down to 35003). > > >>> The only difference in the index before and after the recreation is, > >>> that the one before had IS_GENERATED = true and the one after had > >>> IS_GENERATED = false. > > >>> It seems as if the index was there and if I understand the explain > >>> syntax correctly the optimiser decided to use it, but then scanned the > >>> whole table anyways in spite of the existing Index. > > >>> Has somebody come across such a behaviour before? > > >>> -- > >>> You received this message because you are subscribed to the Google Groups > >>> "H2 Database" group. > >>> To post to this group, send email to [email protected]. > >>> To unsubscribe from this group, send email to > >>> [email protected]. > >>> For more options, visit this group > >>> athttp://groups.google.com/group/h2-database?hl=en. > > >> -- > >> You received this message because you are subscribed to the Google Groups > >> "H2 Database" group. > >> To post to this group, send email to [email protected]. > >> To unsubscribe from this group, send email to > >> [email protected]. > >> For more options, visit this group > >> athttp://groups.google.com/group/h2-database?hl=en. > > > -- > > You received this message because you are subscribed to the Google Groups > > "H2 Database" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group > > athttp://groups.google.com/group/h2-database?hl=en. > > > > h2TestCase.sql > 1KViewDownload -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
