Expression Indexes - can I project the expression value from the index?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Expression Indexes - can I project the expression value from the index?

Deon Brewis
Let's say I have an expression index:

CREATE INDEX FOOINDEX on FOO(func(col))

And then I use that index:
SELECT bar FROM FOO INDEXED BY FOOINDEX WHERE func(col) = 42;

I can see that it will use the stored value, since if I put a breakpoint on func, it won't hit. Good and well.


HOWEVER, if I run:
SELECT func(col) FROM FOO INDEXED BY FOOINDEX WHERE func(col) = 42;


It will execute the breakpoint on func(col) for every row in the table, even though FOOINDEX is a covered index wrt. that query.

Is there a way to avoid this? I would just like to return the value that's already stored in the index.

- 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
|  
Report Content as Inappropriate

Re: Expression Indexes - can I project the expression value from the index?

Richard Hipp-3
On 8/31/16, Deon Brewis <[hidden email]> wrote:
>
> CREATE INDEX FOOINDEX on FOO(func(col))
>
> HOWEVER, if I run:
> SELECT func(col) FROM FOO INDEXED BY FOOINDEX WHERE func(col) = 42;
>
> It will execute the breakpoint on func(col) for every row in the table, even
> though FOOINDEX is a covered index wrt. that query.

We will take this as an enhancement request.

--
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
|  
Report Content as Inappropriate

Re: Expression Indexes - can I project the expression value from the index?

Deon Brewis
Thanks! I'll be willing to test this beta once it's available.

One more thing - I think there is a bug here in the query planner if there is an indexed expression in an index. It doesn't seem to think an index is ever covering if the inputs to the expression isn't in the index as well.

E.g.
CREATE TABLE Foo
(
   col1 int,  
   col2 int
);

CREATE INDEX FOOINDEX on FOO(abs(col1), col2);

SELECT col2 from Foo indexed by FOOINDEX where abs(col1) = 42;

In my opinion the index is covering the query above - even without col1 in the index. col1 isn't used in the select - only abs(col1) is used. It can't possibly be using col1 anywhere because it would hit a breakpoint I have. However, the query isn't executed as covered:


explain query plan SELECT col2 from Foo indexed by FOOINDEX where abs(col1) = 42 ;
RecNo selectid order from detail                                          
----- -------- ----- ---- ------------------------------------------------
    1 0        0     0    SEARCH TABLE Foo USING INDEX FOOINDEX (<expr>=?)

-- NOTE: You can also see in the opcodes it's actually doing an OpenRead cursor to the main table as well, but then not ever using it:

explain SELECT col2 from Foo indexed by FOOINDEX where abs(col1) = 42;
RecNo addr opcode      p1 p2 p3 p4               p5 comment
----- ---- ----------- -- -- -- ---------------- -- -------
    1 0    Init        0  14 0                   00 (null)  
    2 1    OpenRead    0  2  0  2                00 (null)  
    3 2    OpenRead    1  3  0  k(3,nil,nil,nil) 02 (null)  
    4 3    Integer     42 1  0                   00 (null)  
    5 4    SeekGE      1  11 1  1                00 (null)  
    6 5    IdxGT       1  11 1  1                00 (null)  
    7 6    IdxRowid    1  2  0                   00 (null)  
    8 7    Seek        0  2  0                   00 (null)  
    9 8    Column      1  1  3                   00 (null)  
   10 9    ResultRow   3  1  0                   00 (null)  
   11 10   Next        1  5  0                   00 (null)  
   12 11   Close       0  0  0                   00 (null)  
   13 12   Close       1  0  0                   00 (null)  
   14 13   Halt        0  0  0                   00 (null)  
   15 14   Transaction 0  0  16 0                01 (null)  
   16 15   TableLock   0  2  0  Foo              00 (null)  
   17 16   Goto        0  1  0                   00 (null)  


If I however change the index to:
CREATE INDEX FOOINDEX on FOO(abs(col1), col1, col2)

Then it becomes a covering index:

explain query plan SELECT col2 from Foo indexed by FOOINDEX where abs(col1) = 42
RecNo selectid order from detail                                                    
----- -------- ----- ---- ---------------------------------------------------------
    1 0        0     0    SEARCH TABLE Foo USING COVERING INDEX FOOINDEX (<expr>=?)


- Deon

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Wednesday, August 31, 2016 7:34 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Expression Indexes - can I project the expression value from the index?

