Fwd: SELECT Max(IndexedField) doesn't use partial index

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Fwd: SELECT Max(IndexedField) doesn't use partial index

Paul van Helden
Hi,

I use a lot of indexes on fields that typically contain lots of NULLs, so
the WHERE NOT NULL partial indexing seems very useful.

However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs. a
normal index, SQLite won't use the index to find Max(IndexedField) if it is
a partial index.

Is this an optimization opportunity? I understand that other kinds of
partial indexes might exclude possible Min or Max values, but a NOT NULL
index would be fine for mins, maxes and most other things?

Regards,

Paul.
_______________________________________________
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: Fwd: SELECT Max(IndexedField) doesn't use partial index

Scott Robison-2
On Mon, May 8, 2017 at 11:40 AM, Paul van Helden <[hidden email]> wrote:

> Hi,
>
> I use a lot of indexes on fields that typically contain lots of NULLs, so
> the WHERE NOT NULL partial indexing seems very useful.
>
> However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs. a
> normal index, SQLite won't use the index to find Max(IndexedField) if it is
> a partial index.
>
> Is this an optimization opportunity? I understand that other kinds of
> partial indexes might exclude possible Min or Max values, but a NOT NULL
> index would be fine for mins, maxes and most other things?

This may be an optimization opportunity, but you can easily force the
use of that index by stating WHERE NOT NULL in the select query
itself. For example:

CREATE TABLE a(b);
CREATE INDEX ab on a(b) where b is not null;

sqlite> explain query plan select max(b) from a;
0|0|0|SEARCH TABLE a
sqlite> explain query plan select max(b) from a where b is not null;
0|0|0|SEARCH TABLE a USING COVERING INDEX ab

>
> Regards,
>
> Paul.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
Scott Robison
_______________________________________________
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: Fwd: SELECT Max(IndexedField) doesn't use partial index

Paul van Helden
On Mon, May 8, 2017 at 8:20 PM, Scott Robison <[hidden email]>
wrote:

> On Mon, May 8, 2017 at 11:40 AM, Paul van Helden <[hidden email]>
> wrote:
> > Hi,
> >
> > I use a lot of indexes on fields that typically contain lots of NULLs, so
> > the WHERE NOT NULL partial indexing seems very useful.
> >
> > However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs.
> a
> > normal index, SQLite won't use the index to find Max(IndexedField) if it
> is
> > a partial index.
> >
> > Is this an optimization opportunity? I understand that other kinds of
> > partial indexes might exclude possible Min or Max values, but a NOT NULL
> > index would be fine for mins, maxes and most other things?
>
> This may be an optimization opportunity, but you can easily force the
> use of that index by stating WHERE NOT NULL in the select query
> itself. For example:
>
> CREATE TABLE a(b);
> CREATE INDEX ab on a(b) where b is not null;
>
> sqlite> explain query plan select max(b) from a;
> 0|0|0|SEARCH TABLE a
> sqlite> explain query plan select max(b) from a where b is not null;
> 0|0|0|SEARCH TABLE a USING COVERING INDEX ab
>
> >


Thanks Scott,

That works and fully solves my problem!

Regards,

Paul.
_______________________________________________
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: Fwd: SELECT Max(IndexedField) doesn't use partial index

jose isaias cabrera-3
In reply to this post by Scott Robison-2

Scott Robison wrote...
On Mon, May 8, 2017 at 11:40 AM, Paul van Helden <[hidden email]>
wrote:

>> Hi,
>>
>> I use a lot of indexes on fields that typically contain lots of NULLs, so
>> the WHERE NOT NULL partial indexing seems very useful.
>>
>> However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs.
>> a
>> normal index, SQLite won't use the index to find Max(IndexedField) if it
>> is
>> a partial index.
>>
>> Is this an optimization opportunity? I understand that other kinds of
>> partial indexes might exclude possible Min or Max values, but a NOT NULL
>> index would be fine for mins, maxes and most other things?

>This may be an optimization opportunity, but you can easily force the
>use of that index by stating WHERE NOT NULL in the select query
>itself. For example:
>
>CREATE TABLE a(b);
>CREATE INDEX ab on a(b) where b is not null;
>
>sqlite> explain query plan select max(b) from a;
>0|0|0|SEARCH TABLE a
>sqlite> explain query plan select max(b) from a where b is not null;
>0|0|0|SEARCH TABLE a USING COVERING INDEX ab

So, a newbie question here...  What happens if you have,

INDEX ab on a(b);

and no "where b is not null".  Doesn't the INDEX takes care of all those
nulls and the same outcome would result?

thanks.


_______________________________________________
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: Fwd: SELECT Max(IndexedField) doesn't use partial index

Scott Robison-2
On May 9, 2017 9:07 PM, "jose isaias cabrera" <[hidden email]> wrote:


Scott Robison wrote...

On Mon, May 8, 2017 at 11:40 AM, Paul van Helden <[hidden email]>
wrote:

> Hi,
>>
>> I use a lot of indexes on fields that typically contain lots of NULLs, so
>> the WHERE NOT NULL partial indexing seems very useful.
>>
>> However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs. a
>> normal index, SQLite won't use the index to find Max(IndexedField) if it
>> is
>> a partial index.
>>
>> Is this an optimization opportunity? I understand that other kinds of
>> partial indexes might exclude possible Min or Max values, but a NOT NULL
>> index would be fine for mins, maxes and most other things?
>>
>
This may be an optimization opportunity, but you can easily force the

> use of that index by stating WHERE NOT NULL in the select query
> itself. For example:
>
> CREATE TABLE a(b);
> CREATE INDEX ab on a(b) where b is not null;
>
> sqlite> explain query plan select max(b) from a;
> 0|0|0|SEARCH TABLE a
> sqlite> explain query plan select max(b) from a where b is not null;
> 0|0|0|SEARCH TABLE a USING COVERING INDEX ab
>

So, a newbie question here...  What happens if you have,

INDEX ab on a(b);

and no "where b is not null".  Doesn't the INDEX takes care of all those
nulls and the same outcome would result?

thanks.


Yes, it might just need to walk through a larger data set. If nulls are
rare, there probably wouldn't be much of a difference (not in front of a
computer to check). If the column was mostly nulls, and a lot of rows, it
might be a lot faster.
_______________________________________________
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: Fwd: SELECT Max(IndexedField) doesn't use partial index

jose isaias cabrera-3

Scott Robison wrote...

>Yes, it might just need to walk through a larger data set. If nulls are
>rare, there probably wouldn't be much of a difference (not in front of a
>computer to check). If the column was mostly nulls, and a lot of rows, it
>might be a lot faster.

Hmmm... Thanks.  I have lots of INDEXes to re-INDEX.  Thanks...

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users