Re: [sqlite] Odd query plan for without rowid table

On Mon, Oct 16, 2017 at 12:28 AM, Richard Hipp wrote: > > Fixed on trunk. https://sqlite.org/src/info/ee31c043 FYI, small typo in that commit. --DD line 1885 of where.c ** Return TRUE if all of the following are true: ** ** (1) X has the same or lower cost that Y ** (2)

Re: [sqlite] Odd query plan for without rowid table

On 10/15/17, korablev wrote: > Hmm, I got this: > > Am I doing something wrong? Are you compiling with -DSQLITE_ENABLE_STAT4? The STAT4 extension is necessary in order for SQLite to distinguish between the two WHERE clauses. -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] Odd query plan for without rowid table

Hmm, I got this: SQLite version 3.21.0 2017-10-15 22:16:25 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .open test_db1 sqlite> CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID; sqlite> WITH

Re: [sqlite] Odd query plan for without rowid table

On 10/15/17, korablev wrote: > Thanks, I really appreciate so fast responce. However, example above still > doesn't work: planner prefers index 'a' instead of TABLE SCAN for WITHOUT > ROWID table, when range of query covers full table. My input is this: CREATE TABLE t1(a

Re: [sqlite] Odd query plan for without rowid table

Thanks, I really appreciate so fast responce. However, example above still doesn't work: planner prefers index 'a' instead of TABLE SCAN for WITHOUT ROWID table, when range of query covers full table. -- Sent from: http://sqlite.1065341.n5.nabble.com/

Re: [sqlite] Odd query plan for without rowid table

On 10/15/17, korablev wrote: > > Is this just a drawback of the optimizer, or things are not so simple and > there is hidden sense? > Fixed on trunk. https://sqlite.org/src/info/ee31c043 Note that the upcoming 3.21.0 release will be off of a branch so 3.21.0 will not contain

Re: [sqlite] Odd query plan for without rowid table

On 10/15/17, korablev wrote: > > Moreover, if .selecttrace and .wheretrace options are enabled, we can > notice, that for without rowid tables planer doesn't even take into account > SCAN TABLE plan: [...] >skip: * 0.01.00 t1._10 f 00240 N 0 cost

[sqlite] Odd query plan for without rowid table

Consider the following example: CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID; WITH RECURSIVE cnt(x) AS (VALUES(1000) UNION ALL SELECT x+1 FROM cnt WHERE x<2000) INSERT INTO t1(a,b,c) SELECT x, x,x FROM cnt; CREATE INDEX t1b ON t1(b); ANALYZE; EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b