CODEWITHSUNDEEP
sqlindexingignitesql-execution-plangridgain
RSSAH
RSSAH

Reputation: 153

SQL table query is slow in Apache Ignite

We are constantly getting these warnings for select query execution.

[20:30:16,798][WARNING][query-#75][IgniteH2Indexing] Long running query is finished [duration=5534ms, type=MAP, distributedJoin=false, enforceJoinOrder=false, lazy=false, schema=PUBLIC, sql='SELECT
"__Z0"."ID" "__C0_0",
"__Z0"."URL" "__C0_1",
"__Z0"."SCORE" "__C0_2",
"__Z0"."APPNAME_ID" "__C0_3"
FROM "PUBLIC"."URLS" "__Z0"
WHERE "__Z0"."APPNAME_ID" = ?1
ORDER BY 3 FETCH FIRST ?2 ROWS ONLY', plan=SELECT
    __Z0.ID AS __C0_0,
    __Z0.URL AS __C0_1,
    __Z0.SCORE AS __C0_2,
    __Z0.APPNAME_ID AS __C0_3
FROM PUBLIC.URLS __Z0
    /* PUBLIC.URLS.__SCAN_ */
    /* scanCount: 1541722 */
WHERE __Z0.APPNAME_ID = ?1
ORDER BY 3
FETCH FIRST ?2 ROWS ONLY, node=TcpDiscoveryNode [id=9b49bd18-a35d-44b9-a6d7-e28444b061cc, consistentId=9b49bd18-a35d-44g9-a6d7-e280b4b061cc, addrs=ArrayList [0:0:0:0:0:0:0:1%lo0, 127.0.0.1, 192.168.32.10], sockAddrs=HashSet [/192.168.32.10:0, /0:0:0:0:0:0:0:1%lo0:0, /127.0.0.1:0], discPort=0, order=3, intOrder=3, lastExchangeTime=1188336578528, loc=false, ver=8.7.10#20191227-sha1:c481141d, isClient=true], reqId=39750, segment=0]

The SQL create statement is 

CREATE TABLE IF NOT EXISTS URLS (" +
                        " ID LONG PRIMARY KEY, URL VARCHAR, SCORE DOUBLE, APPNAME_ID LONG) " +
                        " WITH \"TEMPLATE=PARTITIONED,ATOMICITY=ATOMIC,WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC,BACKUPS=0\"

Index create statement is

CREATE INDEX IF NOT EXISTS IDX_2_URLS ON URLS (APPNAME_ID, SCORE)

When the records reach higher number we are getting this is warnings. Can someone please suggest some idea to increase performance?

Upvotes: 1

Views: 504

Answers (1)

alamar
alamar

Reputation: 19313

You only have condition on APPNAME_ID so your index cannot be used, hence the whole table is scanned sequentially. Position of fields in index is important.

You need an index on (APPNAME_ID) or (APPNAME_ID, SCORE) if you like. Only one index may be used during execution of SELECT and only for those columns which appear headfirst in index. As soon as next field in index does not have condition in WHERE, all the remaining are ignored.

Upvotes: 1

Related Questions