Apache ignite query not working well with index

Question

I upload the data in ignite 3 cache and total data around 1 million in all 3 caches.while i run query on it i got result in 1s and its generate below query plan

SELECT DISTINCT
   A3.VAL2 AS __C0
FROM DPHAF.DPHAF A1
   /* DPHAF.DPHAF.__SCAN_ */
   /* WHERE (A1.VAL0 = 'dg~')
       AND ((A1.PROP6 = '1oh~#has_single_bond')
       AND (A1.PROP0 = '4xm~#type'))
   */
INNER JOIN DS_AF.DS_AF S1
   /* DS_AF."prop_asc_entity_asc_elem_asc_typ_asc_idx": PROP = A1.PROP6
       AND ENTITY = A1.ENTRY
    */
   ON 1=1
   /* WHERE (A1.PROP6 = S1.PROP)
       AND (A1.ENTRY = S1.ENTITY)
   */
INNER JOIN DPHAF.DPHAF A2
   /* DPHAF."entry_asc_idx": ENTRY = S1.ELEM */
   ON 1=1
   /* WHERE (S1.ELEM = A2.ENTRY)
       AND ((A2.PROP0 = '4xm~#type')
       AND ((A2.PROP6 = '1oh~#has_single_bond')
       AND (A2.VAL0 = '7a~')))
   */
INNER JOIN DS_AF.DS_AF S2
   /* DS_AF."prop_asc_entity_asc_elem_asc_typ_asc_idx": PROP = A2.PROP6
       AND ENTITY = A2.ENTRY
    */
   ON 1=1
   /* WHERE (A2.PROP6 = S2.PROP)
       AND (A2.ENTRY = S2.ENTITY)
   */
INNER JOIN DPHAF.DPHAF A3
   /* DPHAF."entry_asc_idx": ENTRY = S2.ELEM */
   ON 1=1
WHERE (A3.PROP2 = '1oh~#is_atom_of')
   AND ((A3.VAL0 = '7a~')
   AND ((A3.PROP0 = '4xm~#type')
   AND ((S2.ELEM = A3.ENTRY)
   AND ((A2.PROP6 = S2.PROP)
   AND ((A2.PROP6 = '1oh~#has_single_bond')
   AND ((A2.ENTRY = S2.ENTITY)
   AND ((A2.VAL0 = '7a~')
   AND ((A2.PROP0 = '4xm~#type')
   AND ((S1.ELEM = A2.ENTRY)
   AND ((A1.PROP6 = S1.PROP)
   AND ((A1.PROP6 = '1oh~#has_single_bond')
   AND ((A1.ENTRY = S1.ENTITY)
   AND ((A1.PROP0 = '4xm~#type')
   AND (A1.VAL0 = 'dg~'))))))))))))))
LIMIT 1000

SELECT DISTINCT
   __C0 AS VAL2
FROM PUBLIC.__T0
   /* DPHAF."merge_scan" */
LIMIT 1000

as you notice there is full cache scan here /* DPHAF.DPHAF.__SCAN_ */

After that when we add index on DPHAF group index it will give us below plan

SELECT
    A1.VAL2 AS __C0
FROM DPHAF.DPHAF A1
    /* DPHAF."prop0_asc_val0_asc_idx": VAL0 = '7a~'
        AND PROP0 = '4xm~#type'
     */
    /* WHERE (A1.PROP0 = '4xm~#type')
        AND ((A1.PROP2 = '1oh~#is_atom_of')
        AND ((A1.VAL0 = '7a~')
        AND ((A1.VAL5 = '3')
        AND ((A1.PROP8 = '1oh~#has_double_bond')
        AND (A1.PROP6 = '1oh~#has_single_bond')))))
    */
INNER JOIN DPHAF.DPHAF A3
    /* DPHAF."prop0_asc_val0_asc_idx": VAL0 = '7a~'
        AND PROP0 = '4xm~#type'
     */
    ON 1=1
    /* WHERE (A3.PROP0 = '4xm~#type')
        AND ((A3.VAL5 = '1')
        AND (A3.VAL0 = '7a~'))
    */
INNER JOIN DPHAF.DPHAF A4
    /* DPHAF."prop0_asc_val0_asc_idx": VAL0 = '562~'
        AND PROP0 = '4xm~#type'
     */
    ON 1=1
    /* WHERE (A4.PROP0 = '4xm~#type')
        AND ((A4.VAL5 = '1')
        AND (A4.VAL0 = '562~'))
    */
INNER JOIN DS_AF.DS_AF S1
    /* DS_AF."prop_asc_entity_asc_elem_asc_typ_asc_idx": PROP = A1.PROP6
        AND ENTITY = A1.ENTRY
     */
    ON 1=1
    /* WHERE (A1.PROP6 = S1.PROP)
        AND (A1.ENTRY = S1.ENTITY)
    */
INNER JOIN DPHAF.DPHAF A2
    /* DPHAF."entry_asc_idx": ENTRY = S1.ELEM */
    ON 1=1
    /* WHERE (S1.ELEM = A2.ENTRY)
        AND ((A2.VAL5 = '1')
        AND (A2.VAL0 = '562~'))
    */
INNER JOIN DS_AF.DS_AF S2
    /* DS_AF."prop_asc_elem_asc_entity_asc_typ_asc_idx": ELEM = A3.ENTRY
        AND PROP = A1.PROP6
        AND ENTITY = A1.ENTRY
     */
    ON 1=1
    /* WHERE (A1.ENTRY = S2.ENTITY)
        AND ((S2.ELEM = A3.ENTRY)
        AND (A1.PROP6 = S2.PROP))
    */
INNER JOIN DS_AF.DS_AF D1
    /* DS_AF."prop_asc_elem_asc_entity_asc_typ_asc_idx": ELEM = A4.ENTRY
        AND PROP = A1.PROP8
        AND ENTITY = A1.ENTRY
     */
    ON 1=1
WHERE (A4.VAL5 = '1')
    AND ((A4.VAL0 = '562~')
    AND ((A4.PROP0 = '4xm~#type')
    AND ((D1.ELEM = A4.ENTRY)
    AND ((A1.PROP8 = D1.PROP)
    AND ((A1.PROP8 = '1oh~#has_double_bond')
    AND ((A1.ENTRY = D1.ENTITY)
    AND ((A3.VAL5 = '1')
    AND ((A3.VAL0 = '7a~')
    AND ((A3.PROP0 = '4xm~#type')
    AND ((S2.ELEM = A3.ENTRY)
    AND ((A1.PROP6 = S2.PROP)
    AND ((A1.ENTRY = S2.ENTITY)
    AND ((A2.VAL5 = '1')
    AND ((A2.VAL0 = '562~')
    AND ((S1.ELEM = A2.ENTRY)
    AND ((A1.PROP6 = S1.PROP)
    AND ((A1.PROP6 = '1oh~#has_single_bond')
    AND ((A1.ENTRY = S1.ENTITY)
    AND ((A1.VAL5 = '3')
    AND ((A1.VAL0 = '7a~')
    AND ((A1.PROP2 = '1oh~#is_atom_of')
    AND (A1.PROP0 = '4xm~#type'))))))))))))))))))))))

SELECT
    __C0 AS VAL2
FROM PUBLIC.__T0
    /* DPHAF."merge_scan" */

Here you can see we dont have full scan of DPHAF but my query is running for soo long or infinite so am i do anything wrong? Thanks

Denis Kholodov Denis Kholodov · Accepted Answer · 2016-10-25T13:51:24

It is best if you ask this question at Ignite user forum. It requires debugging to get to the bottom of it.

Apache ignite query not working well with index

1 Answers