You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ignite.apache.org by Valentin Kulichenko <va...@gmail.com> on 2017/01/03 19:59:09 UTC
min()/max() SQL and indexes
Folks,
Does anyone know if index is supposed to be used when a query like below is
executed?
select min(id) from MyValue
select max(id) from MyValue
I tried it and execution time doesn't seem to depend on whether I create an
index for 'id' field or not. It always takes around 2 seconds with only
5,000,000 records on a single node. Result is the same for min() and max(),
so index order doesn't seem to have affect either.
Execution plan shows that index is used though:
SELECT
MAX(ID) AS __C0
FROM "test".MYVALUE
/* "test"."id_idx" */
SELECT
MAX(__C0) AS __C0
FROM PUBLIC.__T0
/* "test"."merge_scan" */
Sergi, can you please clarify what is the expected behavior here?
-Val
Re: min()/max() SQL and indexes
Posted by Denis Magda <dm...@apache.org>.
https://issues.apache.org/jira/browse/IGNITE-4524 <https://issues.apache.org/jira/browse/IGNITE-4524>
Put it for 2.0. Sergi, assigned the ticket on you so that you can provide implementations details or take care of it personally. Whatever you prefer.
—
Denis
> On Jan 4, 2017, at 8:37 AM, Dmitriy Setrakyan <ds...@apache.org> wrote:
>
> Is there a ticket for this issue?
>
> On Wed, Jan 4, 2017 at 3:11 AM, Sergi Vladykin <se...@gmail.com>
> wrote:
>
>> Our indexes do not support this optimization right now.
>>
>> It is not that hard to implement it, I'd suggest to plan it for
>> 2.0+PageMemory, because otherwise we'll have to implement it twice with
>> conflicts.
>>
>> Sergi
>>
>>
>>
>> 2017-01-03 22:59 GMT+03:00 Valentin Kulichenko <
>> valentin.kulichenko@gmail.com>:
>>
>>> Folks,
>>>
>>> Does anyone know if index is supposed to be used when a query like below
>>> is executed?
>>>
>>> select min(id) from MyValue
>>> select max(id) from MyValue
>>>
>>> I tried it and execution time doesn't seem to depend on whether I create
>>> an index for 'id' field or not. It always takes around 2 seconds with
>> only
>>> 5,000,000 records on a single node. Result is the same for min() and
>> max(),
>>> so index order doesn't seem to have affect either.
>>>
>>> Execution plan shows that index is used though:
>>>
>>> SELECT
>>> MAX(ID) AS __C0
>>> FROM "test".MYVALUE
>>> /* "test"."id_idx" */
>>>
>>> SELECT
>>> MAX(__C0) AS __C0
>>> FROM PUBLIC.__T0
>>> /* "test"."merge_scan" */
>>>
>>> Sergi, can you please clarify what is the expected behavior here?
>>>
>>> -Val
>>>
>>
Re: min()/max() SQL and indexes
Posted by Dmitriy Setrakyan <ds...@apache.org>.
Is there a ticket for this issue?
On Wed, Jan 4, 2017 at 3:11 AM, Sergi Vladykin <se...@gmail.com>
wrote:
> Our indexes do not support this optimization right now.
>
> It is not that hard to implement it, I'd suggest to plan it for
> 2.0+PageMemory, because otherwise we'll have to implement it twice with
> conflicts.
>
> Sergi
>
>
>
> 2017-01-03 22:59 GMT+03:00 Valentin Kulichenko <
> valentin.kulichenko@gmail.com>:
>
> > Folks,
> >
> > Does anyone know if index is supposed to be used when a query like below
> > is executed?
> >
> > select min(id) from MyValue
> > select max(id) from MyValue
> >
> > I tried it and execution time doesn't seem to depend on whether I create
> > an index for 'id' field or not. It always takes around 2 seconds with
> only
> > 5,000,000 records on a single node. Result is the same for min() and
> max(),
> > so index order doesn't seem to have affect either.
> >
> > Execution plan shows that index is used though:
> >
> > SELECT
> > MAX(ID) AS __C0
> > FROM "test".MYVALUE
> > /* "test"."id_idx" */
> >
> > SELECT
> > MAX(__C0) AS __C0
> > FROM PUBLIC.__T0
> > /* "test"."merge_scan" */
> >
> > Sergi, can you please clarify what is the expected behavior here?
> >
> > -Val
> >
>
Re: min()/max() SQL and indexes
Posted by Sergi Vladykin <se...@gmail.com>.
Our indexes do not support this optimization right now.
It is not that hard to implement it, I'd suggest to plan it for
2.0+PageMemory, because otherwise we'll have to implement it twice with
conflicts.
Sergi
2017-01-03 22:59 GMT+03:00 Valentin Kulichenko <
valentin.kulichenko@gmail.com>:
> Folks,
>
> Does anyone know if index is supposed to be used when a query like below
> is executed?
>
> select min(id) from MyValue
> select max(id) from MyValue
>
> I tried it and execution time doesn't seem to depend on whether I create
> an index for 'id' field or not. It always takes around 2 seconds with only
> 5,000,000 records on a single node. Result is the same for min() and max(),
> so index order doesn't seem to have affect either.
>
> Execution plan shows that index is used though:
>
> SELECT
> MAX(ID) AS __C0
> FROM "test".MYVALUE
> /* "test"."id_idx" */
>
> SELECT
> MAX(__C0) AS __C0
> FROM PUBLIC.__T0
> /* "test"."merge_scan" */
>
> Sergi, can you please clarify what is the expected behavior here?
>
> -Val
>