Hi,
Looks like it's known issue:
https://issues.apache.org/jira/browse/IGNITE-10781
According to this issue, indexes can work non-effective for distinct clause.
However, looks like the explain from your log isn't full. It should
contain two parts - reducer and mapper.
Can you please run next from any SQL tool:
explain select distinct ipStart,ipEnd from IpContainerIpV4Data where
subscriptionId = some_value;
BR,
Andrei
1/24/2020 3:23 PM, Prasad Bhalerao пишет:
Hi,
I am using Ignite 2.6 version. I have around total 6 million entries
in my cache.
Following sql is taking too much time to execute. Some times it takes
more than 180 seconds.
This SQL returns 4.5 million entries for given subscriptionId. I tried
to add query parallelism (4-16 threads) on cache configuration. But it
did not help.
If I remove DISTINCT keyword from sql then it executes quickly. But I
need distinct in this particular case.
Can some please advise?
*SQL:*
select distinct ipStart,ipEnd from IpContainerIpV4Data where
subscriptionId = ?
/2020-01-23 06:49:38,249 264738612 [query-#30600%springDataNode%] WARN
o.a.i.i.p.query.h2.IgniteH2Indexing - Query execution is too long
[time=83159 ms, sql='SELECT DISTINCT __Z0.IPSTART __C0_0, __Z0.IPEND
__C0_1 FROM IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z0 WHERE
__Z0.SUBSCRIPTIONID = ?1', plan= SELECT DISTINCT __Z0.IPSTART AS
__C0_0, __Z0.IPEND AS __C0_1 FROM
IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z0 /*
IP_CONTAINER_IPV4_CACHE.IP_CONTAINER_IPV4_IDX2: SUBSCRIPTIONID = ?1 */
WHERE __Z0.SUBSCRIPTIONID = ?1 , parameters=[1234]]/
*Index is as follows:*
**
public class IpContainerIpV4Data implements
Data<DefaultDataAffinityKey>, UpdatableData<DefaultDataAffinityKey> {
@QuerySqlField
private long id;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ip_container_ipv4_idx1", order = 2)})
private int moduleId;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ip_container_ipv4_idx1", order = 1),
@QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 0)})
private long subscriptionId;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ip_container_ipv4_idx1", order = 4, descending = true),
@QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 2,
descending = true)})
private int ipEnd;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ip_container_ipv4_idx1", order = 3),
@QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 1)})
private int ipStart;
@QuerySqlField
private int partitionId;
@QuerySqlField
private long updatedDate;
}
*Cache Configuration:*
private CacheConfiguration ipContainerIPV4CacheCfg() {
CacheConfiguration ipContainerIpV4CacheCfg =new
CacheConfiguration<>(CacheName.IP_CONTAINER_IPV4_CACHE.name());
ipContainerIpV4CacheCfg.setAtomicityMode(CacheAtomicityMode.TRANSACTIONAL);
ipContainerIpV4CacheCfg.setWriteThrough(ENABLE_WRITE_THROUGH);
ipContainerIpV4CacheCfg.setReadThrough(false);
ipContainerIpV4CacheCfg.setRebalanceMode(CacheRebalanceMode.ASYNC);
ipContainerIpV4CacheCfg.setWriteSynchronizationMode(CacheWriteSynchronizationMode.FULL_SYNC);
ipContainerIpV4CacheCfg.setBackups(this.backupCount);
Factory<IpContainerIpV4CacheStore> storeFactory =
FactoryBuilder.factoryOf(IpContainerIpV4CacheStore.class);
ipContainerIpV4CacheCfg.setCacheStoreFactory(storeFactory);
ipContainerIpV4CacheCfg.setIndexedTypes(DefaultDataAffinityKey.class,
IpContainerIpV4Data.class);
ipContainerIpV4CacheCfg.setCacheStoreSessionListenerFactories(cacheStoreSessionListenerFactory());
ipContainerIpV4CacheCfg.*setSqlIndexMaxInlineSize(84);*
RendezvousAffinityFunction affinityFunction =new
RendezvousAffinityFunction();
affinityFunction.setExcludeNeighbors(true);
ipContainerIpV4CacheCfg.setAffinity(affinityFunction);
ipContainerIpV4CacheCfg.setStatisticsEnabled(true);
ipContainerIpV4CacheCfg.setPartitionLossPolicy(PartitionLossPolicy.READ_WRITE_SAFE);
ipContainerIpV4CacheCfg.setQueryParallelism(4);return
ipContainerIpV4CacheCfg; }
Thanks,
Prasad
