Dan, I've got a couple answers for now, and maybe more after source diving.
On Jul 28, 5:41 pm, Dan Armbrust <[email protected]> wrote: > On Tue, Jul 28, 2009 at 10:53 AM, bob mcgee<[email protected]> wrote: > How or when does H2 decide to update it's own statistics? It doesn't... yet (it's in the Roadmap for future develipment). > Do you need to manually update them after you create a new table, > indexes, and populate the table? After index creation, you should run "ANALYZE;" to gather stats for the index. It might be enough to assign a selectivity b/w 2 and 99 to the column though. If data changes a lot, it's a good idea to run analyze periodically to ensure the best index is used. > How frequently would one need rerun Analyze if the table is growing? I believe just if you think another index might become better after updates? I'm not sure though -- the initial run to guarantee index use is probably most important. > I've never understood the lengths that databases sometimes go to to > avoid using the indexes in situations where (it thinks) the table is > small. IANTM (I Am Not Thomas Mueller) but my understanding is that this is actually desirable, assuming your DB has a join algorithm (besides nested loop join) which can operate without indices. In theory, table scans should be able to iterate through rows much faster than B-trees because of the data structures involved and opportunity for (more) sequential I/O patterns. Jumping through 2 useless items for every used one in table scan might actually be faster than using an index to cut the search space to 1/3 and then iterating (with the index). Heck if I know though, query planning is COMPLEX. The H2 planner is fancy enough to use genetic algorithms, and still doesn't optimize some things properly, such as IN(...) conditions. > PostgreSQL would use the query plan that was > created at the time the prepared statement was made Aww, be fair! The query planner should be smart, not psychic -- we wouldn't want Skynet level craziness happening, now would we? > If I then query on just A - will it be able to use this index? Or > will it only be used for a query on A, B (in that order)? It should. > [Query] I think that should work fine with indices in H2 (in theory), once selectivity is set. You might try the following though, where the joins are more explicit -- might make things easier on the other DBMSes too, depending. SELECT * FROM A INNER JOIN B ON A.zz=B.zz AND (A.xx='string' AND A.yy <> 'string') OR (A.yy='string' AND A.xx <>'string' AND B.qq='1'); alternative: SELECT * FROM a INNER JOIN b ON a.zz=b.zz WHERE (a.xx='string' AND b.yy <> 'string') OR (a.xx <>'string' AND a.yy='string' AND b.qq='1'); Cheers, Bob McGee --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
