Hello! I imagine that Ignite's underlying H2 planner will not use two different indexes on a same table.
So even if you want ORDER BY to use index, it should be the same index that you are filtering results with. So you should probably start your index from assetGroupId. Regards, -- Ilya Kasnacheev вт, 25 дек. 2018 г. в 13:39, Prasad Bhalerao <[email protected]>: > > I am executing following SQL on ignite cache. This cache has 37 million > records and this data is present on single node. > > *SQL:* > SELECT ipv4agd.id, > ipv4agd.assetGroupId, > ipv4agd.ipStart, > ipv4agd.ipEnd > FROM IpV4AssetGroupData ipv4agd > JOIN TABLE (assetGroupId bigint = ? ) temp > ON ipv4agd.assetGroupId = temp.assetGroupId > WHERE subscriptionId = ? > AND (ipStart <= ? AND ipEnd >= ?) > ORDER BY ipv4agd.assetGroupId,ipv4agd.ipStart > > > As per the execution plan show below, ignite is using index > "IPV4_ASSET_GROUP_DATA_IDX2 ". > > Now the question is > 1) Index sort order is subscriptionId->assetGroupID->ipStart->ipEnd. > In this case do I need to add ORDER BY clause explicitly to get the > results sorted by assetGroupId and then ipStart? > > 2) Does Order By add extra overhead? > > 2) Does ignite always return the results in sort order defined on index? > > 3) This sql is taking around 23 seconds. I have set the index inline size > as 32 bytes. > The number of assetGroupIds set in join clause are 50. > > I have checked the memory and cpu utilization and it it is very low. I > also tried to profile it using jprofiler to find out the issue, but could > not find solution. I have also attached profiler snapshot at the end. > Please check. > > Is there anything I can do to improve the performance of this SQL. > One thing I will be doing is adding 3 more nodes to distribute the data. > This might improve the sql execution time. > > *Indexes:* > > public class IpV4AssetGroupData implements > UpdatableData<DefaultDataAffinityKey> { > > @QuerySqlField > private long id; > @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = > "ipv4_asset_group_data_idx2", order = 2)}) > private long assetGroupId; > @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = > "ipv4_asset_group_data_idx1", order = 1), > @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 1)}) > private long subscriptionId; > @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = > "ipv4_asset_group_data_idx1", order = 2), > @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 3)}) > private int ipStart; > @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = > "ipv4_asset_group_data_idx1", order = 3), > @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 4)}) > private int ipEnd; > > } > > *Execution plan:* > > SELECT > IPV4AGD__Z0.ID __C0_0, > IPV4AGD__Z0.ASSETGROUPID __C0_1, > IPV4AGD__Z0.IPSTART __C0_2, > IPV4AGD__Z0.IPEND __C0_3 > FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0 > INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1 > ON TRUE > WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID) > AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2) > AND ((IPV4AGD__Z0.IPSTART <= ?3) > AND (IPV4AGD__Z0.IPEND >= ?4))) > ORDER BY 2', > plan= > SELECT > IPV4AGD__Z0.ID AS __C0_0, > IPV4AGD__Z0.ASSETGROUPID AS __C0_1, > IPV4AGD__Z0.IPSTART AS __C0_2, > IPV4AGD__Z0.IPEND AS __C0_3 > FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0 > /* IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4_ASSET_GROUP_DATA_IDX2: > SUBSCRIPTIONID = ?2 > AND IPSTART <= ?3 > AND IPEND >= ?4 > */ > /* WHERE (IPV4AGD__Z0.IPEND >= ?4) > AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2) > AND (IPV4AGD__Z0.IPSTART <= ?3)) > */ > INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1 > /* function: ASSETGROUPID = IPV4AGD__Z0.ASSETGROUPID */ > ON 1=1 > WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID) > AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2) > AND ((IPV4AGD__Z0.IPSTART <= ?3) > AND (IPV4AGD__Z0.IPEND >= ?4))) > ORDER BY 2 > > > Profiler snapshot: > > [image: image.png] >
