Hi,
I am using Ignite Cache with Persistence. While execution over large tables,
i noticed the below message repeatedly getting logged.
[WARNING][query-#100%IGNITE%][IgniteH2Indexing] Query execution is too long
[time=4863 ms, sql='SELECT
PUBLIC.__Z0._KEY __C0_0,
PUBLIC.__Z0._VAL __C0_1
FROM PUBLIC.TABLE_A __Z0
WHERE (__Z0.COL_A IN('AB', 'CD')) AND ((__Z0.COL_B IN('123', '1234')) AND
((__Z0.COL_C IN('XY', 'XYZ')) AND ((__Z0.COL_D = ?3) AND ((__Z0.COL_E = ?1)
AND (__Z0.COL_F = ?2)))))', plan=
SELECT
PUBLIC.__Z0._KEY AS __C0_0,
PUBLIC.__Z0._VAL AS __C0_1
FROM PUBLIC.TABLE_A __Z0
/* PUBLIC.TABLE_A.__SCAN_ */
WHERE (__Z0.COL_A IN('AB', 'CD'))
AND ((__Z0.COL_B IN('123', '1234'))
AND ((__Z0.COL_C IN('XY', 'XYZ'))
AND ((__Z0.COL_D = ?3)
AND ((__Z0.COL_E = ?1)
AND (__Z0.COL_F = ?2)))))
, parameters=[A, 1, 2]]
To resolve this, I created the below Index
CREATE INDEX IF NOT EXISTS TABLE_A_index1
ON TABLE_A
(COL_A Asc, COL_B ASC, COL_C ASC, COL_D ASC, COL_E ASC, COL_F asc)
PARALLEL 8;
Even after this, the slow queries kept coming.
On further analysis using 'Explain', I identified that Index is applied
properly if the below query is fired.
SELECT
COL_A
FROM PUBLIC.TABLE_A __Z0
WHERE (__Z0.COL_A IN('AB', 'CD'))
AND ((__Z0.COL_B IN('123', '1234'))
AND ((__Z0.COL_C IN('XY', 'XYZ'))
AND ((__Z0.COL_D = ?3)
AND ((__Z0.COL_E = ?1)
AND (__Z0.COL_F = ?2)))))
It doesn't get applied, when I fire the below query:
SELECT
COL_Z
FROM PUBLIC.TABLE_A __Z0
WHERE (__Z0.COL_A IN('AB', 'CD'))
AND ((__Z0.COL_B IN('123', '1234'))
AND ((__Z0.COL_C IN('XY', 'XYZ'))
AND ((__Z0.COL_D = ?3)
AND ((__Z0.COL_E = ?1)
AND (__Z0.COL_F = ?2)))))
It means, *the Index is getting applied only if I select a column which is
present in the Index. If I try to select any other column, which is not in
the Index, the index doesn't get applied.
*
Is any of you aware of this kind of behaviour? Is this normal with Ignite?
Do we have a workaround to make the index work?
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
