CODEWITHSUNDEEP
ignitegridgain
randomuser
randomuser

Reputation: 1221

Apache Ignite: IN query too slow

I'm trying to run queries on Apache Ignite after populating it from PostgreSQL db. Problem is that

Suppoe I've 2 caches A and B, having 8700 and 10,000 records respectively. When I run queries independent queries

SELECT id FROM "pcm".pcm WHERE code=1222;

SELECT id, name FROM "tn".tn WHERE id IN (6838);

Here, 6838 is returned by the 1st query. These queries take around 6ms and 10ms resp. But when I run

SELECT id, name FROM "tn".tn WHERE id IN (SELECT id FROM "pcm".pcm WHERE code=1222);

This takes around 9000ms.

Can somebody help me to figure out why this query is taking so much time? PS: I have not built any indexes on ignite as of now.

Upvotes: 1

Views: 1020

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

select id, tn.name
from 
    pcm.pcm
    inner join
    tn.tn using (id)
where pcm.code = 1222

Upvotes: -1

Evgenii Zhuravlev
Evgenii Zhuravlev

Reputation: 3007

Operator IN doesn't use indexes. You should replace IN with joins:

https://apacheignite-sql.readme.io/docs/performance-and-debugging#section-sql-performance-and-usability-considerations

Upvotes: 5

Related Questions