Hi Igniters, I have a 6 node cluster with one partitioned cache and one replicated cache.
I run a join query on the two caches with a few indexes defined, but ignite
is not using an index for the query -
<bean class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name"
value="SHOP_ITEM_BITMAP_CACHE" />
<property name="eagerTtl"
value="false"/>
<property name="copyOnRead"
value="false"/>
<property name="atomicityMode"
value="ATOMIC"/>
<property name="backups" value="0"/>
<property name="memoryMode"
value="ONHEAP_TIERED"/>
<property name="offHeapMaxMemory"
value="0"/>
<property name="swapEnabled"
value="false"/>
<property name="evictionPolicy">
<bean
class="org.apache.ignite.cache.eviction.fifo.FifoEvictionPolicy">
<property name="maxSize"
value="30500000"/>
</bean>
</property>
<property name="cacheMode"
value="PARTITIONED" />
<property name="affinity">
<bean
class="org.apache.ignite.cache.affinity.rendezvous.RendezvousAffinityFunction">
<property
name="partitions" value="64"/>
</bean>
</property>
<property name="cacheStoreFactory">
<bean
class="javax.cache.configuration.FactoryBuilder$SingletonFactory">
<constructor-arg>
<bean
class="com.nielsen.poc.aggregation.ignite.datagrid.store.ShopItemBitmapStore">
</bean>
</constructor-arg>
</bean>
</property>
<property name="readThrough"
value="true" />
<property name="queryEntities">
<list>
<bean
class="org.apache.ignite.cache.QueryEntity">
<property
name="keyType"
value="com.nielsen.poc.aggregation.ignite.datagrid.model.ShopItemBitmapKey"
/>
<property
name="valueType"
value="com.nielsen.poc.aggregation.ignite.datagrid.model.ShopItemBitmap" />
<property
name="fields">
<map>
<entry key="id" value="java.lang.Integer" />
<entry key="sid_per_id" value="java.lang.Integer" />
<entry key="sid_mah_id" value="java.lang.Integer" />
<entry key="sid_itm_id" value="java.lang.Integer" />
<entry key="sid_prm_id" value="java.lang.Integer" />
<entry key="sid_cha_code" value="java.lang.String" />
<entry key="sid_service" value="java.lang.String" />
<entry key="sid_itm_dist" value="java.lang.String" />
<entry key="category" value="java.lang.String" />
</map>
</property>
<property
name="indexes">
<list>
<bean class="org.apache.ignite.cache.QueryIndex">
<constructor-arg value="category" />
</bean>
</list>
</property>
</bean>
</list>
</property>
</bean>
<bean
class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name"
value="MARKET_POD_ACV_CACHE" />
<property name="copyOnRead"
value="false"/>
<property name="eagerTtl"
value="false"/>
<property name="atomicityMode"
value="ATOMIC"/>
<property name="backups" value="0"/>
<property name="memoryMode"
value="ONHEAP_TIERED"/>
<property name="offHeapMaxMemory"
value="0"/>
<property name="evictionPolicy">
<bean
class="org.apache.ignite.cache.eviction.fifo.FifoEvictionPolicy">
<property name="maxSize"
value="21000"/>
</bean>
</property>
<property name="swapEnabled"
value="false"/>
<property name="cacheMode"
value="REPLICATED" />
<property name="cacheStoreFactory">
<bean
class="javax.cache.configuration.FactoryBuilder$SingletonFactory">
<constructor-arg>
<bean
class="com.nielsen.poc.aggregation.ignite.datagrid.store.MarketPodAcvStore">
</bean>
</constructor-arg>
</bean>
</property>
<property name="readThrough"
value="true" />
<property name="queryEntities">
<list>
<bean
class="org.apache.ignite.cache.QueryEntity">
<property
name="keyType" value="java.lang.Integer" />
<property
name="valueType"
value="com.nielsen.poc.aggregation.ignite.datagrid.model.MarketPodAcv" />
<property
name="fields">
<map>
<entry key="id" value="java.lang.Integer" />
<entry key="mrkt_key" value="java.lang.Integer" />
<entry key="prd_id" value="java.lang.Integer" />
<entry key="chld_mrkt_key" value="java.lang.Integer" />
</map>
</property>
<property
name="indexes">
<list>
<bean class="org.apache.ignite.cache.QueryIndex">
<constructor-arg index="0">
<list>
<value>mrkt_key</value>
<value>prd_id</value>
<value>chld_mrkt_key</value>
</list>
</constructor-arg>
<constructor-arg index="1" value="SORTED"/>
</bean>
<bean class="org.apache.ignite.cache.QueryIndex">
<constructor-arg value="chld_mrkt_key" />
</bean>
</list>
</property>
</bean>
</list>
</property>
</bean>
the query is -
SELECT distinct sid_per_id, sid_mah_id, sid_itm_id, sid_prm_id,
sid_cha_code, sid_service, sid_itm_dist
FROM ShopItemBitmap as bitmap
JOIN \"" + MRKT_POD_ACV_CACHE + "\".MarketPodAcv as mkt ON
bitmap.SID_MAH_ID = mkt.CHLD_MRKT_KEY and bitmap.SID_PER_ID = mkt.prd_id and
bitmap.category = '"+category+"'"
WHERE mkt.prd_id = "+period
and mkt.MRKT_KEY = "+market
its not using the index on the field "category" in ShopItemBitmap cache.
This query runs for more that 20 secs for a case when the number of records
in the results is more than 900,000 records. I want it to return in < 5
secs. I have colocated the records in ShopItemBitmap cache based on category
field.
Thanks,
Arthi
--
View this message in context:
http://apache-ignite-users.70518.x6.nabble.com/Why-does-ignite-not-use-an-index-tp4057.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
