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.manda...@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- > 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. > -- Best regards, Andrey V. Mashenkov
