Here's the detailed information for my join test.
0: jdbc:ignite:thin://sap-datanode6/> select * from a;
x 1
y 1
A bearbrick
1 row selected (0.002 seconds)
0: jdbc:ignite:thin://sap-datanode6/> select count(*) from b;
COUNT(*) 14337959
1 row selected (0.299 seconds)
0: jdbc:ignite:thin://sap-datanode6/> select x,y from b where _key = '1';
x 1
y 1
1 row selected (0.002 seconds)
select a.x,a.y from a join b where a.x = b.x and a.y = b.y;
x 1
y 1
1 row selected (6.036 seconds) -- Takes 6 seconds to join a table with one
row to 14 million row table using affinity key x
explain select a.x,a.y from a join b where a.x = b.x and a.y = b.y;
PLAN SELECT
A__Z0.x AS __C0_0,
A__Z0.y AS __C0_1
FROM PUBLIC.B__Z1
/* PUBLIC.B.__SCAN_ */
INNER JOIN PUBLIC.T A__Z0
/* PUBLIC.AFFINITY_KEY: x = B__Z1.x */
ON 1=1
WHERE (A__Z0.y = B__Z1.y)
AND (A__Z0.x = B__Z1.x)
PLAN SELECT
__C0_0 AS x,
__C0_1 AS y
FROM PUBLIC.__T0
/* PUBLIC."merge_scan" */
If I create a index on table b on field x and y, it takes 6.8 seconds to
finish join.
create index on b(x,y);
No rows affected (31.316 seconds)
0: jdbc:ignite:thin://sap-datanode6/> select a.x,a.y from a join b where a.y
= b.y and a.x = b.x;
x 1
y 1
1 row selected (6.865 seconds)
0: jdbc:ignite:thin://sap-datanode6/> explain select a.x,a.y from a join b
where a.y = b.y and a.x = b.x;
PLAN SELECT
A__Z0.x AS __C0_0,
A__Z0.y AS __C0_1
FROM PUBLIC.T A__Z0
/* PUBLIC.T.__SCAN_ */
INNER JOIN PUBLIC.B__Z1
/* PUBLIC."b_x_asc_y_asc_idx": y = A__Z0.y
AND x = A__Z0.x
*/
ON 1=1
WHERE (A__Z0.y = B__Z1.y)
AND (A__Z0.x = B__Z1.x)
PLAN SELECT
__C0_0 AS x,
__C0_1 AS y
FROM PUBLIC.__T0
/* PUBLIC."merge_scan" */
2 rows selected (0.003 seconds)
Here's my configuration
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd";>
<bean id="grid.cfg"
class="org.apache.ignite.configuration.IgniteConfiguration">
<property name="segmentationPolicy" value="RESTART_JVM"/>
<property name="peerClassLoadingEnabled" value="true"/>
<property name="failureDetectionTimeout" value="60000"/>
<property name="dataStorageConfiguration">
<bean
class="org.apache.ignite.configuration.DataStorageConfiguration">
<property name="defaultDataRegionConfiguration">
<bean
class="org.apache.ignite.configuration.DataRegionConfiguration">
<property name="name" value="default_Region"/>
<property name="initialSize" value="#{100L * 1024 * 1024
* 1024}"/>
<property name="maxSize" value="#{460L * 1024 * 1024 *
1024}"/>
<property name="persistenceEnabled" value="true"/>
<property name="checkpointPageBufferSize" value="#{8L *
1024 * 1024 * 1024}"/>
</bean>
</property>
<property name="walMode" value="BACKGROUND"/>
<property name="walFlushFrequency" value="5000"/>
<property name="checkpointFrequency" value="600000"/>
</bean>
</property>
<property name="discoverySpi">
<bean
class="org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi">
<property name="localPort" value="49500"/>
<property name="ipFinder">
<bean
class="org.apache.ignite.spi.discovery.tcp.ipfinder.vm.TcpDiscoveryVmIpFinder">
<property name="addresses">
<list>
<value>node1:49500</value>
<value>node2:49500</value>
<value>node3:49500</value>
<value>node4:49500</value>
<value>node5:49500</value>
<value>node6:49500</value>
</list>
</property>
</bean>
</property>
</bean>
</property>
<property name="gridLogger">
<bean class="org.apache.ignite.logger.log4j2.Log4J2Logger">
<constructor-arg type="java.lang.String"
value="config/ignite-log4j2.xml"/>
</bean>
</property>
</bean>
</beans>
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
