You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by DonTequila <do...@gmx.de> on 2021/04/08 22:03:32 UTC
SQL query performance with JOIN and ORDER BY or WHERE
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/
Re: Re: SQL query performance with JOIN and ORDER BY or WHERE
Posted by do...@gmx.de.
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:
USE INDEX(my_index_on_order_by_column)
Thanks
Thomas.
On 13.04.21 at 13:10, Taras Ledkov wrote:
From: "Taras Ledkov" <tl...@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,
>>
>> \--
>
>>
>> Ilya Kasnacheev
>
>>
>>
>
>>
>>
>
>>
>> сб, 10 апр. 2021 г. в 00:22,
<[don.tequila@gmx.de](mailto:don.tequila@gmx.de)>:
>
>>
>>> 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:
>
>
>>>
>>> From: "Ilya Kasnacheev"
<[ilya.kasnacheev@gmail.com](mailto:ilya.kasnacheev@gmail.com)>
> Date: 9. April 2021
> To: [user@ignite.apache.org](mailto:user@ignite.apache.org)
> Cc:
> Subject: Re: SQL query performance with JOIN and ORDER BY or WHERE
>>>
>>> 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
>
>
>>>
>>>
>
>>>
>>>
>
>>>
>>> пт, 9 апр. 2021 г. в 01:03, DonTequila
<[don.tequila@gmx.de](mailto:don.tequila@gmx.de)>:
>
>>>
>>>> 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: [tledkov@gridgain.com](mailto:tledkov@gridgain.com)
Re: SQL query performance with JOIN and ORDER BY or WHERE
Posted by Taras Ledkov <tl...@gridgain.com>.
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,
>> --
>> Ilya Kasnacheev
>>
>>
>> сб, 10 апр. 2021 г. в 00:22, <don.tequila@gmx.de
>> <ma...@gmx.de>>:
>>
>> 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:
>>
>> From: "Ilya Kasnacheev" <ilya.kasnacheev@gmail.com
>> <ma...@gmail.com>>
>> Date: 9. April 2021
>> To: user@ignite.apache.org <ma...@ignite.apache.org>
>> Cc:
>> Subject: Re: SQL query performance with JOIN and ORDER BY or WHERE
>> 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
>>
>>
>>
>> пт, 9 апр. 2021 г. в 01:03, DonTequila <don.tequila@gmx.de
>> <ma...@gmx.de>>:
>>
>> 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/
>> <http://apache-ignite-users.70518.x6.nabble.com/>
>>
--
Taras Ledkov
Mail-To: tledkov@gridgain.com
Re: SQL query performance with JOIN and ORDER BY or WHERE
Posted by Thomas Kramer <do...@gmx.de>.
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,
> --
> Ilya Kasnacheev
>
>
> сб, 10 апр. 2021 г. в 00:22, <don.tequila@gmx.de
> <ma...@gmx.de>>:
>
> 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:
>
> From: "Ilya Kasnacheev" <ilya.kasnacheev@gmail.com
> <ma...@gmail.com>>
> Date: 9. April 2021
> To: user@ignite.apache.org <ma...@ignite.apache.org>
> Cc:
> Subject: Re: SQL query performance with JOIN and ORDER BY or WHERE
> 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
>
>
>
> пт, 9 апр. 2021 г. в 01:03, DonTequila <don.tequila@gmx.de
> <ma...@gmx.de>>:
>
> 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/
> <http://apache-ignite-users.70518.x6.nabble.com/>
>
Re: Re: SQL query performance with JOIN and ORDER BY or WHERE
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
I think you can try a (QUEUEID, STATUS) index.
Or maybe a (STATUS, QUEUEID), probably makes sense to try both.
Regards,
--
Ilya Kasnacheev
сб, 10 апр. 2021 г. в 00:22, <do...@gmx.de>:
> 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:
>
> From: "Ilya Kasnacheev" <il...@gmail.com>
> Date: 9. April 2021
> To: user@ignite.apache.org
> Cc:
> Subject: Re: SQL query performance with JOIN and ORDER BY or WHERE
> 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
>
>
>
> пт, 9 апр. 2021 г. в 01:03, DonTequila <do...@gmx.de>:
>
>> 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/
>>
>
Re: Re: SQL query performance with JOIN and ORDER BY or WHERE
Posted by do...@gmx.de.
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:
From: "Ilya Kasnacheev" <il...@gmail.com>
Date: 9. April 2021
To: user@ignite.apache.org
Cc:
Subject: Re: SQL query performance with JOIN and ORDER BY or WHERE
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
пт, 9 апр. 2021 г. в 01:03, DonTequila
<[don.tequila@gmx.de](mailto:don.tequila@gmx.de)>:
> 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/>
>
Re: SQL query performance with JOIN and ORDER BY or WHERE
Posted by Ilya Kasnacheev <il...@gmail.com>.
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
пт, 9 апр. 2021 г. в 01:03, DonTequila <do...@gmx.de>:
> 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/
>