Re: [sqlite] 64-column covering index limit clarification
On 10/19/2018 02:30 AM, Deon Brewis wrote: Hi, I seem to have run into a limit where SQLITE doesn't use an index correctly if an indexed column is over the 64th column in the table. It's a partial index like: CREATE INDEX idx ON table(A, B DESC, C, D) WHERE A > 0 Where A and B are columns 70 and 72 on 'table'. I know about the 64-column limitation for covering indexes: http://sqlite.1065341.n5.nabble.com/Max-of-63-columns-for-a-covering-index-to-work-td68945.html However, this isn't a covering index, it's a partial index. But it seems to run into the same limit. Even if I forced in the index into a query it still does a "USE TEMP B-TREE" at the end to satisfy a simple "ORDER BY A, B DESC" query. After I re-ordered the table, it magically started working. Can you post an SQL script that demonstrates the problem? Running the script below here, the partial index is used to optimize the ORDER BY in the query. Thanks, Dan. CREATE TABLE t1( c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34, c35, c36, c37, c38, c39, c40, c41, c42, c43, c44, c45, c46, c47, c48, c49, c50, c51, c52, c53, c54, c55, c56, c57, c58, c59, c60, c61, c62, c63, c64, c65, c66, c67, c68, c69, c70, c71, c72, c73, c74, c75, c76, c77, c78, c79, c80, c81, c82, c83, c84, c85, c86, c87, c88, c89 ); CREATE INDEX i1 ON t1(c80, c81 DESC, c82, c83) WHERE c80>0; EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c80>0 ORDER BY c80, c81 DESC; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64-column covering index limit clarification
Okay, next question: Does the query you're testing this with obey the requirements needed to use the partial index (see https://sqlite.org/partialindex.html#queries_using_partial_indexes)? On Thu, Oct 18, 2018 at 9:43 PM Deon Brewis wrote: > Yes a non partial index beyond column 64 works as I would expect. > > - Deon > > > On Oct 18, 2018, at 12:34 PM, Shawn Wagner > wrote: > > > > Does a normal non-partial index make a difference in the query plan? > > > >> On Thu, Oct 18, 2018, 12:30 PM Deon Brewis wrote: > >> > >> Hi, > >> > >> I seem to have run into a limit where SQLITE doesn't use an index > >> correctly if an indexed column is over the 64th column in the table. > It's a > >> partial index like: > >> > >> CREATE INDEX idx ON > >> table(A, B DESC, C, D) > >> WHERE A > 0 > >> > >> Where A and B are columns 70 and 72 on 'table'. > >> > >> I know about the 64-column limitation for covering indexes: > >> > >> > http://sqlite.1065341.n5.nabble.com/Max-of-63-columns-for-a-covering-index-to-work-td68945.html > >> > >> However, this isn't a covering index, it's a partial index. But it seems > >> to run into the same limit. Even if I forced in the index into a query > it > >> still does a "USE TEMP B-TREE" at the end to satisfy a simple "ORDER BY > A, > >> B DESC" query. After I re-ordered the table, it magically started > working. > >> > >> Is there any better documentation anywhere (other than the archive) of > all > >> of the cases to which the 64-column limit applies? > >> > >> - Deon > >> > >> ___ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64-column covering index limit clarification
Yes a non partial index beyond column 64 works as I would expect. - Deon > On Oct 18, 2018, at 12:34 PM, Shawn Wagner wrote: > > Does a normal non-partial index make a difference in the query plan? > >> On Thu, Oct 18, 2018, 12:30 PM Deon Brewis wrote: >> >> Hi, >> >> I seem to have run into a limit where SQLITE doesn't use an index >> correctly if an indexed column is over the 64th column in the table. It's a >> partial index like: >> >> CREATE INDEX idx ON >> table(A, B DESC, C, D) >> WHERE A > 0 >> >> Where A and B are columns 70 and 72 on 'table'. >> >> I know about the 64-column limitation for covering indexes: >> >> http://sqlite.1065341.n5.nabble.com/Max-of-63-columns-for-a-covering-index-to-work-td68945.html >> >> However, this isn't a covering index, it's a partial index. But it seems >> to run into the same limit. Even if I forced in the index into a query it >> still does a "USE TEMP B-TREE" at the end to satisfy a simple "ORDER BY A, >> B DESC" query. After I re-ordered the table, it magically started working. >> >> Is there any better documentation anywhere (other than the archive) of all >> of the cases to which the 64-column limit applies? >> >> - Deon >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64-column covering index limit clarification
Does a normal non-partial index make a difference in the query plan? On Thu, Oct 18, 2018, 12:30 PM Deon Brewis wrote: > Hi, > > I seem to have run into a limit where SQLITE doesn't use an index > correctly if an indexed column is over the 64th column in the table. It's a > partial index like: > > CREATE INDEX idx ON > table(A, B DESC, C, D) > WHERE A > 0 > > Where A and B are columns 70 and 72 on 'table'. > > I know about the 64-column limitation for covering indexes: > > http://sqlite.1065341.n5.nabble.com/Max-of-63-columns-for-a-covering-index-to-work-td68945.html > > However, this isn't a covering index, it's a partial index. But it seems > to run into the same limit. Even if I forced in the index into a query it > still does a "USE TEMP B-TREE" at the end to satisfy a simple "ORDER BY A, > B DESC" query. After I re-ordered the table, it magically started working. > > Is there any better documentation anywhere (other than the archive) of all > of the cases to which the 64-column limit applies? > > - Deon > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
