Sergi, AFAIK, you suggest to make query run on replicated cache in similar way as on partitioned. Do you mean that query will run on one node in single thread as it works now?
On Wed, May 3, 2017 at 3:39 PM, Sergi Vladykin <sergi.vlady...@gmail.com> wrote: > 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 <andrey.mashen...@gmail.com>: > >> 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@iriworldwid >> e.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 >> > > -- Best regards, Andrey V. Mashenkov
