On Fri, Sep 24, 2010 at 8:03 PM, Charles Samuels <[email protected]>wrote:
> > Greetings, > > With the sqlite database here: > http://www.derkarl.org/~charles/massive_performance_regression.bz2<http://www.derkarl.org/%7Echarles/massive_performance_regression.bz2> > > There is a massive performance regression between 3.7.1 and 3.7.2, when > compared to 3.6.23.1. > You have a very unusual data distribution in your tables. SQLite does not know this and so it chooses a query plan (in 3.7.1 and later) that assumes a different and more typical data distribution. The new query plan works better for most cases, but is much worse in your unusual case. You can remedy this by running ANALYZE. The ANALYZE command gathers statistics about your data and makes them available to the query planner to help it choose better plans based on your specific data profiles. I also recommend that you drop the redundant page_table_index since it duplicates the primary key index on the page_table. > > The following query runs orders of magnitude slower than it did in the > previous version: > > > select * > from object_formats join > page_table on page_table.version=3 and page_table.objectid = > object_formats.objectid ; > > Thanks, > > Charles > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
