Re: SQL exeucution in ignite

[Denis Mekhanikov <dm...@gmail.com>]

Prasad,

> 1) What happens if the index is present filter column and sort column?
If there is an index on a needed column, then Ignite doesn't have to sort
data again. It just can use the index.

> If ignite pulls all data in heap then what the use of page size property
Page size specifies the size of batches of rows, that are transferred
between nodes and sent to the query cursor.
But in cases, when one page is not enough to execute a query, more data may
be pulled to heap and transferred to a reducer node.

> 2) Does it not use disk space/file system like in external merge sort to
sort the entries or to filter the records based on where clause?
Ignite doesn't implement sorting in external memory, so all data should be
available on heap to perform it.

> 3) Is there anyway to keep the memory foot print low in such scenario?
You can change your query in the following way:

    select * from CACHE_1
where filter1=? and
filter2=? and
filter3=?
order by column1 asc, column2 desc

and create a compound index (column1 asc, column2 desc) on this table.

Denis

вт, 31 июл. 2018 г. в 19:08, Prasad Bhalerao <pr...@gmail.com>:

> 1) What happens if the index is present filter column and sort column?
>
> If ignite pulls all data in heap then what the use of page size property.
> Even if I set it to 500 ignite is going to pull all 3 million records in
> heap space.
>
> 2) Does it not use disk space/file system like in external merge sort to
> sort the entries or to filter the records based on where clause?
>
>
> 3) Is there anyway to keep the memory foot print low in such scenario?
>
> On Tue, Jul 31, 2018, 9:21 PM Denis Mekhanikov <dm...@gmail.com>
> wrote:
>
>> Prasad,
>>
>> Your understanding is correct. All entries will be pulled to heap and
>> sorted there.
>> In general, *SELECT * FROM table ORDER BY column *query processes all
>> data on heap, except for the cases, when there is an index on needed
>> columns.
>>
>> Denis
>>
>> вт, 31 июл. 2018 г. в 8:44, Prasad Bhalerao <prasadbhalerao1983@gmail.com
>> >:
>>
> 1) Does ignite use heap space to do the required processing as per the
>>> execution plan to retrieve the data from cache which is in off heap memory?
>>>
>>> 2) Please check the following SQL.  This CACHE_1 has around 50 million
>>> entries.
>>>
>>> select * from (
>>> select * from CACHE_1 where filter1 = ?  (Q1)
>>> UNION
>>> select * from CACHE_1 where filter2 = ?  (Q2)
>>> UNION
>>> select * from CACHE_1 where filter3 = ?   (Q3)
>>> ) order by column1 asc , column2 desc
>>>
>>> Lets assume the query Q1,Q2 and Q3 returns 1 million records each, so
>>> total 3 million records. Let's assume that data query execution is local to
>>> the node.
>>>
>>> Page size is set to 500 to utilize the heap space efficiently or to
>>> avoid out of memory errors.
>>>
>>> In this scenario does ignite brings all million records in heap space
>>> and then does the union operation and then sorts it on column1 and column2?
>>>
>>
>>> I want to keep the memory foot print low. So I am just trying to
>>> understand how ignite executes the sql internally.
>>>
>>> Can someone please explain it?
>>>
>>> Thanks,
>>> Prasad
>>>
>>>