Dan, If you can give a little more info about the queries, indexes, and table structures, I may be able to suggest why the indexes aren't being used.
The most likely culprits are: Not having updated selectivity statistics Solution: run ANALYZE; or ANALYZE SAMPLE_SIZE 0; Low Selectivity: index may not be used Solution: fake a better selectivity, if you're sure it will improve performance (test this) Use an ALTER TABLE ALTER COLUMN SELECTIVITY statement. Multi-column indices: can only be used in order specified. If first column not used in where, index won't be. Also: did improving queries bring H2 up to the same performance level as PostgreSQL? I'm looking to see if there are any hotspots or points where performance needs optimizations. I've already submitted a pair of patches to Thomas for when he returns from vacation -- these may improve start-up time and index creation speed a little bit. The speed at which strings are stored/retrieved from disk can also be improved considerably by storing their byte size along with their character count, but this could break compatibility. Cheers, Bob McGeee On Jul 28, 11:15 am, Dan Armbrust <[email protected]> wrote: > On Mon, Jul 27, 2009 at 12:44 PM, bob mcgee<[email protected]> wrote: > > ... > > Thanks for the feedback and tips. > > Not sure how I overlooked the obviousness of the "EXPLAIN" keyword :) > > As expected, I mostly have issues with a couple of poorly designed > queries which are causing tablescans. > > If we decide to officially support H2 as a new database, I'll have to > either rework them in such a way that H2 can use the indexes, or dig > into H2's code a bit, and try to figure out why it isn't using the > indexes. > > Thanks, > > Dan --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
