Hi Mihaela,
Index is not used in your case because you specify function-based
condition. Usually this is resolved by adding functional index, but Ignite
doesn't support it at the moment unfortunately. Is it possible to
"materialize" the condition "POSITION ('Z',manufacturerCode)>0" as
additional attribute and add an index on it? In this case SQL would look
like this and index will be used:
SELECT COUNT(_KEY) FROM IgniteProduct AS product
WHERE manufacturerCodeZ=1
Another important thing is selectivity - which fraction of records fall
under this condition?
Also I would recommend to change "COUNT(_KEY)" to "COUNT(*)".
Vladimir.
On Tue, Aug 29, 2017 at 6:05 PM, Andrey Mashenkov <
andrey.mashen...@gmail.com> wrote:
> It is possible returned dataset is too large and cause high network
> pressure that results in large query execution time.
>
> There is no recommendation for grid nodes count.
> Simple SQL queries can work slower on large grid as most of time is spent
> in inter-node communication.
> Heavy SQL queries may show better results on larger grid as every node
> will have smaller dataset.
>
> You can try to look at page memory statistics [1] to get estimate numbers.
>
> Really, there is an issue with large OFFSET as Ignite can't just skip
> entries and have to fetch all of them from nodes.
> OFFSET makes no sense without ORDER as Ignite fetch rows from other nodes
> in async way and row order should be preserved between such queries.
> OFFSET applies on query initiator node (reduce side) after results merged
> as there is no way to understand on map side what rows should be skiped.
>
>
> Looks like underlying H2 tries to use index scan, but I don't think index
> can help in case of functional condition.
> You can try to make Ignite to have inline values in index or use separate
> field with smaller type that can be inlined. By default, index inlining is
> enabled for 10 byte length values.
> See IGNITE_MAX_INDEX_PAYLOAD_SIZE_DEFAULT system property docs and [2].
>
> [1] https://apacheignite.readme.io/v2.1/docs/memory-metrics
> [2] https://issues.apache.org/jira/browse/IGNITE-6060
>
> On Tue, Aug 29, 2017 at 3:59 PM, mhetea <mihaela.he...@gmail.com> wrote:
>
>> Thank you for your response.
>> I used query parallelizm and the time reduced to ~2.3s, which is still too
>> much.
>> Regarding 1. is there any documentation about configuration parameters
>> (recommended number of nodes, how much data should be stored on each
>> node).
>> We currently have 2 nodes with 32GB RAM each. Every 1 million records from
>> our cache occupy about 1GB (is there a way to see how much memory a cache
>> actually occupies? we look now at the Allocated next memory segment log
>> info)
>> For 3. it seems that the index is hit from the execution plan:
>> /* "productCache".IGNITEPRODUCT_MANUFACTURERCODE_IDX */
>> No?
>>
>> We have this issue also when we use a large OFFSET (we execute this kind
>> of
>> query because we want paginated results)
>>
>> Also, this cache will be updated frequently so we expect it to grow in
>> size.
>>
>> Thank you!
>>
>>
>>
>> --
>> View this message in context: http://apache-ignite-users.705
>> 18.x6.nabble.com/SQL-query-is-slow-tp16475p16487.html
>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>
>
>
>
> --
> Best regards,
> Andrey V. Mashenkov
>
