Hello,
I have a cache in REPLICATED mode populated with the contents of a single DB
table having ~2 million rows.
I am running a simple SQL query like:
select * from simulation_initial_values
where category_id in (1,2,3,4,5,6)
and geo_channel_id in (3,4,5,6)
and type_id=3
and week between 1888 and 1939;
I have indexes at all the above fields and the queries are of course
executed in local mode (since I have REPLICATED cache of read-only nature).
The performance of the query is poor, I get warnings in the log and I see
that no indexing is used.
I saw the
https://apacheignite.readme.io/docs/sql-performance-and-debugging#sql-performance-and-usability-considerations
<https://apacheignite.readme.io/docs/sql-performance-and-debugging#sql-performance-and-usability-considerations>
where it says that if a query contains IN operators then this query will
not use indexes. I followed the alternative approach proposed by replacing
the IN clause with JOIN but still the same poor results. I paste log below:
[14:36:53,479][WARN ][pub-#86%ppsp-cluster-IV%][IgniteH2Indexing] Query
execution is too long [time=7133 ms, sql='select * from
SimulationInitialValues siv join table(id bigint = ?) c on siv.categoryId =
c.id join table(id bigint = ?) g on siv.geoChannelId = g.id and siv.type =
? and siv.week between ? and ?', plan=
SELECT
SIV._KEY,
SIV._VAL,
SIV.ID,
SIV.CATEGORYID,
SIV.GEOCHANNELID,
SIV.GEOID,
SIV.PRODUCTID,
SIV.PPGID,
SIV.TYPE,
SIV.WEEK,
C.ID,
G.ID
FROM "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES SIV
/* "PPSP-IMDG-CACHE"."type_idx": TYPE = ?3 */
/* WHERE (SIV.TYPE = ?3)
AND ((SIV.WEEK >= ?4)
AND (SIV.WEEK <= ?5))
*/
INNER JOIN TABLE(ID BIGINT=?1) C
/* function: ID = SIV.CATEGORYID
AND ID = SIV.CATEGORYID
*/
ON 1=1
/* WHERE SIV.CATEGORYID = C.ID
*/
INNER JOIN TABLE(ID BIGINT=?2) G
/* function: ID = SIV.GEOCHANNELID
AND ID = SIV.GEOCHANNELID
*/
ON 1=1
WHERE (SIV.CATEGORYID = C.ID)
AND (((SIV.WEEK >= ?4)
AND (SIV.WEEK <= ?5))
AND ((SIV.TYPE = ?3)
AND (SIV.GEOCHANNELID = G.ID)))
, parameters=[[Ljava.lang.Object;@7346a49b, [Ljava.lang.Object;@5458bfc9, 2,
1888, 1939]]
--
View this message in context:
http://apache-ignite-users.70518.x6.nabble.com/Ignite-SQL-Indexing-Performance-problems-tp12342.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