On 8/31/16, Deon Brewis <[hidden email]> wrote:
>
> CREATE INDEX FOOINDEX on FOO(func(col))
>
> HOWEVER, if I run:
> SELECT func(col) FROM FOO INDEXED BY FOOINDEX WHERE func(col) = 42;
>
> It will execute the breakpoint on func(col) for every row in the
> table, even though FOOINDEX is a covered index wrt. that query.

We will take this as an enhancement request.

--
D. Richard Hipp
[hidden email]
_______________________________________________
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
|  
Report Content as Inappropriate

Re: Expression Indexes - can I project the expression value from the index?

Richard Hipp-3
On 8/31/16, Deon Brewis <[hidden email]> wrote:
> Thanks! I'll be willing to test this beta once it's available.
>
> One more thing - I think there is a bug here in the query planner

I'm pedantic about this because it is important:  What you describe is
not a bug.  A bug means it gets the wrong answer.  Getting the correct
answer more slowly than you would like is not a bug - that's an
optimization opportunity.  So what you describe (if I can verify it,
which seems likely, but which I have not yet attempted to do) is an
optimization opportunity, not a bug.

Thanks for reporting it.  I will look into it when I get a chance.

--
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
|  
Report Content as Inappropriate

Re: Expression Indexes - can I project the expression value from the index?

Deon Brewis
Sorry! My bad.

I work in one of those shops where everything from a feature request, to a task, to a customer support issue, to an actual software defect is referred to as a "bug". I forget it doesn't always translate.

Thanks for looking into it!
- Deon

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Wednesday, August 31, 2016 4:15 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Expression Indexes - can I project the expression value from the index?

On 8/31/16, Deon Brewis <[hidden email]> wrote:
> Thanks! I'll be willing to test this beta once it's available.
>
> One more thing - I think there is a bug here in the query planner

I'm pedantic about this because it is important:  What you describe is not a bug.  A bug means it gets the wrong answer.  Getting the correct answer more slowly than you would like is not a bug - that's an optimization opportunity.  So what you describe (if I can verify it, which seems likely, but which I have not yet attempted to do) is an optimization opportunity, not a bug.

Thanks for reporting it.  I will look into it when I get a chance.

--
D. Richard Hipp
[hidden email]
_______________________________________________
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
|  
Report Content as Inappropriate

Re: Expression Indexes - can I project the expression value from the index?

Deon Brewis
In reply to this post by Richard Hipp-3
Hi,

Just following up - is this tracked somewhere in a feature/bug database?


I have to do some interesting gymnastics to work around this all the time, since the query optimizer seems to think it can read covered values from an expression index, but during execution it doesn't.

e.g. On the example below, a query like:
SELECT func(col), count(*) FROM FOO GROUP BY func(col)

Will try to use FOOINDEX, but that's a really bad index since it then proceeds to executes func(col) on every row from the index, but FOOINDEX doesn't actually contain col, only the result - func(col).

- Deon

-----Original Message-----
> On 8/31/16, Deon Brewis <[hidden email]> wrote:
>
> Let's say I have an expression index:

> CREATE INDEX FOOINDEX on FOO(func(col))
>
> And then I use that index:
> SELECT bar FROM FOO INDEXED BY FOOINDEX WHERE func(col) = 42;
>
> I can see that it will use the stored value, since if I put a breakpoint on func, it won't hit. Good and well.
>>
> HOWEVER, if I run:
> SELECT func(col) FROM FOO INDEXED BY FOOINDEX WHERE func(col) = 42
>
> It will execute the breakpoint on func(col) for every row in the
> table, even though FOOINDEX is a covered index wrt. that query.
>
> Is there a way to avoid this? I would just like to return the value that's already stored in the index.


We will take this as an enhancement request.

--
D. Richard Hipp
[hidden email]
_______________________________________________
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
|  
Report Content as Inappropriate

Re: Expression Indexes - can I project the expression value from the index?

Richard Hipp-3
On 4/5/17, Deon Brewis <[hidden email]> wrote:
> Hi,
>
> Just following up - is this tracked somewhere in a feature/bug database?
>

We are aware of the feature request and discussed it internally just
yesterday.  It is non-trivial to implement.
--
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
|  
Report Content as Inappropriate

Re: Expression Indexes - can I project the expression value from the index?

Deon Brewis
Thanks so much for looking into it!

- Deon

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Wednesday, April 5, 2017 7:30 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Expression Indexes - can I project the expression value from the index?

On 4/5/17, Deon Brewis <[hidden email]> wrote:
> Hi,
>
> Just following up - is this tracked somewhere in a feature/bug database?
>

We are aware of the feature request and discussed it internally just yesterday.  It is non-trivial to implement.
--
D. Richard Hipp
[hidden email]
_______________________________________________
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
Loading...