Hi,
I have this query (used Explain on original query):
SELECT
COUNT(DISTINCT _1__Z3.IP) AS __X0
FROM (
SELECT
ENDPOINT__Z0___KEY AS IP,
ENDPOINT__Z0__ELEMENTID AS TARGET,
CASEWHEN((ENDPOINT__Z0__STATUS IS NOT 'RULES_PENDING_ADD'),
ENDPOINT__Z0__STATUS, CASEWHEN((RULES__Z2.DEPLOYEDCOUNT > 0),
CASEWHEN((RULES__Z2.FAILEDCOUNT > 0), 'DEPLOYMENT_PARTIALLY_FAILED',
'RULES_DEPLOYED_OK'), CASEWHEN((RULES__Z2.FAILEDCOUNT > 0),
'RULES_DEPLOY_FAILED', 'RULES_PENDING'))) AS STATUS,
ENDPOINT__Z0__ENDPOINTTYPE AS EPTYPE,
ENDPOINT__Z0__COMBINATIONID AS COMBINATION
FROM (
SELECT
__C0_0 AS ENDPOINT__Z0___KEY,
__C0_1 AS ENDPOINT__Z0__COMBINATIONID,
__C0_2 AS ENDPOINT__Z0__ENDPOINTTYPE,
__C0_3 AS ENDPOINT__Z0__ELEMENTID,
__C0_4 AS ENDPOINT__Z0__STATUS
FROM PUBLIC.__T0
ORDER BY 1
) __Z5
LEFT OUTER JOIN (
SELECT
__C1_0 AS DESTIP,
SUM(__C1_1) AS DEPLOYEDCOUNT,
SUM(__C1_2) AS FAILEDCOUNT
FROM PUBLIC.__T1
GROUP BY __C1_0
ORDER BY 1
) RULES__Z2
ON ENDPOINT__Z0___KEY = RULES__Z2.DESTIP
ORDER BY 1
) _1__Z3
/* SELECT
ENDPOINT__Z0___KEY AS IP,
ENDPOINT__Z0__ELEMENTID AS TARGET,
CASEWHEN((ENDPOINT__Z0__STATUS IS NOT 'RULES_PENDING_ADD'),
ENDPOINT__Z0__STATUS, CASEWHEN((RULES__Z2.DEPLOYEDCOUNT > 0),
CASEWHEN((RULES__Z2.FAILEDCOUNT > 0), 'DEPLOYMENT_PARTIALLY_FAILED',
'RULES_DEPLOYED_OK'), CASEWHEN((RULES__Z2.FAILEDCOUNT > 0),
'RULES_DEPLOY_FAILED', 'RULES_PENDING'))) AS STATUS,
ENDPOINT__Z0__ENDPOINTTYPE AS EPTYPE,
ENDPOINT__Z0__COMBINATIONID AS COMBINATION
FROM (
SELECT
__C0_0 AS ENDPOINT__Z0___KEY,
__C0_1 AS ENDPOINT__Z0__COMBINATIONID,
__C0_2 AS ENDPOINT__Z0__ENDPOINTTYPE,
__C0_3 AS ENDPOINT__Z0__ELEMENTID,
__C0_4 AS ENDPOINT__Z0__STATUS
FROM PUBLIC.__T0
ORDER BY 1
) __Z5
/++ SELECT
__C0_0 AS ENDPOINT__Z0___KEY,
__C0_1 AS ENDPOINT__Z0__COMBINATIONID,
__C0_2 AS ENDPOINT__Z0__ENDPOINTTYPE,
__C0_3 AS ENDPOINT__Z0__ELEMENTID,
__C0_4 AS ENDPOINT__Z0__STATUS
FROM PUBLIC.__T0
/++ PUBLIC."merge_sorted" ++/
ORDER BY 1
/++ index sorted ++/
++/
LEFT OUTER JOIN (
SELECT
__C1_0 AS DESTIP,
SUM(__C1_1) AS DEPLOYEDCOUNT,
SUM(__C1_2) AS FAILEDCOUNT
FROM PUBLIC.__T1
GROUP BY __C1_0
ORDER BY 1
) RULES__Z2
/++ SELECT
__C1_0 AS DESTIP,
SUM(__C1_1) AS DEPLOYEDCOUNT,
SUM(__C1_2) AS FAILEDCOUNT
FROM PUBLIC.__T1
/++ PUBLIC."merge_scan": __C1_0 IS ?1 ++/
WHERE __C1_0 IS ?1
GROUP BY __C1_0
ORDER BY 1: DESTIP = ENDPOINT__Z0___KEY
++/
ON ENDPOINT__Z0___KEY = RULES__Z2.DESTIP
ORDER BY 1
*/
/* WHERE _1__Z3.IP IS NOT NULL
*/
LEFT OUTER JOIN (
SELECT
__C2_0 AS PRIORITY__Z4___KEY
FROM PUBLIC.__T2
ORDER BY 1
) __Z6
/* SELECT
__C2_0 AS PRIORITY__Z4___KEY
FROM PUBLIC.__T2
/++ PUBLIC."merge_sorted": __C2_0 IS ?1 ++/
WHERE __C2_0 IS ?1
ORDER BY 1
/++ index sorted ++/: PRIORITY__Z4___KEY = _1__Z3.IP
*/
ON _1__Z3.IP = PRIORITY__Z4___KEY
WHERE _1__Z3.IP IS NOT NULL
The ignite topology snapshot is the following(I am running ignite 2.6):
/Topology snapshot [ver=1, servers=1, clients=0, CPUs=8, offheap=3.1GB,
heap=2.0GB]/
Sizes if the tables:
/PUBLIC.__T0 is ~30k,
PUBLIC.__T1 is ~100k
PUBLIC.__T2 is 0/
I defined the tables to be in cache mode replicated with the following:
//
If I run the query with that configuration it takes ~1m 30s to get the
result.
But if I change the cache mode to LOCAL it will take <1s to get the result.
Is this accepted behavior? because, as I understand from the documentation
if I want to read using sql query it should work the same with REPLICATED as
LOCAL.
In the document it says ( from
https://apacheignite-sql.readme.io/docs/how-ignite-sql-works):
"First, if a query is executed against a fully REPLICATED data then Ignite
will send it to a single cluster node and run it over the local data there."
And I have only 1 node currently so it should run it over the local data on
this node.
Also, if I had more than one node , shouldn't the running be the same?
Thanks,
Moti
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
