Re: [sqlite] performance regression: sqlite-3.8.4.3 is not using an automatic covering index when joining with a where condition

2014-05-08 Thread Hinrichsen, John
These are all good points. Have you considered implementing hash joins for tables that join on columns that are not indexed? Typical hash joins (using the equality operator) can be performed in O(N) time without indexes. Because hash joins evaluate each row just once, they might also permit us

Re: [sqlite] performance regression: sqlite-3.8.4.3 is not using an automatic covering index when joining with a where condition

2014-05-07 Thread Richard Hipp
On Wed, May 7, 2014 at 6:58 PM, Hinrichsen, John wrote: > On Wed, May 7, 2014 at 5:21 PM, Richard Hipp wrote: > > > > > Do you have a database file where the 3.8.4.3 query plan really is > slower? > > Can you please run ANALYZE on that database and send us

Re: [sqlite] performance regression: sqlite-3.8.4.3 is not using an automatic covering index when joining with a where condition

2014-05-07 Thread Hinrichsen, John
On Wed, May 7, 2014 at 5:21 PM, Richard Hipp wrote: > > Do you have a database file where the 3.8.4.3 query plan really is slower? > Can you please run ANALYZE on that database and send us the content of the > "sqlite_stat1" table? > > It is true that if we add the analyze, the

Re: [sqlite] performance regression: sqlite-3.8.4.3 is not using an automatic covering index when joining with a where condition

2014-05-07 Thread Richard Hipp
On Wed, May 7, 2014 at 4:51 PM, Hinrichsen, John wrote: > $ sqlite3 > SQLite version 3.7.17 2013-05-20 00:56:22 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> CREATE TABLE x AS SELECT 1 AS a, 1 AS b; > sqlite> CREATE INDEX ix ON x

[sqlite] performance regression: sqlite-3.8.4.3 is not using an automatic covering index when joining with a where condition

2014-05-07 Thread Hinrichsen, John
$ sqlite3 SQLite version 3.7.17 2013-05-20 00:56:22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE x AS SELECT 1 AS a, 1 AS b; sqlite> CREATE INDEX ix ON x (a); sqlite> CREATE TABLE y AS SELECT 1 AS b; sqlite> EXPLAIN QUERY PLAN SELECT * FROM x INNER