Andrey, In 2.0 queries over replicated cache still work in a single thread.
Kyriakos, I see that only index on TYPE is used which is not the most selective obviously. I suggest to create a group index on (TYPE, CATEGORYID, GEOCHANNELID, WEEK) and make sure it is used in query plan. Sergi 2017-05-03 15:02 GMT+03:00 Andrey Mashenkov <[email protected]>: > Hi, > > As you use REPLICATED cache, the query will run on one node in single > thread. This should be fixed in 2.0 > The only workaround for now is to convert cache to PARTITIONED, to make > the query to be run on several nodes, that will significantly increase > performance. > > Also, you can try to add more field in index "PPSP-IMDG-CACHE"."type_idx". > > On Tue, May 2, 2017 at 4:33 PM, kmandalas <Kyriakos.Mandalas@ > iriworldwide.com> wrote: > >> 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-debu >> gging#sql-performance-and-usability-considerations >> <https://apacheignite.readme.io/docs/sql-performance-and-deb >> ugging#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.705 >> 18.x6.nabble.com/Ignite-SQL-Indexing-Performance-problems-tp12342.html >> Sent from the Apache Ignite Users mailing list archive at Nabble.com. >> > > > > -- > Best regards, > Andrey V. Mashenkov >
