ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kmandalas <Kyriakos.Manda...@IRIworldwide.com>
Subject Ignite SQL Indexing Performance problems
Date Tue, 02 May 2017 13:33:02 GMT
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.

Mime
View raw message