optimizing min/max with calculated index?

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

optimizing min/max with calculated index?

wmertens
I have a table with an indexed json field, and I want to know the maximum
value of that field.

    create table events(id integer primary key, json JSON);
    create index t on events(json_extract(json, '$.ts));

If I do

    select max(json_extract(json, '$.ts')) from events;

it does a table scan with the index, as far as I can decode the query plan
it's doing a max() on every row. It takes 150ms on my data.

If instead I do

    select json_extract(json, '$.ts') from events order by
 json_extract(json, '$.ts' ) desc limit 1;

I get the same result in 6ms.

The same queries on a real field, like `id`, always return in 0-2ms.

Is this an optimization opportunity for calculated indexes?
_______________________________________________
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: optimizing min/max with calculated index?

Richard Hipp-3
On 10/26/17, Wout Mertens <[hidden email]> wrote:

> I have a table with an indexed json field, and I want to know the maximum
> value of that field.
>
>     create table events(id integer primary key, json JSON);
>     create index t on events(json_extract(json, '$.ts));
>
> If I do
>
>     select max(json_extract(json, '$.ts')) from events;
>
> it does a table scan with the index,
>
> Is this an optimization opportunity for calculated indexes?

It is.  There is a min/max optimization to deal with this situation,
but it currently does not know about indexes on expressions.

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: optimizing min/max with calculated index?

Jens Alfke-2


> On Oct 26, 2017, at 6:11 AM, Richard Hipp <[hidden email]> wrote:
>
>> Is this an optimization opportunity for calculated indexes?
>
> It is.  There is a min/max optimization to deal with this situation,
> but it currently does not know about indexes on expressions.


+1. The project I work on (Couchbase Lite 2) also uses indexes on expressions and would benefit from this min/max optimization.

—Jens
_______________________________________________
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: optimizing min/max with calculated index?

Richard Hipp-3
Y'all please try the lastest trunk version of SQLite for me and let me
know if it works better for you.  Thanks.

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: optimizing min/max with calculated index?

wmertens
Works great for me!

On Thu, Oct 26, 2017 at 10:13 PM Richard Hipp <[hidden email]> wrote:

> Y'all please try the lastest trunk version of SQLite for me and let me
> know if it works better for you.  Thanks.
>
> --
> D. Richard Hipp
> [hidden email]
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users