64-column covering index limit clarification

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

64-column covering index limit clarification

deon
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 64-column covering index limit clarification

Shawn Wagner
Does a normal non-partial index make a difference in the query plan?

On Thu, Oct 18, 2018, 12:30 PM Deon Brewis <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 64-column covering index limit clarification

Deon Brewis
Yes a non partial index beyond column 64 works as I would expect.

- Deon

> On Oct 18, 2018, at 12:34 PM, Shawn Wagner <[hidden email]> wrote:
>
> Does a normal non-partial index make a difference in the query plan?
>
>> On Thu, Oct 18, 2018, 12:30 PM Deon Brewis <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 64-column covering index limit clarification

Shawn Wagner
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 <[hidden email]> 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 <[hidden email]>
> wrote:
> >
> > Does a normal non-partial index make a difference in the query plan?
> >
> >> On Thu, Oct 18, 2018, 12:30 PM Deon Brewis <[hidden email]> 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
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 64-column covering index limit clarification

Dan Kennedy-4
In reply to this post by deon
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users