Here is the explain plan.
0: jdbc:ignite:thin://127.0.0.1/> EXPLAIN SELECT DISTINCT ACCOUNT_ID FROM
PERF_POSITIONS;
'PLAN'
'SELECT DISTINCT
__Z0.ACCOUNT_ID AS __C0_0
FROM PUBLIC.PERF_POSITIONS __Z0
/* PUBLIC."_key_PK" */'
'SELECT DISTINCT
__C0_0 AS ACCOUNT_ID
FROM PUBLIC.__T0
/* PUBLIC."merge_scan" */'
2 rows selected (0.026 seconds)
Based on your suggestion, I tested below changes, but none of them made a
difference. In all cases, the query took consistently 56-60 seconds.
1. Having the index with inline size 60.
CREATE INDEX PERF_POSITIONS_IDX ON PERF_POSITIONS (ACCOUNT_ID) INLINE_SIZE
60;
2. Re-creating the table with VARCHAR size 4. (all the values in this
particular dataset are 4 chars).
CREATE TABLE PERF_POSITIONS (
ACCOUNT_ID VARCHAR(4) NOT NULL,
...
3. Using index hint.
SELECT DISTINCT ACCOUNT_ID FROM PERF_POSITIONS USE
INDEX(PERF_POSITIONS_IDX);
On a side note, I noticed that while the query is running, only 1 out of the
16 available cores get spiked to 100%, while the rest remain idle. Not sure
whether this is expected.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
