On Tue, Jul 28, 2009 at 10:53 AM, bob mcgee<[email protected]> wrote: > > 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;
How or when does H2 decide to update it's own statistics? Do you need to manually update them after you create a new table, indexes, and populate the table? How frequently would one need rerun Analyze if the table is growing? > > 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. 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. If someone bothered to create the index, they probably intended for the database to use the index where possible. I've had this issue with PostgreSQL in the past too... I used to have to change a setting that basically told PosgreSQL if the index exists, use it. Otherwise, it had a tendency to make bad decisions when it thought a table had 10 rows in it, but in reality, it now had 50,000 (but the stats were out of date). Or worse, if you created a prepared statement when a table had 10 rows in it, then populated the table with thousands of rows, then used the prepared statement, PostgreSQL would use the query plan that was created at the time the prepared statement was made - which would conclude that a table scan was always the thing to do, since the table was small. > > Multi-column indices: can only be used in order specified. > If first column not used in where, index won't be. If a table has columns A, B, C, And the primary key is A, B - I assume it creates an index on A, B. 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)? > > Also: did improving queries bring H2 up to the same performance level > as PostgreSQL? I haven't been able to rework the queries yet... its an issue with a legacy schema design that doesn't handle the question being asked in a very straightforward way. Probably won't put to much more work into it unless I get a go-ahead from management saying that we want to officially support H2. My query looks something like this: select * from a, b where a.zz=b.zz and ((a.xx='string' and a.yy<>'string' ) or (a.yy='string' and a.xx<>'string' and b.qq='1')) There are indexes for: b.zz a.zz a.xx, a,yy a.yy Postgres starts this query by doing index scans for each of half of the OR. H2 starts with a table scan on a. But this isn't a big deal to me at the moment. I'd like to replace the query anyway, and it's likely going to be a while before I get the go-ahead to integrate support for H2. Until then, I don't have a lot of cycles to test alternatives. I was just doing a cursory test of various embedded databases to see what is possible with our current code. 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 -~----------~----~----~----~------~----~------~--~---
