Hi All,
We're trying to aggregate records using the below queries. We're using paged
query in order to create an aggregation on many devices in one query (using IN
operator)
Each query creates a similar range scan (with different values). Table is a 50M
records table spread across 5 regions. (accountId, metricName, deviceId are the
first 3 parts of the key in that order - out of 4)
The first query completes very fast because the keys are close to each other
and therefor the range scan is short.
The second query takes a lot of time (2-3 minutes) since the range scan scans
almost all table because the keys are at the beginning and end of the table.
We've tried to put a skip scan hint and it didn't help.
We're trying to understand if that is the way phoenix operates and if so then
why and how should we perform the desired operation?
If it shouldn't work like this - what are we doing wrong? Or is it a bug?
Any quick help / lead will be highly appreciated.
Thanks,
Ido
1. Query:
select count(*) from REPORT_TEMP_DEVICE_MEASUREMENT where
("accountId","metricName","deviceId") in (('z','ac.x','android~phone'),
('y','ac.y','android~phone'));
Explain:
CLIENT PARALLEL 32-WAY RANGE SCAN OVER REPORT_TEMP_DEVICE_MEASUREMENT
['y\x00ac.y\x00android~phone'] - ['z\x00ac.x\x00android~phone']
SERVER FILTER BY FIRST KEY ONLY AND (accountId, metricName, deviceId) IN
([121,0,97,99,46,121,0,97,110,100,114,111,105,100,126,112,104,111,110,101],[122,0,97,99
SERVER AGGREGATE INTO SINGLE ROW
2. Query:
select count(*) from REPORT_TEMP_DEVICE_MEASUREMENT where
("accountId","metricName","deviceId") in (('z','ac.x','android~phone'),
('a','ac.y','android~phone'));
Explain:
CLIENT PARALLEL 30-WAY RANGE SCAN OVER REPORT_TEMP_DEVICE_MEASUREMENT
['a\x00ac.y\x00android~phone'] - ['z\x00ac.x\x00android~phone']
SERVER FILTER BY FIRST KEY ONLY AND (accountId, metricName, deviceId) IN
([97,0,97,99,46,121,0,97,110,100,114,111,105,100,126,112,104,111,110,101],[122,0,97,99,
|
SERVER AGGREGATE INTO SINGLE ROW |
---------------------------------------------------------------------
Intel Electronics Ltd.
This e-mail and any attachments may contain confidential material for
the sole use of the intended recipient(s). Any review or distribution
by others is strictly prohibited. If you are not the intended
recipient, please contact the sender and delete all copies.
