Wow, that seems to do the trick. When forcing the index use on the column that I order by the query returns within milliseconds instead of >40 seconds!
I‘ll do more tests but so far it looks like you are right, this helps significantly:
Thomas.
From: "Taras Ledkov" <tled...@gridgain.com>
Date: 13. April 2021
To: user@ignite.apache.org
Cc:
Subject: Re: SQL query performance with JOIN and ORDER BY or WHERE
Hi,
Please try to enforce use index for sorted select.
It make sense when filter produce a lot pf results.
So, full table will be scanned by ordered index, but result will
be not materialized on map nodes.
Using the example of the first message, it looks like this:
SELECT JQ._KEY
FROM "JobQueue".JOBQUEUE AS JQ USE INDEX(IDX_JQ_QUEUED)
INNER JOIN "Jobs".JOBS AS J ON JQ.jobid=J._key
WHERE JQ.STATUS = 2
ORDER BY JQ.QUEUED ASC
LIMIT 20
When IDX_JQ_QUEUED was created on JOBQUEUE (QUEUED).
I cannot guarantee he performance boost. Just to try.
On 12.04.2021 23:02, Thomas Kramer
wrote:
Hi Ilya,
unfortunately this also didn't help improving query
performance. Not sure what else I can try. Or maybe it is
expected? In my opinion it shouldn't take that long as the query
without the ORDER BY clause is super fast. Since there is a
index on the order field I would expect this should be fast.
Btw, I noticed that for some other queries the first call has
this long execution while on every following call with the same
SQL statement it returns within half a second. But I guess this
is caching related and not the issue I see here?
Best Regards,
Thomas.
On 12.04.21 13:15, Ilya Kasnacheev
wrote:
Hello!
I think you can try a (QUEUEID, STATUS) index.
Or maybe a (STATUS, QUEUEID), probably makes sense to try
both.
Regards,
The QUEUED
field is a BIGINT that contains timestamp from
System.currentTimeMillis(), so it should be pretty
easy to sort, shouldn’t it? Looks like the field
STATUS (used in where clause) and field QUEUED (used
in order clause) are not working optimal when used
together. Does this make sense? Do I need to create an
index on both together?
I will take
a look at UNION and WHERE EXISTS, I‘m not familiar
with these statements.
Thanks!
On 09.04.21 at 17:37, Ilya Kasnacheev wrote:
Hello!
ORDER BY will have to sort the whole table.
I think that using index on QUEUED will be
optimal here. What is the selectivity of this
field? If it s boolean, you might as well use
UNION queries.
Have you tried joining JOBS
via WHERE EXISTS?
Regards,
--
Ilya Kasnacheev
Hi,
I have a SQL performance issue. There are indexes
on both fields that are
used in the ORDER BY clause and the WHERE clause.
The following statement takes about 133941 ms with
several warnings from
IgniteH2Indexing:
SELECT JQ._KEY
FROM "JobQueue".JOBQUEUE AS JQ
INNER JOIN "Jobs".JOBS AS J ON JQ.jobid=J._key
WHERE JQ.STATUS = 2
ORDER BY JQ.QUEUED ASC
LIMIT 20
But when I remove the ORDER BY part or the WHERE
part from the statement it
returns in <10ms.
What may I do wrong?
Thanks,
Thomas.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
--
Taras Ledkov
Mail-To: tled...@gridgain.com
